VBA Incremental Column selection (weekly updated dashboard)

[SebastiaanK]

I've managed to create an easy to update dashboard in Excel using VBA. It's pretty solid, but the big cheese requires I make weekly updated trend line dashboard to add to this data (single snapshot dashboard).

What I'd like is a macro that does the following:

Copy data from range X (e.g. Data!A1:A5) to Range Y in different sheet (e.g. Trend!B1:B5)
And then when I run step 1 and 2 again


Copy NEW data from range X (e.g. Data!A1:A5) to Range Y+1 in different sheet (e.g. Trend!C1:C5)

repeat ad infinitum (each time moving one column to the right)

What I get is that i need to define the column line as an integer and then refer to it. (e.g.

Please Login or Register  to view this content.

However repeating this macro does not increment i for me.


Pretty much what I need to fix is that the line

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

Permanently, and repeat the next time for i = 4, i = 5 etc.

After that for me it is easy to create a line chart from this range

Thank you.

edit: I had a brainfart about what might work, but no idea if it can.

Would it be possible to do an If statement on cell Trend(1,i) to see if it is empty. If true > paste. If false then do i + 1 and repeat.