I have pieced together a macro from reading posts here. Everything works but I would just like it to start pasting the data at row 4 instead of row 2. Any ideas? Thanks.
Code:
Sub GetMyData()
Dim myDir As String, fn As String, sn As String, sn2 As String, n As Long, NR As Long
'***** Change Folder Path *****
'myDir = "C:\TestData" 'for testing
myDir = "C:\Users\stepone\Desktop\DATA FOR EXCEL"
'***** Change Sheetname(s) *****
sn = "Summary Data"
fn = Dir(myDir & "\*.xls")
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
With ThisWorkbook.Worksheets(1)
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
With .Range("A" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B1"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B3"
.Value = .Value
End With
With .Range("C" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B4"
.Value = .Value
End With
With .Range("D" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B6"
.Value = .Value
End With
With .Range("E" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B8"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B19"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B20"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B21"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B23"
.Value = .Value
End With
With .Range("J" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B24"
.Value = .Value
End With
With .Range("K" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B25"
.Value = .Value
End With
With .Range("L" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B26"
.Value = .Value
End With
With .Range("M" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B28"
.Value = .Value
End With
With .Range("N" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B29"
.Value = .Value
End With
With .Range("O" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B31"
.Value = .Value
End With
End With
End If
fn = Dir
Loop
End Sub