Macros Containing a File Save As & Tab Deletions

M

Mike The Newb

I have a macro that saves off a copy of the file, replacing the existing
saved off copy. Even though I record "yes" to replace the existing copy I am
always prompted to say/click "yes" again when the macro runs. Also, on
another macro, I delete a few tabs from the copy. I am prompted during the
recording (like "are you sure you want to delete this tab") and I still get
prompted again when the macro runs.

Is there a way to program it so the macro saves over an existing or delete
tabs without user intervention, to agree with what has already been agreed
to, while the macro is actually running?

Many thanks in advance!

Regards,
Mike
 
D

Dave Peterson

If you use something like:

Activeworkbook.save
or
thisworkbook.save

You shouldn't be prompted to overwrite the existing file.

If you're using .saveas, you can avoid the prompt:

application.displayalerts = false
activeworkbook.saveas .....
application.displayalerts = true

And the same .displayalerts will stop the prompt when you delete a sheet in
code.
 
G

Gord Dibben

Mike

Wrap the actions inside these two lines.

Application.DisplayAlerts = False

'do your code stuff

Application.DisplayAlerts = True


Gord Dibben MS Excel MVP
 
M

Mike The Newb

Dave - worked great/excellent! Thank you!

Dave Peterson said:
If you use something like:

Activeworkbook.save
or
thisworkbook.save

You shouldn't be prompted to overwrite the existing file.

If you're using .saveas, you can avoid the prompt:

application.displayalerts = false
activeworkbook.saveas .....
application.displayalerts = true

And the same .displayalerts will stop the prompt when you delete a sheet in
code.
Click to expand...
 
M

Mike The Newb

Gord - thank you - it worked great.

Gord Dibben said:
Mike

Wrap the actions inside these two lines.

Application.DisplayAlerts = False

'do your code stuff

Application.DisplayAlerts = True


Gord Dibben MS Excel MVP
Click to expand...
 
Top