Merge a sheet with specific name from multiple workbooks.

[shirazsafa]

Hello
There are several Excel files in one folder.
i ned to Merge a sheet with specific name from multiple workbooks.
For example, sheets(purchase)
with this formula, only the first sheets will be merged -
Please guide me if possible
I thank you in advance for your kindness and love
Thank you

Sub Excellearn()
Dim FileName As String
Dim Path As String
Dim Sheet As Worksheet
Path = "C:\Users\carsys 3\Desktop\Merge sheet\mahmole\"
FileName = Dir("C:\Users\carsys 3\Desktop\Merge sheet\mahmole\" & "*.xlsx")

Do While FileName <> ""
Workbooks.Open FileName:=Path & FileName
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ThisWorkbook.Sheets(1)
Workbooks(FileName).Close
Next
FileName = Dir()
Loop
End Sub

 

[shirazsafa]

If this name is changed the formula will not work
Sheet.Copy after:=ThisWorkbook.Sheets(1) if 'Sheets(1) change to Sheets(2)

 

[alansidman]

Here is a Power Query Option

Combine Data from Multiple Workbooks in Excel (using Power Query)

Power Query can be of great help when you want to combine data from multiple workbooks into one single worksheet. Here is a step-by-step guide on how to do this in Excel. You can combine data when it's stored in an Excel table, or even when it's not formatted as a table.

trumpexcel.com

 

[shirazsafa]

Here is a Power Query Option

Combine Data from Multiple Workbooks in Excel (using Power Query)

Power Query can be of great help when you want to combine data from multiple workbooks into one single worksheet. Here is a step-by-step guide on how to do this in Excel. You can combine data when it's stored in an Excel table, or even when it's not formatted as a table.

trumpexcel.com

Sorry, I need a VBA formula