VBA - Appending weekly worksheet data to master sheet

Hi there,

I've searched fruitlessly for a week for a solution to the below:

I've a workbook with two sheets 1)'weekly' and 2)'summary.'

Weekly is divided into products (up to a maximum of 8) with a fixed number of columns (4) directly below but a varying number of rows beneath as follows:

Product A (main header)
Issue|Serial Number|Analysis|Comments
data|data|data|data
data|data|data|data
data|data|data|data
data|data|data|data

--couple of blank rows--

Product B
Issue|Serial Number|Analysis|Comments
data|data|data|data
data|data|data|data
data|data|data|data

--couple of blank rows--

Product C
Issue|Serial Number|Analysis|Comments
data|data|data|data
data|data|data|data
data|data|data|data
data|data|data|data
data|data|data|data
data|data|data|data

..etc...

This weekly data must be appended into each product's respective grouping within the 'Summary' tab. With the following small additions: 2 additional columns filled with the model and current week must be inserted beforehand:

Product C
Model|week|Issue|Serial Number|Analysis|Comments
Product C|7|data|data|data|data
Product C|7|data|data|data|data
Product C|7|data|data|data|data
Product C|7|data|data|data|data
Product C|7|data|data|data|data
Product C|7|data|data|data|data

Summary page will contain the groupings of Product A through H. The weekly may only contain 1 (say Product D) in any given week, or all 8.

I realize there are more efficient ways of doing this, i.e. pivot tables, creating discrete tabs for each product, etc... but I've been tasked by the higher-ups to generate the above as I've laid out. Oh and I'm assuming this will involve VBA, as the title suggests, of which I possess some knowledge.

If anyone can point me in the right direction (perhaps something like this has already been resolved) I'd be most appreciative.

Thanks.

Beautiful. Thank you very much Pierre.

Hi there - I've just come across an issue that would seemingly involve a simple fix yet this is not the case. In the weekly tab if there is a blank row between 'Product A' and the four headers like so (and this formatting would extend to all the Products):

Product A

Issue Serial no. Analysis Comments
Broken 123A456 test 3 qwerty
Broken2 123A4556 test 8 qwerty
Broken3 123A456 test 3 qwerty
Broken4 123A4556 test 8 qwerty


I assumed that to capture the contents below one would just adjust the Offset(2) to Offset(3) in line ten of the above code. For whatever reason this is not working - what results instead is the copying and pasting of the first 2 rows (the header + the first row of data). What should I adjust to capture just the data?

Further to this - if the last listed product in the weekly tab contains no data beneath it, the macro inserts a blank line into the summary tab. Is there a way to avoid this?

Thank you.

Thank you for your time, especially given the vagueness of my requirements. I am still encountering issues. I've attached a file with a before and after example if you'd care to take a look. I think one of the sticking points is the fact that I am required to employ merged rows within the headings. (Personally, I'd just eliminate them - but at this point I must attempt to mirror what you see in the file unfortunately).

Thanks again.

You've cracked it. I can't thank you enough.