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

kinnoyu

New Member
Joined
Apr 5, 2021
Messages
1
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

KasangoJS

New Member
Joined
Mar 14, 2021
Messages
40
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows
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

Active Member
Joined
Dec 16, 2011
Messages
323
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
KasangoJS, How about posting your solution so we can all benefit from it?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,560
Members
417,220
Latest member
lam150498

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
Top