I have been searching for awhile now trying to find a solution, i can find similar solutions but i cannot get any to work even with tweaks and amendments.
I have a master workbook called 'Master.xlsb' with 1 sheet called 'Summary'. I have a list of 189 files in one folder called 'EmailAttachments'.
Each individual file will have a different amount of rows so i would like to loop through all files and copy from range '"B7:B" & LastRow' and paste data below last row containing data in 'Master.xlsb' (Which will increase as data is pasted in).
Also, I would like to have the file name in column A starting from '"A7"' so i know which file the data is from.
Thanks in advance.
EDIT:
I managed to get the code working below:
Public Sub DataToSummary()
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim LastRowMaster As Long
Dim DataRowsMaster As Long
Dim LastRowSource As Long
Dim FileNameSource As String
Dim i As Integer, intValueToFind As Integer
Path = "C:\Example\Path\"
Filename = Dir(Path & "*.xlsx")
Do While Len(Filename) > 0
Set wbk = Workbooks.Open(Path & Filename)
For i = 1 To 500
If Cells(i, 1).Value = intValueToFind Then
GoTo Skip
End If
Next i
LastRowSource = Cells(Rows.Count, 2).End(xlUp).Row
DataRowsSource = LastRowSource - 6
FileNameSource = Left(Filename, Len(Filename) - 5)
Workbooks(Filename).Sheets(1).Range("B7:M" & LastRowSource).Copy
Workbooks("Master.xlsb").Activate
LastRowMaster = Cells(Rows.Count, 6).End(xlUp).Row
ThisWorkbook.Sheets(1).Range("F" & LastRowMaster + 1).PasteSpecial xlPasteValues
ThisWorkbook.Sheets(1).Range("B" & LastRowMaster + 1 & ":B" & LastRowMaster + DataRowsSource).Value = FileNameSource
ThisWorkbook.Sheets(1).Range("C1:E1").Copy
ThisWorkbook.Sheets(1).Range("C" & LastRowMaster + 1 & ":E" & LastRowMaster + DataRowsSource).PasteSpecial xlPasteFormulas
Skip:
wbk.Close True
Filename = Dir
Loop
End Sub