VBA Problem: Subscript Out of Range

[craig159753]

Hi all,

I am seeing a strange error when running a simple line of VBA code.
When I personally run the VBA macro I do NOT get the error message but when my colleague runs the VBA macro they get the "subscript out of range" error.

The line of code causing this is the following.

Dim workbookname as string
Dim worksheetname as string

Workbooks(workbookname).Worksheets(worksheetname).Range("C177:C1357").Copy

It is merely selecting the values from column C from row 177 to row 1357 and copying them.
Any idea why this runs fine for me but fails for my colleague?


Thanks
Craig

 

[steve the fish]

Add this bit of code just before the offending line executes:

For Each bk In Workbooks
    If LCase(bk.Name) = LCase(workbookname) Then
        MsgBox "Book ok"
    End If
Next
For Each sh In Worksheets
    If LCase(sh.Name) = LCase(worksheetname) Then
        MsgBox "Sheet ok"
    End If
Next

What msgboxes do you see?

 

[craig159753]

Hi Steve,

I figured out the problem, it was the workbookname value.

Originally I was deriving the workbookname like so

workbookname = Trim(Left(WB.Name, InStr(ActiveWorkbook.Name, ".") - 1))

Which was working fine for me, but not my colleague, I therefore simply updated this line to the following

workbookname = ActiveWorkbook.Name

All working for everyone now.

Thanks
Craig