repeat data on (odd/even) row

hi.

i need to create a macro function to run a repeat cell data that pulling from a sheet in the same excel file.
attached sample

basically what i need to do is:

write a macro:

insert a new sheet and name it as "upload"

on the "upload" sheet:

A1 to be "ASN Header"
A2 to be "ASN Shipment"
A3 to be "ASN order"
B1 - default and always to be as " 056481716"
B2, C1 & C2 - I will manually input the data
D1 - default and always to be as "Zoe"

B3 = ASN!A2

here comes the tricky part from below:

on Column B (on every even cell) from:
A4= shown as "ASN Item"
B4 = ASN!G2 = as: 1038, but it needed to add one 0 in the front so it appears as 01038 in this sample file

same on Column B (but, on every odd cell) from:
A5 = shown as "ASN CASE CONTAINER"
B5 = ASN!I2 = as: 12 in this sample file

on Column C (on every odd cell) from:
C5 = ASN!J2 = as: 104004437 in this sample file

under this formula method, repeat down until it reach Total

so, the theory is kind of like repeat alternately in even row/ odd row:

on column A, every even row (starting from A4) = A4 shown as: "ASN Item", repeat A6 "ASN Item", repeat A8 "ASN Item".... continue and stop until it reaches Total shown on Column A

on column A, every odd row (starting from A5) = A5 shown as: "ASN CASE Container", repeat A7 "ASN CASE Container", repeat A9 "ASN Item".... continue and stop until it reaches Total shown on Column A

in this sample file, Total is on cell: A1379

moving to Column B

on column B, every even row (starting from B4) B4= ASN!G2, repeat B6 = ASN!G3, repeat B8 = ASN!G4 ... continue until and stop until it reaches Total on Col.A
on column B, every odd row (starting from B5) B5= ASN!I2, repeat B7 = ASN!I3, repeat B9 = ASN!I4 ... continue until and stop until it reaches Total on Col.A
on column C, every odd row (starting from C5) C5= ASN!J2, repeat C7 = ASN!J3, repeat C9 = ASN!J4... continue and stoop until it reaches Total on Co.lA

stop until it reach Total (in this sample is A1379)

I hope it makes sense, let me know if anything is confused.

any help is appreciated !

Maybe this will work for you

Please Login or Register  to view this content.

Is there a sheet named ASN upload in your workbook?
If not change at the top of code

Please Login or Register  to view this content.

See attatched file. Click button to run macro. Then look at sheet ASN upload

sample file.xlsm

Give this a try

Please Login or Register  to view this content.