Consolidate selected worksheets into new one and format as table

[Mad-Dog]

Good morning VBA'ers,

I have an excel workbook where there are 9 worksheets of which 6 contain data in Columns A:CP and should always remain the same; however, the Row depths will vary depending on each.

What I would like to do at a click of a button is to consolidate the 6 RED worksheets into ONE worksheet called "Consolidate" (ignoring the other worksheets) but ensure that any blank cells in Column A of the wroksheets do not prevent the macro from running....so my newbie logic says to me that perhaps first read the columns the rows but I'm guessing you will know best. Also would be great to be able to amend the code to expand the column selection should the need arise in the future.

Following that it would be great if the macro would then format the consolidation tab data into a table of sorts whereby the top line is Frozen, a filter added and some table format (colour) of any description is added.

I have made an attempt (macro) which works pretty well but it grabs data from every tab and I'm not knowledgeable enough to tweak this "borrowed" code

Thanking everyone in advance.

Please Login or Register  to view this content.

[HaHoBe]

Hi, Mad-Dog,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[Mad-Dog]

Thank you for pointing this out...knew I had to do something to that effect hence the [].

[HaHoBe]

Hi, Mad-Dog,

from what I suggest I guess you may try (please adapt the names of the worksheets to suit, I assumed all to be consildated start with RED):

Please Login or Register  to view this content.

Again this is what I figure out without seeing the data in the workbook, there may be a better way to do what you would like to get.

Ciao,
Holger

[Mad-Dog]

Hi Holger,

Sorry should have been clearer.....The RED tabs are each named according to a team member so Sam, Chris, Emraz, Katy, Sophie, Monica, Kathrin so how would I amend the code to take this into consideration?

Much appreciated for the speedy response

[HaHoBe]

Hi, Mad-Dog,

of which 6 contain data

Sam, Chris, Emraz, Katy, Sophie, Monica, Kathrin

make up a count of 7 to me

Please Login or Register  to view this content.

The constant isnīt as dynamic as you may want it to be - I would need if the headings for the data wioll always be in the same row in order to check out the last used column via

Please Login or Register  to view this content.

Ciao,
Holger

[Mad-Dog]

Holger,

Almost there. Can we make it so that if the macro is run again it simply overwrites the current data "Consolidate" tab. This will negate the need to delete the tab first prior to running it again.

Once thats done I can consider this Solved :-)

[HaHoBe]

Hi, Mad-Dog,

Please Login or Register  to view this content.

This line of code should delete all contents without the headers from the summary sheet - Iīm afraid I canīt get what you are askin gof rwith

Can we make it so that if the macro is run again it simply overwrites the current data "Consolidate" tab.

Due to maybe different amounts of data I would recommend to clear before simply overwriting and maybe leave older records which arenīt actual any more.

Can you please explain to me what you expect the code to do - I may be able to put that into a code-line for work?

Ciao,
Holger

[pnperl]

I use this simple tool http://www.rondebruin.nl/merge.htm

[Mad-Dog]

OK will try explain as best as I can.

So each of the tabs with names will be added to on a daily basis some more than others. At various points during the day I simply want to run the macro and whatever was on the Consolidate tab I want deleted and overwritten with an update of all the new data that exists in the six tabs the macro is captuirng from.

When I run the current code it is pushing the existing data in the Consolidate tab down (i.e. inserting rows) by the same amount of rows of the existing data but these new rows are blank with no data.


One other solution maybe to detect if there is a a tab called Consolidate, if so delete it, and add a new tab called Consolidate and capture the data from the 6 tabs.

I hope that makes sense.

[HaHoBe]

Hi, Mad-Dog,

I forgot about the Table and worked for a normal range instead.

Please try:

Please Login or Register  to view this content.

Ciao,
Holger

[Mad-Dog]

Hi Holger,

Worked a charm. Many thanks for your efforts.