Macro to combine multiple workbooks

[spectraflame]

I would like to combine 12 separate workbooks into a single Workbook but have the data from each in a separate Worksheet. Any suggestions on how to get started?

 

[Ricksp]

Is that something that you are going to do frequently that requires a VBA macro?

 

[Joyner]

...Or maybe Power Query?

 

[spectraflame]

...Or maybe Power Query?

I will performs task at least once per month. I am not sure what Power Query is. This will eventually be used non technical excel users.

 

[Joyner]

If you have excel 2010, I believe you have to download Power Query from MS. If that is an option for you, there are some good videos that will help, if not an option, then VBA would be the way to go.

If you need to get VBA help, you should add more details, like are all of the files exactly the same for headings / fields - is there only one worksheet in each of the workbooks or multiples, etc.

 

[spectraflame]

If you have excel 2010, I believe you have to download Power Query from MS. If that is an option for you, there are some good videos that will help, if not an option, then VBA would be the way to go.

If you need to get VBA help, you should add more details, like are all of the files exactly the same for headings / fields - is there only one worksheet in each of the workbooks or multiples, etc.

Since this sheet would be used by multiple people, I would have to ensure the add-in was installed on each workstation so using VBA would be easier I believe. Each file will have the same number of columns and headings. There is only a single worksheet in each of the workbooks and they will always be in .csv format.

 

[Joyner]

When you say:

"... and they will always be in .csv format."

what do you mean. Have the csv files been imported into Excel, into separate columns / fields?

 

[spectraflame]

When you say:

"... and they will always be in .csv format."

what do you mean. Have the csv files been imported into Excel, into separate columns / fields?

The original files will be created by a different software program, one for each of the previous 12 months. They will have unique file names with the same number of columns but different rows. The end goal is to allow the end user to import the data and create a summary page for each of the 12 months. My original thought was to just have 12 blank tabs, one for each month, and have the user just City and paste the data into the appropriate sheet which may be the easiest way to do it.

 

[Joyner]

Hello, take a look at this and see if this is what you want to do (video from Mrexcel):

NOTE: Always test the code on a copy of your data.

https://www.youtube.com/watch?v=n9WcP18wXFA

Or see here:

https://www.mrexcel.com/forum/excel...le-worksheets-into-one-new-worksheet-vba.html

There are a lot of threads here covering combining worksheets using VBA.

Some code I found:

Sub Combine()
    Dim J As Integer
    Dim s As Worksheet

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined" Then
            Application.GoTo Sheets(s.Name).[a1]
            Selection.CurrentRegion.Select
            ' Don't copy the headings
            Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
            Selection.Copy Destination:=Sheets("Combined"). _
              Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub