VBA 9. Consolidating files from Folder in one sheet

‘*************consolidating files from folders using vba************** ‘ 
Dim fileList() As String
Dim fpath As String
Dim file As String

Dim sourceWB As Workbook
Dim sourceWs As Worksheet
Dim fileCount As Integer

Dim bFirstCopy As Boolean
Dim targetWS As Worksheet

Set targetWS = ThisWorkbook.Sheets(“Sheet1”) ‘ this is the sheet where data will be consolidated
fileCount = 0


‘THIS IS THE PATH OF FOLDER WHERE SOURCE FILES ARE LISTED, CHANGE IT ACCORDING TO YOUR PATH

fpath = “C:\Users\Sandeep Singh\Desktop\import vba\”

file = Dir(fpath & “*.xlsx”)
While file <> “”

fcount = fcount + 1

ReDim Preserve fileList(1 To fcount)
fileList(fcount) = file

file = Dir() ‘next file
Wend

Dim j As Integer
Dim lastrow As Integer

lastrow = 1

For j = LBound(fileList) To UBound(fileList)

Set sourceWB = Workbooks.Open(fpath & fileList(j))
Set sourceWs = sourceWB.Sheets(“Sheet1”)

If bFirstCopy = False Then

With sourceWs ‘copy data for first file

.UsedRange.Copy Destination:=targetWS.Cells(lastrow, 1)
lastrow = targetWS.Cells(Rows.Count, “A”).End(xlUp).Row + 1

bFirstCopy = True ‘we have copied first file
sourceWB.Close savechanges:=False
End With

Else

With sourceWs ‘copy data for second time file

.UsedRange.Offset(1, 0).Copy Destination:=targetWS.Cells(lastrow, 1)
lastrow = targetWS.Cells(Rows.Count, “A”).End(xlUp).Row + 1
sourceWB.Close savechanges:=False

End With

End If

Next j
End Sub

Leave a Reply