Hello, New here. Hope Im in the right place.
I found a thread that seems to be same problem I am having. I tried using the code provided to other member and am NOT getting results. Here is what I need.
I have 500 workbooks, all with same header and sheet names.
I need to combine the content all into a single workbook with all sheets merged into workbook. data all starts at A2 and varies in row content from 1 row to 5000 per workbook/sheet.
The code is what I found on this site and tried to modify to work before I asked the question.
Application.ScreenUpdating = False
Dim ParentFolder As String
Dim WS As Worksheet
Dim ThisWB As Workbook
Dim ThisWS As Worksheet
Dim OtherWB As Workbook
Dim WScount As Integer
Dim File As Variant
Set ThisWB = ActiveWorkbook
Set ThisWS = ActiveSheet
ParentFolder = "E:\TestMergeVAI\test" 'Change as required
If Right(ParentFolder, 1) <> "" Then ParentFolder = ParentFolder & ""
File = Dir(ParentFolder)
WScount = 0
While File <> ""
Set OtherWB = Workbooks.Open(ParentFolder & File)
For Each WS In OtherWB.Sheets
If Left(UCase(WS.Name), 4) = "Step" Then
WScount = WScount + 1
If WScount = 1 Then WS.Rows(1).EntireRow.Copy Destination:=ThisWS.Rows(1).EntireRow
WS.Range("A2:N" & Range("A" & WS.Rows.Count).End(xlUp).Row).Copy
ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Set OtherWB = Nothing
File = Dir
Set ThisWB = Nothing
Set ThisWS = Nothing
Application.ScreenUpdating = True
MsgBox WScount & " worksheets transferred successfully.", vbInformation, "Done"
When I run the code. I get a window that says 0 book combined
Hope you all can help