VBA code: replicate article information based on year+month combination

[etski]

Hi, I am new to the forum and have just begun to learn VBA. I have a task that I would like to automate but I do not even know where to start. I believe there must be an easy way to solve this but I am not that advanced yet so I guess I am making this more complicated than it actually is.

I would be very thankful for every advice!


CURRENT SITUATION:
I have placed the following scenario on Sheet1:

• a table with client number, article number, product category and amount. Below an example "input table" in columns C-F (in reality there are more rows)
• there are 5 different product categories and an article can only belong to one category
• cell A2 shows the current month in YYYYMM format (assumed now is Feb 2018)

Sheet1:

I need to convert this table on Sheet1 to another format. I have placed the required format on Sheet2 and it looks like this:



REQUIRED FORMAT

Sheet2:

Columns Client & Amount
1) The information can directly be taken from the input table on Sheet1 (Client, Amount)

Columns Category 1 - Category 5
2) The columns category 1 to category 5 correspond to the column "Product Category" in the input table on Sheet1 --> idea is to place the article code to the category column where it belongs, according to the information that we have in column E on Sheet 1. E.g. article QWERT belongs to Category 2 so article code QWERT will be placed in column F (=Category 2) on Sheet 2.

Columns Business Unit and Key Customer
3) Column Business Unit is always filled with the value "XYZ" and column Key Customer remains always blank

Column Month
4) The column Month needs to be in format YYYYMM. I need to consider which month it currently is and create rows for the current & preceding months of the current year.

• E.g. if now is February 2018, I need to have a row for January 2018 and February 2018, for each of the article lines that I have in the "input table" on Sheet1. These rows are otherwise identical - the only difference is the year+month combination.
• The trick is to replicate each article information line on Sheet1 as many times as the current month is. If it were December 2018, I would have twelve lines (one for each month of 2018) for one article information line on Sheet1.

EXAMPLE:
Assumed now is February 2018 (corresponds to value in cell A2 on Sheet1)
Below is how my "output" table on Sheet2 should look like:

Does anybody have an idea how I could easily solve this? I find it especially difficult to formulate a macro that replicates the article information based on the year+month combination... and to make it flexible so that it does not matter whether it is 2022 or 2018.

Many thanks in advance!!

Kind regards,
etski

 

[etski]

Hi Cal, that works except that I need to have the article code in the product category columns instead of the category.. (It is quite stupid but this is a template requirement)

Kind regards, etski

 

[FatBoyClam]

Sorry, correct this line:

ShtDest.Cells(ResRw, ColNum).Value = ShtSrc.Range("E" & Rw).Value

to this:

ShtDest.Cells(ResRw, ColNum).Value = ShtSrc.Range("D" & Rw).Value

 

[etski]

Thanks, that works like magic! I really appreciate the help!