Supress Prompts in VBA

[Trevor Best]

I have a VBA macro that does things like open up another sheet, copy
it, close it, past contents into current sheet. Then SaveAs.

I get prompted 3 times:
1. On closing other sheet "do you want to close and keep clipboard"
2. On Pasting "source and target not the same shape"
3. On saving "do you want to overwrite"

My answer to everything is yes or OK. Any way to supress these
prompts?

 

[Dave Peterson]

#1. Add this to clear the clipboard right after your paste (or right before you
close the workbook):

Application.cutcopymode = false

#2. Instead of trying to determine the paste-range shape, you could just paste
into the topleftcorner of the paste-range

workbooks("book1.xls").worksheets("sheet1").range("c39:e99").copy _
destination:=workbooks("book2.xls").worksheets("Sheet33").range("b9")

Excel will figure out where things go.

#3. If you always answer yes, you can do this:

application.disablealerts = true
'your workbook save code
application.disablealerts = false

 

[Trevor Best]

#1. Add this to clear the clipboard right after your paste (or right before you
close the workbook):

Application.cutcopymode = false

#2. Instead of trying to determine the paste-range shape, you could just paste
into the topleftcorner of the paste-range

workbooks("book1.xls").worksheets("sheet1").range("c39:e99").copy _
destination:=workbooks("book2.xls").worksheets("Sheet33").range("b9")

Excel will figure out where things go.

#3. If you always answer yes, you can do this:

application.disablealerts = true
'your workbook save code
application.disablealerts = false

Thanks. One more thing, is there a clipboard object like in VB so that
I could save the current clipboard, do my copy & paste in the macro
then restore whatever was there b4hand?

Thanks for your answer in the next thread too, I couldn't even work
out how to do it manually

 

[Dave Peterson]

I'm not a VB user, but Chip Pearson has some sample code/functions for working
with the clipboard at:
http://www.cpearson.com/excel/clipboar.htm

I tried a sample using Chip's code and I could copy a range, save those values
(emulating an existing copy), then copy a range, paste it and finally put the
Text back on the clipboard--I lost the format/formulas though.

I've never worried about losing what was in the clipboard. I just figured that
the user could recopy it--especially if you have to worry about other stuff than
ranges (pictures/shapes/controls...)