"Subscript out of range" error when using variables as worksheet reference

[msthiagu]

Hi. I have been using excel macro as below and experiencing "Runtime error 9: Subscript out of range" error. It happens only when I use a variable to reference a worksheet within a workbook.

 

[RoryA]

That error just means that the sheet name is wrong (i.e. it doesn't exist in that workbook).

 

[msthiagu]

That error just means that the sheet name is wrong (i.e. it doesn't exist in that workbook).

I have tried to debug the code where the error has occured by introducing a msgbox statement just before that. The result was the sheet name (1029, etc.) available in the workbook. Screenshot of sheets in the workbook attached for your reference. Kindly suggest a probable solution.

 

[RoryA]

The code should work assuming the correct workbook is active at the time because you have explicitly declared the variable as a string, but you could try adding quotes like this:

Set wsdest1 = wb1.sheets("" & shname1)

I'm not sure why you are calculating lrow2 on a different sheet to the one whose cells you are reading.

 

[Alex Blakenburg]

The result was the sheet name (1029, etc.)

What is the significance of the "etc" ? What is the name in the "last" message box before it errors out ?

Also a lot of your declarations don't work and will result in the variables being variant
eg Your worksheet and long dim lines should look like this:

Dim wscopy1 As Worksheet, wsdest1 As Worksheet, wsinterim1 As Worksheet
Dim a As Long, lrow2 As Long, lrow3 As Long

 

[msthiagu]

The code should work assuming the correct workbook is active at the time because you have explicitly declared the variable as a string, but you could try adding quotes like this:

Set wsdest1 = wb1.sheets("" & shname1)

I'm not sure why you are calculating lrow2 on a different sheet to the one whose cells you are reading.

Hi. I tried the quote mentioned. But this doesn't work

Set wsdest1 = wb1.sheets("" & shname1)

My objective is to find a value in "wscopy1" worksheet. Go to the respective sheet in the workbook and copy paste data between those 2 sheets.
"Lrow2" is used to find the length of the row.

 

[RoryA]

You calculate lrow2 based on the data in Sheet20 (the Service sheet) but then when you use it in the loop, you're looking at the SCM sheet, which seems odd.

If you are still getting the error, then the sheet name does not exist in the specified workbook. There is no other cause.

 

[msthiagu]

What is the significance of the "etc" ? What is the name in the "last" message box before it errors out ?

Also a lot of your declarations don't work and will result in the variables being variant
eg Your worksheet and long dim lines should look like this:

Dim wscopy1 As Worksheet, wsdest1 As Worksheet, wsinterim1 As Worksheet
Dim a As Long, lrow2 As Long, lrow3 As Long

The msg box before the error pops out is attached. Declarations as suggested also doesn't help.
The same code works in other sheets in this workbook.

 

[msthiagu]

You calculate lrow2 based on the data in Sheet20 (the Service sheet) but then when you use it in the loop, you're looking at the SCM sheet, which seems odd.

If you are still getting the error, then the sheet name does not exist in the specified workbook. There is no other cause.

I found that bug and it is already corrected. Still the error persists.

 

[dmt32]

Hi,
try changing this line

Set wsdest1 = wb1.Sheets(shname1)

for this

Set wsdest1 = wb1.Worksheets(CStr(shname1))

and see if resolves your issue

Dave