Combining Worksheets into one Master Sheet

[balling_threes]

Hey

I've been looking through code online, but i'm still not understanding how I can combine data from multiple worksheets into one master worksheet.

I have 16 worksheets, with identical column formatting (6 columns), but with different amounts of rows.

The master sheet is called Master Holdings. The first worksheet's data should be placed stating on row 2, and the second sheet's data should follow right after sheet 1's data, etc...

Any help would be greatly appreciated! Thanks!
G

 

[jbeaucaire]

Try this macro:

Option Explicit

Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False

Set cs = Sheets("Master Holdings")
cs.Activate
Range("A2:F" & Rows.Count).ClearContents

    For Each ws In Worksheets
        If ws.Name <> "Master Holdings" Then
            NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
            LR = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2:F" & LR).Copy cs.Range("A" & NR)
        End If
    Next ws

Application.ScreenUpdating = True
End Sub

That same code could be put into a worksheet_activate macro so each time you select the Master Holdings sheet to look at it, it updates automatically:

Option Explicit

Private Sub Worksheet_Activate()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False

Set cs = Sheets("Master Holdings")
cs.Activate
Range("A2:F" & Rows.Count).ClearContents

    For Each ws In Worksheets
        If ws.Name <> "Master Holdings" Then
            NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
            LR = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2:F" & LR).Copy cs.Range("A" & NR)
        End If
    Next ws

Application.ScreenUpdating = True
End Sub

 

[ravishankar]

Hi
try the following codes

Sub G()
Dim e As Long
For e = 2 To Sheets.Count
x = Worksheets("Master Holdings").Cells(Rows.Count, 2).End(xlUp).Row + 2
Worksheets("Master Holdings").Cells(x, 1) = Worksheets(e).Name
Worksheets(e).UsedRange.Copy
Worksheets("Master Holdings").Range("B" & x).PasteSpecial xlPasteAll
Next e
MsgBox "collating is complete."
End Sub

Ravi

 

[balling_threes]

Thanks both of you for you suggestions.
Ravi, your code pasted all the information twice, and the second time it pasted, it was one column further to the right then the first time it pasted (i have no idea why it did this looking at the code)

And JBeaucaire, your code worked FANTASTICALLY. Thanks for putting in the time to code something truly amazing.

Again thanks to both of you for putting time in to resolve my vba issues. Have a Great day!

 

[balling_threes]

I have a quick followup to your code JBeaucaire.

I was wondering how would i be able to tweak the code, to just have just run the macro for specific sheets, called "SmallCap" and "New Perspective".
So basically on a sheet called "International", I want to consolidate the info from SmallCap and New Perspective.

I tried doing this to the code:

For Each ws In Worksheets
        If ws.Name = "Small Cap" Or "New Perpsective" Then
            NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
            LR = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2:F" & LR).Copy cs.Range("A" & NR)
        End If
    Next ws

But it didnt' work. Any ideas?

 

[balling_threes]

I figured it out ! Woot

 

[jbeaucaire]

Just for the sake of clarity, be sure to post your final solution(s) when you figure things out on your own, other people who may be researching later would be ill-served to find the final answer not included in the thread.

This would be my answer:

    For Each ws In Worksheets
        If ws.Name = "Small Cap" Or ws.Name = "New Perpsective" Then
            NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
            LR = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2:F" & LR).Copy cs.Range("A" & NR)
        End If
    Next ws

 

[balling_threes]

I have another question though, sorry.

I was wondering how I could combine info in a similar fashion, but rather than taking it from different worksheets, i want to extract certain data from one worksheet based on a cell value.

For example

Holdings Value Type
Apples 2 Fruit
Celery 1 Veggie
Oranges 4 Fruit
Berries 5 Fruit
Tomatoes 10 Veggie

So I want to copy and paste all the veggie information (holdings, value, type) based on the Type. And i want to paste it into a new sheet.

Is this possible?

 

[balling_threes]

You're right , here's the code I used.
I simply made the sheets i wanted to use visible, and hid all the other sheets, and used International holdings as my holder sheet.

Set cs = Sheets("International Holdings")
cs.Activate
Range("A2:F" & Rows.Count).ClearContents
    For Each ws In Worksheets
        If ws.Visible And ws.Name <> "International Holdings" Then
            NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
            LR = ws.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2:F" & LR).Copy cs.Range("A" & NR)
        End If
    Next ws

 

[jbeaucaire]

I want to extract certain data from one worksheet based on a cell value.

Holdings           Value             Type
Apples               2               Fruit
Celery               1               Veggie
Oranges              4               Fruit
Berries              5               Fruit
Tomatoes             10              Veggie

I want to copy and paste all the veggie information (holdings, value, type) based on the Type. And i want to paste it into a new sheet.

Is this possible?

"Is this possible"? Hehe...you tempt me to say "no". Silly question, yes? You mean "How might I approach this"?

This is simply an AutoFilter event. you can do it by hand quite easily, just turn on the DATA > FILTER > AUTOFILTER on that dataset and filter the TYPE column by VEGGIE. AutoFilter hides rows that do not match the filter criteria. Copy all the remaining visible data to another sheet. 10 seconds work.