Combine Sheet1 from Multiple Workbooks into Single Workbook/Sheet

[slam]

Hi all,

I've found some VBA online to fulfill my requirements, but I cannot get it to work. Let me explain what I have and what I'm looking for:

I have ~150 .xlsx Workbooks, with the exact same format (which might be irrelevant):

• Sheet1 row 1 is a header row.
• Column A is a data validation list that gets its values from Sheet2 column B.
• Column B a data validation list that gets its values from Sheet2 column A.
• Columns C through AF are date columns for April, and numbers are recorded in the cells below a in varying numbers of rows (probably no more than 50 rows in any case).
• Sheet2 is hidden

Ultimately, I am trying to get the data from all Sheet1's on to one single worksheet on one single workbook. However, If I can just get all worksheets into a single workbook, it looks like ASAP Utilities might then be able to merge them onto one worksheet. Either way, I would greatly appreciate any help that can be offered.

This is the code I tried using:

[COLOR=#0000FF][FONT=inherit]Sub ConslidateWorkbooks()[/FONT][/COLOR][COLOR=#0000FF]'Created by Sumit Bansal from https://trumpexcel.com[/COLOR]
[COLOR=#0000FF]Dim FolderPath As String[/COLOR]
[COLOR=#0000FF]Dim Filename As String[/COLOR]
[COLOR=#0000FF]Dim Sheet As Worksheet[/COLOR]
[COLOR=#0000FF]Application.ScreenUpdating = False[/COLOR]
[COLOR=#0000FF]FolderPath = "C:\Users\slam\Desktop\April\"[/COLOR]
[COLOR=#0000FF]Filename = Dir(FolderPath & "*.xlsx*")[/COLOR]
[COLOR=#0000FF]Do While Filename <> ""[/COLOR]
[COLOR=#0000FF] Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True[/COLOR]
[COLOR=#0000FF] For Each Sheet In ActiveWorkbook.Sheets[/COLOR]
[COLOR=#0000FF] Sheet.Copy After:=ThisWorkbook.Sheets(1)[/COLOR]
[COLOR=#0000FF] Next Sheet[/COLOR]
[COLOR=#0000FF] Workbooks(Filename).Close[/COLOR]
[COLOR=#0000FF] Filename = Dir()[/COLOR]
[COLOR=#0000FF]Loop[/COLOR]
[COLOR=#0000FF]Application.ScreenUpdating = True[/COLOR] [COLOR=#0000FF][FONT=inherit]End Sub[/FONT][/COLOR]

Initially I was getting an error on Filename = Dir(FolderPath & "*.xlsx*") and without changing anything as far as I'm aware, I'm now getting an error on Sheet.Copy After:=ThisWorkbook.Sheets(1). Either way, nothing is happening so far. Is having a 2nd sheet in the workbook messing it up? Or might the data validation be messing it up? I don't know if I should have my workbook I want them combined into in the same folder or not, but either way, it doesn't seem to make a difference.

Thank you so much!

 

[slam]

I just found some other code, and this seems to have done exactly what I was looking for!

Sub mergeFiles()    'Merges all files in a folder to a main file.
    
    'Define variables:
    Dim numberOfFilesChosen, i As Integer
    Dim tempFileDialog As FileDialog
    Dim mainWorkbook, sourceWorkbook As Workbook
    Dim tempWorkSheet As Worksheet
    
    Set mainWorkbook = Application.ActiveWorkbook
    Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    'Allow the user to select multiple workbooks
    tempFileDialog.AllowMultiSelect = True
    
    numberOfFilesChosen = tempFileDialog.Show
    
    'Loop through all selected workbooks
    For i = 1 To tempFileDialog.SelectedItems.Count
        
        'Open each workbook
        Workbooks.Open tempFileDialog.SelectedItems(i)
        
        Set sourceWorkbook = ActiveWorkbook
        
        'Copy each worksheet to the end of the main workbook
        For Each tempWorkSheet In sourceWorkbook.Worksheets
            tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
        Next tempWorkSheet
        
        'Close the source workbook
        sourceWorkbook.Close
    Next i
    
End Sub