Populate a template sheet with data

Hello everyone,

I'll start with the attachment since it's always easier:

Attachment 327508

This file is far from finished so there is some useless stuff there. What I'm interested in is in the sheet RecapTable (and in Etiq1 to Etiq4).

I'd like to write a macro that'll populate my Etiq1 -> 4 according to the RecapTable. Data would be set up as in Etiq1 A1:A3.
I don't have to have 4 template sheets and it could change if it's easier to do something else. The goal is to print labels so I maybe only 1 Etiq is needed, and the macro could populate, print, erase, finish populating, print again



Here is the logic that I think would work here. If someone could help me translate that into VBA code it'd be amazing
I'll ignore the exact set up I presented and will assume that 1 cell corresponds to 1 cell in my output.

1. Count entries in RecapTable (found something that does that! yeey!)
n = Worksheets("RecapTable").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count

((2. Divide that by 65 (which is the number of labels per sheet) and round at upper number. For example: if n=100, 100/65 = 1.54 so 2 sheets needed)) [maybe a useless thing to do]

[Assuming I only populate 2 cells]

If v is the row# in RecapTable,
If w is the column# in RecapTable,
If x is the row# in Etiq,
If y is the column# in Etiq,

v=1, w=1, x=1

for v=1 to n or to 65, [taking care of the 1st output sheet: we take the 65 first entries in RecapTable]
-y=1 [reset column# in output]
-for y=1 to 9 [Populating one row of labels at a time in output]
--cell(v,w) = cell(x+1,y)
--cell(v,w+1) = cell(x+2,y) [populating my cells]
--y=y+2 [moving 2 columns to the right to skip the separator column]
--v=v+1 [moving to the next entry in RecapTable]
-end [of looping through horizontal labels]
-x=x+3 [moving to the next row of labels]
end [of 1st sheet of ready-to-print labels]


Aaaand that would already be great! I think if I can see the code this would give, I'd be able to go a little bit further on my own.
Thanks for the help don't hesitate to comment and tell me if there's a better/simpler way!

Simon

After stumbling upon some code that looked like a syntax I could reuse, I gave it a go.

Got a working macro (!!!), which isn't the best obviously but which seem to get the job done.. kind of :p

I still don't have support for more than 65 entries, and I still don't have the perfect format for my cells but it's something!

Feel free to comment

Please Login or Register  to view this content.