I keep getting subscript out of range for the following code, I'm new to VBA so would greatly appreciate your help.
I'm trying to reference a table that contains various source workbooks and copy the data from here to "target" workbooks also contained in the sTable range.
Thanks, Ronan
Sub Import()
Dim sTable As String ' Source table
Dim sTarget As String ' Target range for output
Dim sHeader As String ' Header row from the input data
Dim sFileName As String ' File name to read from
Dim tFileName As String
Dim sInputSheet As String ' Worksheet to read from
Dim sRange As String ' Range to read from/copy
Dim tSheet As String
Dim tRange As String ' Range to paste into/Target Range
Dim sRow As Integer
Dim cRow As Integer
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
'Define source(s) and target(t) sheets
sTable = "rng_SourceData"
'loop through source table to copy and paste requred data
sRow = Range(sTable).Rows.Count
For cRow = 1 To sRow
'loop through source table to copy and paste requred data
sRow = Range(sTable).Rows.Count
For cRow = 1 To sRow
sFileName = Worksheets("I.Import").Range(sTable).Cells(cRow, 1)
sInputSheet = Worksheets("I.Import").Range(sTable).Cells(cRow, 2)
sRange = Worksheets("I.Import").Range(sTable).Cells(cRow, 3)
tFileName = Worksheets("I.Import").Range(sTable).Cells(cRow, 4)
tRange = Worksheets("I.Import").Range(sTable).Cells(cRow, 5)
tSheet = Worksheets("I.Import").Range(sTable).Cells(cRow, 6)
'Include all ranges in the input table
Call ImportDataSpreadsheet(sFileName, sInputSheet, sRange, tSheet, tRange)
Next cRow
End Sub
Sub ImportDataSpreadsheet(sFileName, sInputSheet, sRange, tSheet, tRange)
Dim SourceWorkbook As Excel.Workbook
Dim TargetWorkbook As Excel.Workbook
Dim TargetSheet As Excel.Worksheet
'Define Source workbook
Set SourceWorkbook = Workbooks.Open(Filename:=sFileName, Password:=False)
'Select.Workbook.Sheets.Open (sInputSheet)
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
'Copy
SourceWorkbook.Sheets(sInputSheet).Activate
SourceWorkbook.Sheets(sInputSheet).EnableSelection = xlNoRestrictions
SourceWorkbook.Sheets(sInputSheet).Range(sRange).Copy
'Define Target workbook
Set TargetWorkbook = ThisWorkbook.Worksheets("I.Import").Range(sTable).Cells(cRow, 4)
Set TargetSheet = TargetWorkbook.Sheets(tSheet)
'Paste
TargetWorkbook.Sheets(tSheet).Range(tRange).PasteSpecial Paste:=xlPasteValues
'Close and finish.
SourceWorkbook.Close savechanges:=False
End Sub