Merging multiple sheets onto one master sheet

I have a spreadsheet that contains approx 40 sheets, with Columns A to L.
Each sheet contains the same header row, but from 1 to 17,000 rows.

I need to combine all 40 sheets to a new sheet, preferably without including the header row (ROW 1) from each sheet apart from the first sheet.

I have tried some code I found online, but this only merged 99 rows for each sheet, and when I played with the numbers on the Macro this jug gave me an error.

the number of rows will vary week to week and possibly the number of worksheets. Therefore I would prefer not to need to define an exact number of rows, but perhaps an upper limit would be ok.

Any ideas guys?

much apprecuiate any assistance,
Dave

Create a new sheet and Name it MAIN.Copy the header to it.
Run the macro below.

Please Login or Register  to view this content.

hi guys,

many thanks for the prompt assistance - jraj106 I could not upload a sample due to customer confidentiality.
Thanks Trebor76 I tried your macro and it looks like it has worked - 63,000 rows which is roughly what I would expect. Will validate it but all looks good, and also ran very quickly which was a nice surprise.

Thanks also to kvsrinivasamurthy for your assistance.

Dave

Data validated, spot-on - worked well with powerpivot to provide a perfect customer solution. Thanks again.

Hi All,

This is more or less what i am looking to do. I need to be able to create a master sheet which can copy rows from all other sheets on the same file. and with some means it either automatically does it every time the file is saved or if i have to run a macro or push a command button. it all works.

But since this is only my first time ever attempting to do this. i am not sure if i am even using the codes mentioned above correctly.

can someone please help me create this one file.

Thanks in advance