Sub ConsolidateCommSheetFromAllWbk()
Dim folpath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
folpath = .SelectedItems(1)
End With
Dim fs, fol As Object, f
Dim wbk As Workbook
Set fs = CreateObject("scripting.filesystemobject")
Set fol = fs.getfolder(folpath)
Sheets(1).Cells.Clear
'Sheets(1).Cells(1, 1) = "Heading-1"
Dim shtname As String
shtname = InputBox("Enter SheetName")
For Each f In fol.Files
If UCase(fs.getextensionname(f.Name)) = "XLSX" Or _
UCase(fs.getextensionname(f.Name)) = "XLS" Then
Set wbk = Workbooks.Open(f.Path)
wbk.Sheets(shtname).Rows("1:" & Sheets(shtname).UsedRange.Rows.Count).Copy _
ThisWorkbook.Sheets(1).Range("a" & ThisWorkbook.Sheets(1).UsedRange.Rows.Count + 1)
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveWorkbook.Close False
End If
Next
'Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Set sht = Nothing
Set fol = Nothing
Set fs = Nothing
Set f = Nothing
ActiveSheet.UsedRange.Rows.AutoFit
MsgBox "It's Done"
End Sub
Above code working fine, issue data append copied data to the last used row
Code used to copy data from multiple workbooks from one folder and with the same sheet name
One folder
Multiple workbooks
Same sheet name in all workbooks
Issue :
Should start from ROW1
Consolidate into new workbook showing gaap between data copied from multiple sheets
Bookmarks