Dragging formula across using a macro?

[LemonyMidget]

Hello,

So I need my formulas to be dragged across to the next column using a macro but I'm finding it really hard.

I've got a few tabs I need to do this on as well,
E.g On the 'ACE' tab I need from AS46:AS86 to be dragged across one column to AT46:AT86, but then the next time I need it to go from AT - AU etc etc, I'm having a lot of trouble working out the basics of how to do this.

Any help would be great, thanks

 

[steve the fish]

How do you intend to tell the macro which column is the correct column to use?

 

[LemonyMidget]

I have no idea, that is part of the problem haha

 

[steve the fish]

No how do you identify it's the correct column? How do you know you need to move from AT-AU? Is it the last used column for example? Is AT46 the last used cell in row 46??

 

[LemonyMidget]

Ooooh haha, I'm sorry! Yes, so AS46:AS86 currently have formulas in them/data in them, AT is blank, so next week I need the formula to be dragged across to AT and fill the data in, and then the week after that AT will be the last column to have data in so those formulas need to be dragged to AU as that one will be the next blank column. I apologise if I'm not being very clear, I'm awful at putting what I need into words.

 

[steve the fish]

See if this works for you:

lc = Cells(46, Columns.Count).End(xlToLeft).Column
Range(Cells(46, lc), Cells(86, lc)).Copy Range(Cells(46, lc + 1), Cells(86, lc + 1))