Help copying a specific named sheet from multiple workbooks onto one sheet in another workbook?

[kinnoyu]

I have a number of excel files that have two different named sheets (let's call them "x" and "y" for simplicity); so file1 has two sheets "x" and "y"; file2 has two sheets also named "x" and "y". I want to combine all of the worksheets named "x" onto a master file with a similar name such as "x" or "all x" and then do the same thing with "y" so that I have a master excel file with two sheets labeled "x" and "y".

I've had a lot of trouble trying to figure out if this is possible. Most code talks about just combining multiple worksheets onto one workbook. I know you can combine multiple worksheets into one on a single workbook but can you do that across multiple workbooks? The closest thing I've found so far is this

Sub LoopThroughDirectory()
Dim MyFile As String
Dim q As Long
q = 1
Dim Filepath As String
Filepath = “C:\copy-51\”
Application.ScreenUpdating = False
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0

Workbooks.Open (Filepath & MyFile)
Worksheets(“Sheet1”).Activate
ActiveSheet.UsedRange.Copy
ActiveWorkbook.Close Save = False

Application.DisplayAlerts = False

Sheets(q).Select
ActiveSheet.Paste Destination:=Worksheets(q).Range(“A1”)
Application.CutCopyMode = False
q = q + 1
If q > 4 Then
Exit Sub
End If

MyFile = Dir
Loop
ActiveWorkbook.Save
Application.ScreenUpdating = True

End Sub

However, the video I got it from is describing copying data and then putting it into different sheets on a main workbook. I want all the data that gets copied to go onto a specific sheet that has been named. I also want to be able to choose which sheet I'm taking the data from, not just the first sheet. I'm having trouble trying to figure out how I can paste the data from all the different workbooks onto the next empty row so that I have a long list with all the data from all the workbooks onto one specific sheet.

Sheets(q).Select
ActiveSheet.Paste Destination:=Worksheets(q).Range(“A1”)
Application.CutCopyMode = False
q = q + 1
If q > 4 Then
Exit Sub
End If

It's this part I'm really having trouble modifying.

 

[jskasango]

I have a , solution that works much better, got it right here from Mbaadi. The code lets you select the folder where your workbooks are sitting, then it checks all the .xl* workbooks and looks for Sheet "X" it then merges them into one workbook .

 

[vw412]

KasangoJS, How about posting your solution so we can all benefit from it?