Help With Copy and Paste Macro

[welshraz]

Hi,

I am trying to create a macro that copies and pastes all the data from one tab in a closed workbook into an open and active one. Two main considerations: the name of the source file changes every day and the amount of data with vary each day (some days one line of data, other times 10,000+).

I have set up an 'Inputs' tab so that I can manually adjust the source file name each day, with a button linked to the macro to run it. I have used this for other macros and has worked fine. This is what I have so far:

Sub PullClosedData()
Dim filePath_1 As String
Dim SourceWb_1 As Workbook
Dim TargetWb As Workbook

Set TargetWb = ActiveWorkbook
i = 5 'row number of first input sheet in "Inputs" tab

'Tab 16: Data - NonTask

filePath_1 = TargetWb.Sheets("Inputs").Range("B" & i).Value

Set SourceWb_1 = Workbooks.Open(filePath_1)

'Transfer values from x to y:
TargetWb.Sheets("Data - Non Task").Range("A1").Value = SourceWb_1.Sheets("Data - Non Task").Range("A1")
With SourceWb_1.Sheets("Data - Non Task").UsedRange

'Now, paste to y worksheet:
TargetWb.Sheets("Data - Non Task").Range("A1").Resize( _
.Rows.Count, .Columns.Count) = .Value

End With

SourceWb_1.Close

End Sub

I keep getting a 'Subscript out of range' error but no option to debug, so I have no idea where I'm going wrong. The source workbook opens so at least I'm getting that far.

I have absolutely no idea what I am doing as I'm very new to this. Please be kind!

 

[welshraz]

Anyone? I'm kinda stuck!