Macro - Copy cormulas & range based on value

[Kauerauf]

Hi guys,
im trying to create a macro that can copy formulas in one column (cells: A10:A20) and based on a value in another cell (Cell: A1) the macro should paste the formulas the same number of columns (cells B10:G20) as the value is in Cell: A1 .
Lets say the value is 4 (January to April), then the macro should copy range A10:A20 and paste it in range B10:E10.
if the value is 7 (January to July), then the macro should copy range A10:20 and paste it in range B10:H10. etc

I will be very grateful, if someone know the answer.

 

[SteveO59L]

"copy range A10:A20 and paste it in range B10:E10"

a10:a20 would be 11 cells, b10:e10 is only 4?

 

[FormR]

Hi, welcome to the forum!

If I've understood correctly then here is one option you can try:

Range("A10:A20").Copy Destination:=Range("B10:B20").Resize(, Range("A1").Value)

 

[Kauerauf]

Hi, welcome to the forum!

If I've understood correctly then here is one option you can try:

Range("A10:A20").Copy Destination:=Range("B10:B20").Resize(, Range("A1").Value)

It works fine, thank you so much FormR, is it also possible to have one color (white) for the area that was included (I tried with value = 4 in cell A1 -> included area = B10:E20) and another color (yellow) for the rest of the columns that was not included in this case (F10:G20)?

wbr // Kauerauf

 

[FormR]

is it also possible to have one color (white) for the area that was included (I tried with value = 4 in cell A1 -> included area = B10:E20)

Hi, the interior fill colour of the cell?

and another color (yellow) for the rest of the columns that was not included in this case (F10:G20)?

Is G then maximum column, i.e. is 6 the maximum value of A1?

 

[Kauerauf]

Hi, the interior fill colour of the cell?



Is G then maximum column, i.e. is 6 the maximum value of A1?

Hi again,
Sorry, I typed in a wrongly letter, G is not the last column.
The total area should be:
A10:A20 = copy area
B10:M20 = paste area (one column for each month).
You was right regarding the colours. My problem is now solved, so thank you again for the code!
wbr // Kauerauf

 

[FormR]

My problem is now solved, so thank you again for the code!

Great not sure if you figured out the colouring part by yourself, if not here is one option:

Dim m As Long
m = Range("A1").Value
Range("A10:A20").Copy Destination:=Range("B10:B20").Resize(, m)
Range("B10:B20").Resize(, m).Interior.Color = vbWhite
If m < 12 Then Range("B10:B20").Offset(, m).Resize(, 12 - m).Interior.Color = vbYellow