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

kinnoyu

New Member
Joined
Apr 5, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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.

VBA Code:
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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 .
 
Upvote 0
KasangoJS, How about posting your solution so we can all benefit from it?
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top