Using dynamic column numbers in VBA

[Robert E Lee]

Hi

I have a monthly spreadsheet which has a formula in each column on Row 2. Each month I wish to copy this formula to the next column. I am trying to use this code

Range(Cells(2, H)).Copy Destination:=Range(Cells(2, H2), Cells(R, H2))

where H, H2 & R are dynamic numbers. This fails for some reason which I cannot see. Can someone help

Many thanks

Rob

 

[Andrew Poulsom]

Fails with what error? What are the values of H, H2 and R when it fails?

 

[Dryver14]

just to be clear if in month 1 the amount was in column a and the formula in column e
the next month should this be column a and column f or column b and column f?

 

[Robert E Lee]

Error message "Method 'Range' of 'object_Global' failed.

The values of H, H2 and R are 61, 62, and 1860 respectively

Rob

 

[Robert E Lee]

The formula is in column A in Month 1 and Column B in Month 2, nott quite sure which "amount" you are referring to

 

[Robert E Lee]

Problem solved; it turns out that I did not need the first 'Range' in the line of code

Thanks for your interest

Rob