Run-time 9 Error

[dhill0502]

For the life of my I cannot figure out why the following line of code is giving me a "Run-time error '9': Subscript out of range" Error.

Workbooks("Lookup").Worksheets("Sheet1").Range("A1:BK2000").Copy Destination:=Workbooks("EstimateChecker").Worksheets("estsheet").Range("A1:BK2000")

I am trying to copy sheet1 from the workbook Lookup into the sheet estsheet in the EstimateChecker workbook. Any help would be appreciated.

 

[Fluff]

Shouldn't the Estimate workbook be xlsm rather than xls?

 

[dhill0502]

It should be .xlsm yes. However, changing that to xlsm did not get me any different results. Based on your tests you recommended earlier running Workbooks("Lookup.xlsm").activate gives me the same error as before.

 

[Fluff]

In that case I'm not quite sure what is happening.
One possible workaround, would be to ensure that the Lookup workbook is the active workbook when you run the code & use something like

   Dim Wbk As Workbook
   
   Set Wbk = ActiveWorkbook
   Wbk.Worksheets("Sheet1").Range("A1:BK2000").Copy Destination:=Workbooks("EstimateChecker.xlsm").Worksheets("estsheet").Range("A1:BK2000")