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
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.
It's this part I'm really having trouble modifying.
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.