Macro help

Dartagnan

Active Member
Joined
Jun 19, 2002
Messages
386
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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

New Member
Joined
Feb 25, 2002
Messages
28
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

Well-known Member
Joined
Apr 29, 2002
Messages
636
Xltoend can be buggy. I would suggest counting the number of items in an array such as this:

worksheetfunction.counta(yourrange)
 

Dartagnan

Active Member
Joined
Jun 19, 2002
Messages
386
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
 

Forum statistics

Threads
1,144,311
Messages
5,723,645
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top