Excel VBA - Subscript out of range

Using Windows 7 Home 64bit - Office 365 - Excel.
I have written the following procedure that resides in a Module.
The very first statement (other than Dim statements) gets a Subscript out of Range error. The strSheetLocal string is set to "Sheet1" - I have checked. However, if I change the "Worksheets(strSheetLocal)" to "Worksheets(1)" the procedure runs to completion.
I have googled myself to death and tried all sorts of trial code but I'm stuck.
Why am I getting the Subscript out of range error?

Sub CopyRangeToWB (strSheetLocal As String, intStartRowLocal As Integer, intStartRowExternal As Integer, intNumOfRows As Integer)

'External sheet is always going to be Sheet1
'Columns to copy always 1 to 8

Dim wbkExternal                       As Workbook
Dim shtExternal                         As Worksheet
Dim shtLocal                               As Worksheet
Dim strExternalWBPathName As String
Dim rngLocal                              As Range
Dim rngExternal                         As Range

Set rngLocal = ThisWorkbook.Worksheets(strSheetLocal).Range(Cells(intStartRowLocal, 1), Cells(intStartRowLocal + intNumOfRows - 1, 8))

strExternalWBPathName = ThisWorkbook.Path & "\Data\Data.xlsx"
Set wbkExternal = Workbooks.Open(strExternalWBPathName)                
Set shtExternal = wbkExternal.Worksheets("Sheet1")                
Set rngExternal = shtExternal.Range(Cells(intStartRowExternal, 1), Cells(intStartRowExternal + intNumOfRows - 1, 8))
   
rngExternal.Value = rngLocal.Value

wbkExternal.Close True    'Close and save the workbook

End Sub
RzzBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Subscript out of range error only occurs when an object doesn't exit.

As you can see just after declaring variables your first line of code is this.....

Set rngLocal = ThisWorkbook.Worksheets(strSheetLocal).Range(Cells(intStartRowLocal, 1), Cells(intStartRowLocal + intNumOfRows - 1, 8))

While you have not set the strSheetLocal and it is empty, you are using it in the above line of code. That means you are not passing a Sheet's Name or Sheet Index withing Worksheets(?).
When you use Worksheets(1), it references the first sheet of the workbook irrespective of its name.

So before setting the rngLocal, set the strSheetLocal like......

Set strSheetLocal = Sheets("Sheet1") or Set strSheetLocal = Sheet1 or Set strSheetLocal = Sheets(1)
Then your rngLocal line of code will be this.....

Set rngLocal = strSheetLocal.Range(Cells(intStartRowLocal, 1), Cells(intStartRowLocal + intNumOfRows - 1, 8))
0
RzzBAuthor Commented:
I think you might have missed that strSheetLocal is declared in the sub statement. It's a parameter passed into the procedure.

Also I mentioned in the description that is is set to "Sheet1" and that I checked that. It is not empty.

Surely "Set strSheetLocal =" needs an object not a string?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Ah... I really missed that and that is because you didn't enclosed your code with code tags, it's hard to read otherwise.

So how do you call this sub routine. Are you sure that the value you are passing into strSheetLocal, a sheet similar to that value exists in your workbook?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

RzzBAuthor Commented:
Code tags - OK - I will find out how to do that. My first post here. Apols.

When the code halts with the error strSheetLocal is set to "Sheet1" - I can Debug.Print it to the locals window.

The workbook has four sheets. They all actually have names other that their Code Names - is that perhaps the problem? I have attached a file of the project window.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What is your calling sub routine? strSheetLocal needs to be passed as String.
If your sheet names are other than the default sheet names, you will need to either pass the actual sheet name or Sheet1.Name (where Sheet1 is the code name) withing the calling sub routine.

Also I think you forgot to attache the workbook.
0
RzzBAuthor Commented:
Apologies - I have now uploaded the screenshot.
OK - This is definitely my problem - I'm struggling to understand this correctly.
The worksheets do have manually applied names as you can see from the screenshot.
What I'm struggling with is exactly what I can put in the XXX parameter of .Worksheets(XXX)

Perhaps if I can explain my understanding of all this and you could tell me where I'm wrong.
Let's assume a workbook with a worksheet and that it's the leftmost worksheet.
It has a CodeName of "Sheet1" (and no Manual name applied) so I can refer to this sheet as...

,Worksheets(1) .... (an integer index) that refers to the leftmost worksheet whatever CodeName or manual name it has.

.worksheets("Sheet1") - (a string) is this really it's code name - or is it that the manual name defaults to the CodeName?

.worksheets(whatelse?).... anything else?


If the worksheet has a manually assigned name of "Members" as per my screenshot....
I can refer to the worksheet with code as...

,Worksheets(1) .... (an integer index) that refers to the leftmost worksheet whatever CodeName or manual name it has.

.Worksheets("Members")... (a string) the manually applied sheet name

.worksheets("Sheet1") (a string) it's codename - this was my understanding but it's obviously wrong because it doesn't work


I'm confused.....

And BTW - What method should I be using from a "best practices" point of view?
Many thanks for your help. Roy

Project Window Screenshot
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Based on your description, try something like this....

Sub CopyRangeToWB(strSheetLocal As String, intStartRowLocal As Integer, intStartRowExternal As Integer, intNumOfRows As Integer)

'External sheet is always going to be Sheet1
'Columns to copy always 1 to 8

Dim wbkExternal                 As Workbook
Dim shtExternal                 As Worksheet
Dim shtLocal                    As Worksheet
Dim strExternalWBPathName       As String
Dim rngLocal                    As Range
Dim rngExternal                 As Range

Set shtLocal = ThisWorkbook.Sheets(strSheetLocal)
Set rngLocal = shtLocal.Range(shtLocal.Cells(intStartRowLocal, 1), shtLocal.Cells(intStartRowLocal + intNumOfRows - 1, 8))

strExternalWBPathName = ThisWorkbook.Path & "\Data\Data.xlsx"
Set wbkExternal = Workbooks.Open(strExternalWBPathName)
Set shtExternal = wbkExternal.Worksheets("Sheet1")
Set rngExternal = shtExternal.Range(shtExternal.Cells(intStartRowExternal, 1), shtExternal.Cells(intStartRowExternal + intNumOfRows - 1, 8))
   
rngExternal.Value = rngLocal.Value

wbkExternal.Close True    'Close and save the workbook

End Sub

'**********************************
'Calling Sub-Routine
Sub CopyToDataWorkbook()
Call CopyRangeToWB("Members", 1, 1, 5)
End Sub
'**********************************

Open in new window

Notice the line Call CopyRangeToWB("Members", 1, 1, 5) which will copy the data from Members Sheet to the External workbook.

Also while setting the Range, if you are using Cells property, it also needs to qualify with the Sheet Reference as well like in the below line....
Set rngLocal = shtLocal.Range(shtLocal.Cells(intStartRowLocal, 1), shtLocal.Cells(intStartRowLocal + intNumOfRows - 1, 8))

Open in new window


Does this work now?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RzzBAuthor Commented:
sktneer many thanks for your help and patience.  
I now understand what it going on.
Also thank you for the hint re qualifying the Cells property. That was going to be my next question!
I'll try and be a little more succinct next time!
Roy
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Roy! Glad I could offer some help.
0
RzzBAuthor Commented:
Yes indeed - very helpful. Thank you very much.

I though I might mention that I also bumped into this link, which I also found very helpful with my understanding of this... So much to learn!!!

http://www.cpearson.com/excel/codemods.htm

After all this I'm still not quite sure what method of referencing sheets is "best practice"... if there is one that is...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office 365

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.