Excel for Mac: Combine multiple sheets into one

[yichuansancun]

Hello -

I have a workbook that have multiple sheets. The sheets have the same data structure. How do I combine them into one without doing copy and paste? I tried using consolidate, but consolidate does aggregate on the values. In my case, I don't need to do any aggregation. It is all text. I simple just want all the data to be one the same sheet.

Thanks,
Perri

 

[cyrilbrd]

Would you accept vba?
Given that all sheets are similar (number of columns and header), how many sheets would you want to combine?

 

[yichuansancun]

Would you accept vba?
Given that all sheets are similar (number of columns and header), how many sheets would you want to combine?

I prefer no VBA :P
there are 10 sheets. I found ASAP excel add-in may do the job but it is not available on mac

 

[cyrilbrd]

I prefer no VBA :P
there are 10 sheets. I found ASAP excel add-in may do the job but it is not available on mac

Noted, sorry I am not sure that a formula would give you better results than a vba or vba based solution. Maybe other MAC users on this forum might have a better idea.

 

[aggiemarine07]

Did you ever get a VBA solution? I wanting to do something similar but ant figure out how. Thanks.

 

[cyrilbrd]

Did you ever get a VBA solution? I wanting to do something similar but ant figure out how. Thanks.

How many sheets would you wish to merge?

 

[aggiemarine07]

How many sheets would you wish to merge?

6 sheets into 1.

Ideally I would want Sheet2 through Sheet7 merged into my "Summary" sheet (all the same headers) and then have that information sorted by Date and Time (column A and C) moving all blanks to the bottom.

I posted my question here in the forum but havent gotten a response so I started looking at what other people did and try to reverse-engineer it for myself, which led me to here.

 

[cyrilbrd]

Noted, not in front of my computer, will get back to you with proposal, can't type from my phone...

 

[cyrilbrd]

Given that each sheet contains the same number of Column and same header.

Code is:

Sub Populate()    Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).ClearContents
    
    Sheets("[COLOR=#574123]Sheet2[/COLOR]").Select
        Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy
    Sheets("[COLOR=#0000FF]Summary[/COLOR]").Select
        Range("A2").Select
            ActiveSheet.Paste
            
    Sheets("[COLOR=#574123]Sheet3[/COLOR]").Select
        Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy
    Sheets("[COLOR=#0000FF]Summary[/COLOR]").Select
        Range("A2").End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
    
    Sheets("[COLOR=#574123]Sheet4[/COLOR]").Select
        Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy
    Sheets("[COLOR=#0000FF]Summary[/COLOR]").Select
        Range("A2").End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
    
  Sheets("[COLOR=#574123]Sheet5[/COLOR]").Select
        Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy
    Sheets("[COLOR=#0000ff]Summary[/COLOR]").Select
        Range("A2").End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
    
Sheets("[COLOR=#574123]Sheet6[/COLOR]").Select
        Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy
    Sheets("[COLOR=#0000FF]Summary[/COLOR]").Select
        Range("A2").End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste

Sheets("[COLOR=#574123]Sheet7[/COLOR]").Select
        Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy
    Sheets("[COLOR=#0000FF]Summary[/COLOR]").Select
        Range("A2").End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste


    Application.CutCopyMode = False
End Sub

Create a button.
Assign macro.
Click on said button to populate data in sheet Summary

 

[aggiemarine07]

that worked, thanks!