Simple 2007 Excel Macro Question

[bfraser]

I need to know how to write a simple macro for Excel 2007 that .....copies a series of dates from the top of a worksheet (see below) and inserts them into cells further down a worksheet at intervals of 35.


ColumnB
Row 65 Sunday, January 1, 2012
Row 66 Saturday, December 31, 2011
Row 67 Friday, December 30, 2011
Row 68 Thursday, December 29, 2011

Row 437 Sunday, January 1, 2012
Row 472 Saturday, December 31, 2011
Row 507 Friday, December 30, 2011
Row 542 Thursday, December 29, 2011

 

[shg]

I don't see how the row numbers in your example follow the description in the text.

 

[jim may]

Try this........(it's based on your input....)

Sub CopyStuff()
Dim Interval As Long, j As Long, i As Long
Interval = 35
j = 437
Range("A65:A68").Copy
For i = 1 To 4
Cells(j, 1).PasteSpecial xlPasteValues
j = j + Interval
Next i
End Sub

 

[bfraser]

Almost.........
The macro copied all of the four dates to each of the four locations, rather than copying the first date to the first location, the second date to the second location etc.

 

[jim may]

ahhhhh, to do that try:

Sub CopyStuff()
Dim Interval As Long, j As Long, i As Long
Application.ScreenUpdating = False
Interval = 35
j = 437
For i = 65 To 68   'Your row numbers of your copied base cells
Cells(i, 2).Copy
Cells(j, 2).PasteSpecial xlPasteAll
j = j + Interval
Next i
Application.CutCopyMode = False
Range("B65").Select
Application.ScreenUpdating = True
MsgBox "Cells copied down"
End Sub

 

[bfraser]

Unfortunately, it still doesn't work.
It brings up the MsgBox and the hour glass and nothing else happens,,,,,

 

[jim may]

It worked fine for me,, lotta good that is to you..

Comment out (place " ' " in front of the 3rd row application.screenupdating..."

and then STEP thru the code (Pressing the F8 function Key) watching S/S as it
progresses thru. If you get an error (r/t) - post it here!!

Jim

 

[bfraser]

Jim, are you able to send me your email address so I can forward you my worksheet with your suggested macro?