VBA Copying and Pasting Loop with If Then

Hi forum, I am really new to VBA for Excel.

I wanted to create a macro far beyond my skill and hit a brick wall.

I really wish I can attach a sample Excel file - so you can see where I got stuck, but here's what I got;

There's a list (Sheet 1) of stores with grades (A/B/C) and zone types (H/C/T) - [Name of stores] in column A, [grades] in column B and [zone type] in column C.

And there's Pack list (Sheet 2) - [zone types] in column A, [PO#] in column B and [grades] in row 1 and [number of packs] in row 2.

Basically for each PO#, there's different [number of pack] for each [grade]


I hope this wasn't too confusing.

What I need macro to do is;

For each PO# I need to create a new file and copy and paste the [name of stores] to column A and repeat that for [number of packs] for [store grade]

For example if PO#1234 has instruction of 3 packs for A grade stores / 1 pack for B grade stores / 0 pack for C grade stores;

I need to copy the name of A grade stores 3 times then name of B grade stores 1 time in column A and save the file as PO#

[zone type] is just another layer to store grade, some PO# exclude stores in certain [zone type].


I've tried to this a piece by piece;

I can make it create a new file for each PO#s and save them as PO# in a designated folder,

I can make macro to loop IF THEN to test store grade and repeat copy and paste by [number of packs] amount, but got stuck at the point when it goes over to next grade - they just copy&paste over the A grade list not under them....



I'm sorry if there are already similar questions on the forum, but I couldn't find anything similar enough to my problems and I'm certainly not good enough to gather different answers and modify them to solve my own issue.


Many thanks in advance!

Cheers,