Combining Worksheets into one Master Sheet

B

balling_threes

Board Regular
Joined
Jun 30, 2009
Messages
85
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Try this macro:
PHP: 
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:
PHP: 
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
 
Last edited:
Upvote 0
Hi
try the following codes
Code: 
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
 
Upvote 0
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!
 
Upvote 0
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:

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?
 
Upvote 0
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:
PHP: 
    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
 
Upvote 0
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?
 
Upvote 0
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.

Code: 
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
 
Upvote 0
balling_threes said:
I want to extract certain data from one worksheet based on a cell value.

Code: 
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?
Click to expand...

"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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Macro to open multiple workbooks and copy a range of data based on todays date. Then paste the data in master workbook in one sheet under each other.
Replies
8
Views
584
Luke1690
L
O
  • Question Question
VBA code to merge data from files into one master file
Replies
3
Views
422
jkpieterse
jkpieterse
F
  • Question Question
Copy to Multiple Worksheets With Different Arranged Headers ROW by ROW
Replies
1
Views
394
Cubist
Cubist
A
  • Question Question
Assign Rows of Data to Employees from Master
Replies
9
Views
484
mumps
mumps
T
  • Question Question
Complex VBA to loop through each worksheet and extract text
Replies
3
Views
510
bobsan42
bobsan42

Forum statistics

Threads
1,223,276
Messages
6,171,139
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top