Subscript out of range
I am new to VBA and am trying to create a copy of a sheet from one workbook to another workbook using the following code:
Workbooks.Open Filename:=bw_path & "\" & bw_file
'Opening the file from which I want to copy.
Sheets(bw_sh).Copy After:=ThisWorkbook.Sheets(3)
'Trying to copy the open worksheet into the workbook housing the macro.
I am getting the "subscript out of range" error message. I inherited this code and it used to work before. What am I doing wrong and what would be a suggested solution? Any help will be appreciated?
[641 byte] By [
NutoVBA] at [2008-1-7]
Hi,
Have you checked the contents of the variable bw_sh and that the activeworkbook contains a sheet with that name?
Does the workbook containing the macro have 3 sheets?
The workbook containing the macro does have 3 sheets.
I also tried to perform the action (that is copy the sheet from the one workbook to the workbook housing the macro) whilst recording a macro in Excel and this is what Excel recorded:
Sheets("D 1").Select
Sheets("D 1").Copy After:=Workbooks("BW vs SAP check.xls").Sheets(3)
When I mouse over bw_sh in the macro, the value of bw_sh is reflected as "D1"
"BW vs SAP check.xls" is the workbook housing the macro.
I cannot see why it does not work.
If bw_sh is the name of a variable, you would not want to enclose it in quotes. If you do that, it will look for a sheet named bw_sh instead of a sheet named according to the variable bw_sh (eg "D1").
Is the sheet name "D 1" or "D1"? Your recorded macro example shows "D 1" (with a space), but you say when you hover over bw_sh it shows "D1" (without a space). The sheet name in the macro will need to match the exact sheet name in the workbook. So either:
-
change the sheet name in the workbook so that it matches what the macro expects (this is probably the easier option if you're not familiar with VBA); OR
-
the macro needs to be changed so that wherever bw_sh is being defined (this will probably be a line that starts with "bw_sh = "), it defines it as "D 1".
