0

I've got a recorded macro that I tried to simplify by getting a number of activate and select statements onto a single line, but that results in a runtime error.

This is not a critical problem, but I'm just curious to understand what is going on. This is my initial code snippet (it is preceded by a Copy snippet in the procedure):

ThisWorkbook.Activate  
Sheets("MS Act Report").Select  
Range("G1").Select  
ActiveSheet.Paste

this is my simplified code: 

ThisWorkbook.Activate  
Sheets("MS Act Report").Range("G1").Select  
ActiveSheet.Paste

When running this I get a

runtime error '1004': Select method of Range class failed

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
doterfish
  • 3
  • 3
  • You can only select ranges on the active sheet. If "MS Act Report" is not the active sheet, you can't issue a .select command against its cells. – vacip Oct 11 '16 at 11:45
  • 3
    You don't need to select anyway: `ThisWorkbook.Sheets("MS Act Report").Range("G1").PasteSpecial` – Rory Oct 11 '16 at 11:47
  • @vacip - thanks. So by using the separate select commands the sheet becomes activated but this is not the case if I have it on one line. – doterfish Oct 12 '16 at 08:02
  • @Rory - thanks. I'd tried something like your suggestion previously but used .Paste instead of .PasteSpecial, which didn't work. Do you know why that is? Thanks for your suggestion. Going with that one! – doterfish Oct 12 '16 at 08:05
  • @doterfish Exactly, using them step by step they get activated one after the other. For the other question, .Paste is a method of the Worksheet object, .PasteSpecial is a method of the Range object (so you have to use SomeWorksheet.Paste or SomeRange.PasteSpecial. No other way, can't mix them). As for the why: because. Because someone at Microsoft decided this will work this way. – vacip Oct 12 '16 at 08:57

1 Answers1

2

You can only select ranges on the active sheet. If "MS Act Report" is not the active sheet, you can't issue a .select command against its cells. To simplify the code, instead of copy-pasting, just make the ranges equal.

Thisworkbook.WorkSheets("MS Act Report").Range("G1:I5").Value= _
  ActiveWorkbook.Worksheets("Whatever").Range("a1:c5").Value

Some recommended reading: How to avoid using select

Community
  • 1
  • 1
vacip
  • 5,246
  • 2
  • 26
  • 54
  • thanks for the suggestions but the range that's copied is different everytime the procedure is run, so I'd need to work around that. So Rory's suggestion above seems more straightforward. – doterfish Oct 12 '16 at 08:24