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