Varying Length Raw Data Import - Find Max Week + 1

Im having a small issue with trying to come up with a solution for some code im trying to run.

I have attached a file trying to show what i am trying to achieve.

In summary I have 3 different raw data inputs (Worksheets - Raw Data Business 1:3) that are being read into their respective columns in the worksheet '2015 - Product'. The only rows that i want to copy in the raw data are highlighted 'green' - so no headings nor total row, the 'yellow' column labelled 'week' in the '2015 - Product' i want to add this based on the length of the product.
eg. for week 31 business 1 has 7 products so i want to repeat the week 31 x7 times, business 2 has 7 products for week 31 again 7 times.

Where the issue lies is some weeks the product count will vary, so week 32 business 1 may have only 5 products, business 2 has 8 products etc.
So I just need to add this to my code to accommodate for this.

Secondly it is the week input, im finding it difficult to work this out (i have a formula in the worksheet to accommodate for it provided it is a fixed length), however id like to incorporate this into my code too, im trying to find the max of the range and then it is +1 for the week, however im struggling to find suitable logic.

Any help would be greatly appreciated!!