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.
Option Explicit
Sub Extract_Files()
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
End If
Next WS
OtherWB.Close False
Set OtherWB = Nothing
File = Dir
Wend
ThisWB.Activate
ThisWS.Activate
Set ThisWB = Nothing
Set ThisWS = Nothing
Application.ScreenUpdating = True
MsgBox WScount & " worksheets transferred successfully.", vbInformation, "Done"
End Sub
When I run the code. I get a window that says 0 book combined
Hope you all can help