Consolidating Data from multiple tabs to a single tab; then based on variable

B

briley

New Member
Joined
Feb 5, 2015
Messages
15
Thank you for your time. This is my second post and I've learned so much from reading through all of your other responses. THANK YOU for passing along the knowledge and teaching us in the process

Here's my issue:

- I have a workbook with 15 sheets.
- The data from 9 of those sheets (Sheets 5-13) needs to be consolidated into one. (Sheet 2 "CONSOLIDATION - Team")
- The data on those 9 sheets is positioned from A3 to I3 with the number of rows varying by sheet, but month.
- All of the data is fielded uniformly from sheet to sheet and would like to keep it that way (so pasting Values only).

I'm trying to build a single macro that I can assign to a button on the CONSOLIDATION - Team sheet.

With all the searching I done, I would have thought I could find out how to do this one, but they all have variations that I haven't been able to muddle through.

___________________________________

Once I have the CONSOLIDATION - Team sheet, I then need to parse the data a bit further

- On another of the 15 sheets (Sheet 1 "CONSOLIDATION - Overview"), I need to pull in specific columns of information from the CONSOLIDATION - Team sheet based on a variable in column C
- On rows with column C = "Green", copy data in columns A, F and I and paste onto CONSOLIDATION - Overview starting in F5, then continuing down how ever many rows there is data.
- On rows with column C = "Blue", copy data in columns A, F and H and paste onto CONSOLIDATION - Overview starting in J5, then continuing down how ever many rows there is data.

Easy right?????

Thanks in advance for anyone's help.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Give this a try. The pasting area on Sheet 2 was not specified, so I improvised.
Code: 
Sub cons()
Dim sh As Worksheet, U1 As Range, U2 As Range
    For i = 5 To 13
        Sheets(i).Range("A3", Sheets(i).Cells(Rows.Count, "I").End(xlUp)).Copy
        Sheets("CONSOLIDATION - Team").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    Next
    Sheets("CONSOLIDATION - Team").Range("A2", Sheets("CONSOLIDATION - Team").Cells(Rows.Count, i).End(xlUp)).AutoFilter 3, "Green"
    With Sheets("CONSOLIDATION - Team")
        Set U1 = Union(.Columns(1), .Columns(6), .Columns(9))
        U1.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATION - Overview").Range("F5")
        Set U2 = Union(.Columns(1), .Columns(6), .Columns(8))
        U2.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATION - Overview").Range("J5")
        .AutoFilterMode = False
    End With
End Sub
 
Upvote 0
JLGWhiz said:
Give this a try. The pasting area on Sheet 2 was not specified, so I improvised.
Code: 
Sub CONSOLIDATION_Team()
Dim sh As Worksheet, U1 As Range, U2 As Range
    For i = 5 To 13
        Sheets(i).Range("A3", Sheets(i).Cells(Rows.Count, "I").End(xlUp)).Copy
        Sheets("CONSOLIDATION - Team").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    Next
    Sheets("CONSOLIDATED - Team").Range("A2", Sheets("CONSOLIDATED - Team").Cells(Rows.Count, i).End(xlUp)).AutoFilter 3, "Green"
    With Sheets("CONSOLIDATED - Team")
        Set U1 = Union(.Columns(1), .Columns(6), .Columns(9))
        U1.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("F5")
        Set U2 = Union(.Columns(1), .Columns(6), .Columns(8))
        U2.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("J5")
        .AutoFilterMode = False
    End With
End Sub
Click to expand...

________________________________________


So close.... The consolidation worked perfectly (after I corrected my own error of the Sheet name of CONSOLIDATED instead of CONSOLIDATION)

When it starts on the next section of code, I get a Run-time error '1004'; Cannot change part of a merged cell. When I select Debug, it highlights:
U1.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("F5")

I don't have any merged cells either within the data or on the CONSOLIDATED - Overview sheet.

??? thoughts ???
 
Upvote 0
.... and not that it matters, because the way you coded it works just fine, but Sheet 2 starts on A4 (if you think it's better that I change something).
 
Upvote 0
The code seems to be putting a filter on Sheet2, column C. Not sure if that plays into the issue.
 
Upvote 0
briley said:
The code seems to be putting a filter on Sheet2, column C. Not sure if that plays into the issue.
Click to expand...

The filters get the Green and Blue if the cells are not merged. Merged cells are a pain to work with. Do you have only certain cells merged, or are the entire columns merged, and if so which ones? Maybe I can unmerge them on Sheet 2 and make it work.
 
Upvote 0
I added a line of code to the procedure that should fix the merge thing. But I don't know if it will affect the column references, so try it an let me know what happens.

Code: 
Sub CONSOLIDATION_Team()
Dim sh As Worksheet, U1 As Range, U2 As Range
    For i = 5 To 13
        Sheets(i).Range("A3", Sheets(i).Cells(Rows.Count, "I").End(xlUp)).Copy
        Sheets("CONSOLIDATED - Team").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    Next
    Sheets("CONSOLIDATED - Team").Range("A2", Sheets("CONSOLIDATED - Team").Cells(Rows.Count, i).End(xlUp)).AutoFilter 3, "Green"
    With Sheets("CONSOLIDATED - Team")
	.Cells.Unmerge
        Set U1 = Union(.Columns(1), .Columns(6), .Columns(9))
        U1.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("F5")
        Set U2 = Union(.Columns(1), .Columns(6), .Columns(8))
        U2.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("J5")
        .AutoFilterMode = False
    End With
End Sub
 
Upvote 0
JLGWhiz said:
The filters get the Green and Blue if the cells are not merged. Merged cells are a pain to work with. Do you have only certain cells merged, or are the entire columns merged, and if so which ones? Maybe I can unmerge them on Sheet 2 and make it work.
Click to expand...

___________________________

Ahhh. I see what it is. I have a page title across the top of Sheet 2. I unmerged that and the macro ran through, but it capture my page title and the headers in with the data. Is there a way to start pulling data on Row 4?
 
Upvote 0
Is there a way to start pulling data on Sheet2 Row 4, so that the Title and headers are not considered?

Since it ran all the way through, I was able to review the data on Sheet1 brought over from Sheet2 and noticed the code is only pulling in the Green and populating both sections on Sheet1, instead of Green populating one section and Blue populating the other.

Hope that makes sense.
 
Upvote 0
Run This one and see if it goes all the way through without a hiccup. I didn't re-test it because it is late here and I am going to bed.
Code: 
Sub CONSOLIDATION_Team3()
Dim sh As Worksheet, U1 As Range, U2 As Range, lr As Long
    For i = 5 To 13
        Sheets(i).Range("A3", Sheets(i).Cells(Rows.Count, "I").End(xlUp)).Copy
        Sheets("CONSOLIDATED - Team").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    Next
    With Sheets("CONSOLIDATED - Team")
        lr = .Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
        .Cells.UnMerge
        .Range("A3", Sheets("CONSOLIDATED - Team").Cells(Rows.Count, i).End(xlUp)).AutoFilter 3, "Green"
        Set U1 = Union(.Range("A4:A" & lr), .Range("F4:F" & lr), .Range("I4:I" & lr))
        U1.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("F5")
        .AutoFilterMode = False
        .Range("A3", Sheets("CONSOLIDATED - Team").Cells(Rows.Count, i).End(xlUp)).AutoFilter 3, "Blue"
        Set U2 = Union(.Range("A4:A" & lr), .Range("F4:F" & lr), .Range("H4:H" & lr))
        U2.SpecialCells(xlCellTypeVisible).Copy Sheets("CONSOLIDATED - Overview").Range("J5")
        .AutoFilterMode = False
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Consolidate data from multiple sheets based on values selected in dependent list boxes
Replies
0
Views
201
jconda
J
G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
254
bobaol
B
D
  • Question
VBA Script
Replies
3
Views
115
Micron
M
E
  • Question
Sumifs, Index, formula only summing first value
Replies
1
Views
288
etaf
etaf
L
  • Question
Copy Subtotals from Source Workbook to Current Workbook
Replies
1
Views
245
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,593
Messages
6,120,435
Members
448,961
Latest member
nzskater

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