Combine multiple sheets into one sheet in the same workbook

Please use code tags when posting too the Forum (per Forum Rule #3). Maybe:

Please Login or Register  to view this content.

Hi,

It still took the data from - Control (sheet 1), Summary (sheet 42), Sizes - F (sheet 40), Sizes - M (sheet 41)

Please see attached sheet, On this sheet there is only 3 (ward 1,2,3) sheets, but keep in mind on the original one I have 38 (sheet 2 to 39)

Thank you, the only problem is that it is copying the header each time.

How do you copy the header only once?

Try:

Please Login or Register  to view this content.

Its only got the 1 heading, but the table (with the colours like your previous code) are not showing and it is not counting all the data

I have 499 students on the separate sheets, but when it is combined, there is only 486

Is it maybe also possible to copy the same format (colours, font, rows and coulombs) to this "combined" sheet, exactly as what the others look like?

Hi
I asked a similar question a few months ago and was given the following code:-

Please Login or Register  to view this content.

This presupposes that there is one common word on each sheet, in my case it was Title in cell C4, which must always be in the same cell, and then the code will copy from that point down. It will ignore all sheets that do not have that word in the cell. It also presupposes that the data is always copied into Sheet1. It worked brilliantly for me (thanks to the lovely person who supplied it!).

Can't help with the formatting, except if you format as a table and copy and paste into that it should copy the formatting down.

Hope that helps.

Hi, Thanks, JOHN H. DAVIS, it is starting to look better for me.

Just a few issues:

The colours of the table are not showing, as per all other sheets

In line 184 it uses Calibri font, instead of Century gothic

The id numbers are showing some weird format

Will the code change if I copy one of the sheets for the combined sheet and leave it blank and then just take the data from the rest of the sheets?

Untitled.png

Hi, ok, so I managed to sort out the font, coulomb sizes, numbers to show correctly. Using the below code.

The only thing I am struggling with is the colours of the table

Please Login or Register  to view this content.

Yes, I figured it out

Please Login or Register  to view this content.

No worries, I figured it out

I added Sheets("Combined").ListObjects.Add(xlSrcRange, Range("B3:Y2500"), , xlYes).Name = "StudentList3456789101112131415161718192021222324252627283031"