VBA/Macro to Combine worksheets in a workbook into 1 sheet

[Neavalmi]

Hello Excel Experts:

I have search this forum before i made a new post since none of the threads is exactly working for my problem.

I have an excel file which contains costed bills of materials (BOM) of products, each worksheet is named by its SKU/model number.

My data has consistent/fixed columns from A to R
Data starts from row 41 (with headers) , 42 without headers. Some data are within rows 1 to 40 but these should be ignored.
Row numbers vary per worksheet

Appreciate your help to copy and paste/ combine all BOMs data into a separate spreadsheet in the same worksbook.
The headers for the second and succeeding worksheets need not to be copied.

also, the worksheet name must be copied in the last column "S" so i can know where is the data is coming from which worksheet.

The format of each worksheet should be carry over , not paste values. (rows are color coded by the levels in the BOm)

Thank you very much for your help guys.

Regards,
Nea

 

[Sixthsense]

Check this link

https://msdn.microsoft.com/en-us/library/cc793964(v=office.12).aspx

 

[Neavalmi]

Check this link

https://msdn.microsoft.com/en-us/library/cc793964(v=office.12).aspx

Thanks Sixthsense for the link.
I am actually a novice in macro and i only know copy paste and run it.

I tried to copy the function in the link you provided but when i run it, it pops up to look for the macro name.