VBA Code To Consolidate Individual Spreadsheets to 1 Consolidated Spreadsheet in New Document
Wondering if this is an option:
Interested in combining 7 individual excel files (1 worksheet per file), with exactly the same layout, structure, and basic data). 6 Columns per worksheet laid out exactly the same in each excel file.
Looking to automatically consolidate the data between all the sheets to one excel file, and looking to put into a pivot table to view the information.
Any thoughts or suggestions? Things to consider?
anyone want to help me? I love excel and systems, but VBA doesn't seem like something that's teach yourself.
Try This
Hi Dear,
try this hope it will help you...
Dim Fso As New FileSystemObject
Dim fldr As Folder
Dim fl As File
Dim Mrcount As Long, Srcount As Long, sccount As Long
Dim path As String
Dim rng As Range
Dim wkb As Workbook
Sub wkb_consolidation()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select source folder"
.Show
path = .SelectedItems(1) & "\"
End With
Set fldr = Fso.GetFolder(path)
WRcount = wkb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each fl In fldr.Files
Workbooks.Open fl.path
Set Nsh = Workbooks(fl.Name)
Srcount = Nsh.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
sccount = Nsh.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
Nsh.Activate
Nsh.Sheets(1).Range(Cells(2, 1), Cells(Srcount, sccount)).Copy Destination:=wkb.Sheets(1).Cells(WRcount, 1)
Nsh.Close
wkb.Save
Next
MsgBox "done", vbInformation + vbOKOnly
Application.ScreenUpdating = True
End Sub
Warm regards,
Happy coding