repeat macro for date change

dawnfoss64

New Member
Joined
Jan 17, 2014
Messages
5
I am new to macros and am only able to create them using the recorder.

I have a macro that changes the date in column after every 12th row. So for instance, it will repeat 1/1/14 twelve times. I then need to go to the next cell to run the macro to have it repeat 1/2/14 twelve times.l (Row 13) and then run the macro again to to have it change it to the next date for 12 rows.

How can i get it to repeat 365 times? (End result will be having every day of the year repeat 12 times.

This is what i have been using (from the recorder) but this will require me to manually run the macro for every day of the year

Sub datechange()
'
' datechange Macro
' Macro recorded 2/10/2008 by Dfossum
'
' Keyboard Shortcut: Ctrl+d
'
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Selection.copy
ActiveCell.Offset(1, 0).Range("A1:A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Option Explicit
Sub macro1()

Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A2").Select
Selection.Copy
Range("A2:A12").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy;@"

End Sub

or if you want it to go for 365, then use this

Option Explicit
Sub macro1()

Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A2").Select
Selection.Copy
Range("A2:A365").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy;@"

End Sub
 
Last edited:
Upvote 0
Let me know if it works :D, also if you do not want all of your data selected after the macro runs, just add [A1].Select at the end of the code.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top