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.

 

[James006]

Hi,

You can test

[COLOR=#333333]Workbooks("EstimateChecker").Worksheets("estsheet").Range("A1:BK2000").Value = [/COLOR][COLOR=#333333]Workbooks("Lookup").Worksheets("Sheet1").Range("A1:BK2000").Value[/COLOR]

Hope this will help

 

[Fluff]

Hi & welcome to MrExcel
Assuming that both sheet names & workbook names are correct, try adding the file extension to the workbook names.

 

[dhill0502]

I tried the suggestion from James and got the same error. I have tried using the file extensions which for what I know are xlsm for the EstimateChecker which is a Office 2010 Macro-Enabled Workbook. I also tried using xlsx and xls for the Lookup workbook. The Lookup workbook is exported from my computer system, which is old, into an excel sheet and it says it has to open it in Compatibility Mode so I tried using the xls which is for 97-2003 Excel files. Either way using any combination of the file extensions gives the same error.

 

[Fluff]

With the lookup workbook open look in the Title bar & you should see something like Lookup.??? [Compatability mode] - Excel
What does it say in place of the ???

 

[dhill0502]

On the top of the Lookup workbook it says, "Lookup [Compatibility Mode] - Microsoft Excel"

 

[Fluff]

Ok, let's try and narrow it down. What happens if you run this (with both workbooks open)

Sub chk()
   Workbooks("EstimateChecker.xlsm").Activate
   Worksheets("estsheet").Select
   Workbooks("Lookup").Activate
   Worksheets("Sheet1").Select
End Sub

 

[dhill0502]

The problem after using your test is with Lookup workbook. I tried using xls and xlsx file extensions in the name and it gave me the same error. I have no way of changing the export to excel functionality of the outdated software so if the problem is that it is exporting the excel file in the Compatibility Mode, which I think means something to do with a 97-2003 Version, then I will just have to copy and paste the old way.

 

[Fluff]

With the Lookup workbook active run this

Sub chk()
Debug.Print "|" & ActiveWorkbook.Name & "|"
End Sub

The copy paste the result in the immediate window to the thread.
The Immediate window is normally below the main code window & Ctrl G will bring it up if not visible

 

[dhill0502]

|Lookup.xlsm|

That is the result from running the sub you had me run. Unfortunately after changing the file extension in the original code to .xlsm I got the same error.

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

Just to be clear that is the latest code that was used and still I got the Run-Time error '9'