Error 400 in a simple copy and paste between sheets.

[Newfs_R_Sublime]

Range("CC26:EK27").Select
Selection.Copy
Sheets("Sheet2").Select
Range("H1").Select
ActiveSheet.Paste


It crashes on the last two lines. I get a GUI that just says 400. I've seen other issues like this but I can't seem to get this code to work.

Thank you for your time and effort.

Rick

 

[Andrew Poulsom]

How about?

Range("CC26:EK27").Copy Sheets("Sheet2").Range("H1")

 

[VoG]

Try without selecting

Range("CC26:EK27").Copy Destination:=Sheets("Sheet2").Range("H1")

 

[hiker95]

Newfs_R_Sublime,

Try:

Range("CC26:EK27").Copy Sheets("Sheet2").Range("H1")

 

[Newfs_R_Sublime]

Andrew's worked, but it just pushed down the error until the next time I tried to copy and paste data. Is there a reason that this is happening?

 

[Newfs_R_Sublime]

Now when I am on Sheet2 for good, I get the same error (or 1004) with:

Sheets("Sheet2").Select

Columns("M:N").Select

 

[Newfs_R_Sublime]

I asked Andrew in a private message, the above question. I will post his reply (when and if I get it)

 

[Andrew Poulsom]

Where is the code you posted? If it's in the module for a Worksheet try qualifying the Columns property:

Sheets("Sheet2").Columns("M:N").Select

 

[Newfs_R_Sublime]

Yes it is in a macro that was recorded in another workbook and copied over. This worked, but am I going to have to do this with every reference on this sheet?

 

[Andrew Poulsom]

Only if the code is in a worksheet module. Normally VBA uses the ActiveSheet for unqualified range properties, but it uses the sheet containing the code if it's in a worksheet module.