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

Re: Subscript out of range

Andy Pope


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

Re: Subscript out of range


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.

Re: Subscript out of range



Sheets("bw_sh").Copy After:=ThisWorkbook.Sheets(3)

Best Regards


Re: Subscript out of range


I tried it but it does not work. Thanks, anyway.

Re: Subscript out of range

Cringing Dragon

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".

Re: Subscript out of range


Thanks, this was helpful. That was the problem.