Consolidating two Worksheets into one separate Worksheet

[Blanchetdb]

Hi

I have two worksheets (SingleActions) & (BulkActions); looking to combine both into worksheet (CombineActions)
both Worksheets have the same headers and same number of columns (A:T)

take note that there are a number of other worksheets in this workbook but only looking to combine the two mentioned above

I am looking to use VBA to save the person from having to use the Data/consolidate option in the ribbon

any assistance would be greatly appreciated

thanks

 

[Joe4]

VBA might not be necessary.
How about using the new VSTACK function?
See: Combine data in multiple worksheets...

 

[Blanchetdb]

VBA might not be necessary.
How about using the new VSTACK function?
See: Combine data in multiple worksheets...

this is what I have (sample)

SingleAction Sheet

Number	Subtype	Request Subtypes	Bulk	Count	Location	Language	Security	Abolish	Creation
1090	Unique		no		0	0	0	0	0
1101	Security		no		0	0	1	0	0
1102	Linguistic Profile		no		0	0	0	0	0

BulkAction Sheet

Number	Subtype	Request Subtypes	Bulk	Count	Location	Language	Security	Abolish	Creation
1090	Unique			4	0	0	0	0	4
1102	Linguistic Profile			5	0	5	0	0	0

Result needed in CombineActions Sheet (with the same header as above)

1090	Unique			4	0	0	0	0	4
1101	Security			1	0	0	1	0	0
1102	Linguistic Profile			5	0	5	0	0	0

not sure how VSTACK will do that .... When I use the consolidate function in the ribbon, I get the desired result but its manual and I need to avoid that

 

[Joe4]

Seeing the data definitely clarifies things - that is NOT what I thought you were asking.
I though you had two data lists, and wanted to list them both on one page, one above the other.
But it appears that you have common values on both pages that you want to combine/sum together.

This is really a database type scenario (would work better in Acces), but I am thinking Power Pivot or Power Pivot may be the best Excel options (though I don't use them much, as I don't do database work in Excel). There may be a way to do it with some of the newer advanced Excel functions, but that is a little bit beyond my knowledge.

What happens if you turn on the Macro Recorder, and record yourself doing the manual "Data consolidate" method?
Many times, this will record and create VBA code that you may be able to use, with a little tweaking.
Try doing that, and see if it records any code, and if it does, please post it here.

 

[Blanchetdb]

Seeing the data definitely clarifies things - that is NOT what I thought you were asking.
I though you had two data lists, and wanted to list them both on one page, one above the other.
But it appears that you have common values on both pages that you want to combine/sum together.

This is really a database type scenario (would work better in Acces), but I am thinking Power Pivot or Power Pivot may be the best Excel options (though I don't use them much, as I don't do database work in Excel). There may be a way to do it with some of the newer advanced Excel functions, but that is a little bit beyond my knowledge.

What happens if you turn on the Macro Recorder, and record yourself doing the manual "Data consolidate" method?
Many times, this will record and create VBA code that you may be able to use, with a little tweaking.
Try doing that, and see if it records any code, and if it does, please post it here.

Holy Smokes .... never used the Macro Recorder before and had no idea it could provide a possible solution (thanks for the idea)

Had to do some "tweaking" but got it to do what I wanted ... I Added the macro to a previous series of macros for the result I needed

ThisWorkbook.Worksheets("CombineActions").Activate
Worksheets("CombineActions").Range("A2:T3000").ClearContents
    ThisWorkbook.Worksheets("CombineActions").Range("A1").Select
    Selection.Consolidate Sources:=Array( _
        "'C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp-#22511335-v1-TEST_-_TAB_Copy_Classification_Database.XLSM]BulkDataActions'!R1C1:R3000C20" _
        , _
        "'C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\-#22511335-v1-TEST_-_TAB_Copy_Classification_Database.XLSM]SingleDataActions'!R1C1:R3000C20" _
        ), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    ActiveWindow.SmallScroll Down:=6
ThisWorkbook.Worksheets("HomePage").Activate

thank you

 

[Joe4]

You are welcome! Glad it worked for you!

Yes, the Macro Recorder is a great tool to get snippets of VBA code like that - you don't have to know the VBA code, it creates it for you!
It usually just requires a little clean-up on the back-end to make it more dynamic, as the Macro Recoder is very literal, and records every select, scroll, etc, and hard-codes in ranges.