Macro help

[Dartagnan]

On a daily basis I have to copy formulas over to new cells. For example the formula is currently in cells R58 and R59. Today I want to copy the formula to S58 and S59. Tomorrow I will need to copy the formula to T58 and T59. The copying goes on throughout the month. So the macro will need to move to the next blank cell or go by date. The macro I created keeps repeating the same action over and over in the same cells.

Here is the code to the current macro

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/25/2002 by cniva
'
' Keyboard Shortcut: Ctrl+d
'
Range("R50:R51").Select
Selection.AutoFill Destination:=Range("R50:S51"), Type:=xlFillDefault
Range("R50:S51").Select
Range("R58:R59").Select
Selection.AutoFill Destination:=Range("R58:S59"), Type:=xlFillDefault
Range("R58:S59").Select
End Sub

 

[Barrie Davidson]

On a daily basis I have to copy formulas over to new cells. For example the formula is currently in cells R58 and R59. Today I want to copy the formula to S58 and S59. Tomorrow I will need to copy the formula to T58 and T59. The copying goes on throughout the month. So the macro will need to move to the next blank cell or go by date. The macro I created keeps repeating the same action over and over in the same cells.

Try this code:<PRE>
Sub Macro2()
'
' Written by Barrie Davidson
'
Range("R58:S59").Copy _
Destination:=Range("IV58").End(xlToLeft).Offset(0, 1)
End Sub</PRE>


PS - test it first!
Regards,

_________________

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-10-25 16:37

 

[rlarson]

I assume you have data in the cells preceding R58 and R59?

If so, this will look in rows 58 and 59, find the last "non-empty" cell, and then copy, paste into the adjacent cell to the right.

It's not pretty, but it works.

' Macro1 Macro
' Macro recorded 10/25/2002 by
'

'
Range("a58").End(xlToRight).Select
Selection.copy
ActiveCell.Offset(0, 1).Select
ActiveCell.PasteSpecial

Range("a59").End(xlToRight).Select
Selection.copy
ActiveCell.Offset(0, 1).Select
ActiveCell.PasteSpecial
End Sub

 

[zacemmel]

Xltoend can be buggy. I would suggest counting the number of items in an array such as this:

worksheetfunction.counta(yourrange)

 

[Dartagnan]

Thank you rlarson. The macro is working fine. Can you add code to do a copy and paste special?

The data is located in (c65:g65). I want to copy the data into cell U7 (tomorrow I will copy into cell V7). The paste should be paste special for values only and transpose.

This will also be repeated daily so the data will need to fill the empty cell to the right.

Thank you