VBA Code

Posted by Jennifer on June 29, 2001 3:40 PM

I'm trying to copy and paste, with a macro, and the range contains named cells. These named cells are also in the destination workshee. When I run the macro, Excel asks 'Do you want to use the named cells in the destination worksheet', which requires the user to answer 'yes' to. I'd like to code up the 'yes' answer so that the user isn't give the choice.

Anyone know the parameter or code word to do this?

Posted by Scott on June 29, 2001 3:55 PM

I tried to set up what you describe, and my code seems to work ok. It could be that your code is not activating the new workbook inbetween copy and paste? Here is the code that I used, assuming that the Cell name is "Here" in both workbooks.

Sub Copy_to_Another()

Application.Goto Reference:="Here"
Selection.Copy
Windows("Book2").Activate
Application.Goto Reference:="Here"
ActiveSheet.Paste
End Sub

Posted by Joe Was on June 29, 2001 4:04 PM

You can add:

Application.DisplayAlerts=False

above the key code and:

Application.DisplayAlerts=True

below the key code to set the screen alerts back on. This stops Excel from asking what you want and then chooses the defualt answer. JSW

Posted by Jennifer on June 29, 2001 4:13 PM

That did the trick! Thanks a ton.