Thread: Consolidating Columns from Multiple Sheets

After the help I've gotten in other threads I'm almost done my project!!! I will end up with multiple sheets that automatically pull data from a master budget. Each sheet will vary in length and will allow for automatic detection of new accounts on the master budget. The reason for having separate sheets for each section is to that I can quickly verify the totals for each section in case the automated list doesn't balance. Now what I want to do is make a sheet that consolidates all of the other sheets. I included a simple attachment to show you what I am trying to do. I made some attempts at variations of INDEX and a few other things but I could not get it to work.

Any suggestions?

Thank you.

If there will be more than 1 or 2 data sheets, I would use a macro so that it self-expands. Is that OK?

This is what I mean about the simplicity. This macro would cause the Consolidated sheet to update itself every time you look at it, even if you add 100s and 100s of Data sheets.

Code:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Application.ScreenUpdating = False

    Me.UsedRange.Clear
    
    For Each ws In Worksheets
        If ws.Name <> Me.Name Then _
            ws.UsedRange.Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next ws

Application.ScreenUpdating = True
End Sub

1) Right-click on the Conslidated tab and select VIEW CODE
2) Copy and Paste in your code (given above)
3) Get out of VBA (Press Alt+Q)
4) Save as a macro-enabled workbook

Now work on all your data sheets, and each time you look at the Consolidated sheet, it will be current.

well its sunay here but monay will be good lol

Originally Posted by martindwilson

well its sunay here but monay will be good lol

it's only satay (has that got peanuts in) in Ireland..well we normally see what Britain does and follow suit so I'll expect an update from the government anytime soon....***waiting with bated breath***

Are columns A and B filled in on all rows of concern? Not some rows have "A" but no "B", all the rows you want have both "A" and "B"...
...or even simpler, do all the rows you want at least have an "A" value? We can grab all cells that have values in column A and their partner cell in "B", too.

Are the values text? Numbers? Both?
Are ALL the values the results of formulas?

A sample sheet with before/after assistance makes this much simpler.

Originally Posted by The Phil

I guess the easiest way to sum up what I want is; Can the consolidation sheet do the equivalent of cutting and pasting the "values only" of columns A and B from all of the other sheets as values, with no white space?

Yes, not a problem, I don't imagine. A fully demonstrative sample workbook should make it simple to devise.

I can't just upload our budget but I can cut and paste some parts of it to give you an idea.

It shouldn't take that long to dummy down a copy of the workbook, stripping out the read sensitive info leaving dummy data and a working layout.

Originally Posted by JBeaucaire

A fully demonstrative sample workbook should make it simple to devise.

If you save the attached "Budget Example" into C:\Temp2 then it will work. You need to open the budget example file first, (don't need to update it) and then open the autoit file and update it. Everything about it should be automatic, with the exception of the ability to change the variables on the Controls sheet. The consolidation sheet has the code you posted earlier, but it doesn't seem to get values properly from "Parks". To restate what I want, I need the consolidated sheet to have the values of columns A, B, and C only, with no white space between. Having them all come in as numbers would be great but if that's too much work and some come in as text I can always just copy and paste.

Thanks!!

Having the values appear as numbers mean the leading zeros will disappear.

Try this:

Code:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long
Application.ScreenUpdating = False

    Me.Range("A:C").ClearContents
    
    For Each ws In Worksheets
        If ws.Name <> Me.Name And ws.Name <> "Controls" Then
            LR = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A3:C" & LR).Copy
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
    Next ws

    Range("G1") = 1
    Range("G1").Copy
    Range("A2", Range("C2").End(xlDown)).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
    Range("G1").ClearContents
    Columns("A:C").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    LR = Application.WorksheetFunction.Match(Application.WorksheetFunction.Max(Range("A:A")), Range("A:A"), 1) + 1
    Range("A" & LR, "C" & Rows.Count).Clear
    
    Range("A1").Select
Application.ScreenUpdating = True
End Sub