Macro to add next week of dates to spreadsheet

[LOW]

The following module is meant to generate the next week (day of week in one column; actual date in the next). When I execute it, the days of the week populate the cells correctly but the date is incorrect. Rather than the next week following the week listed above, it's the week prior. Any ideas what I'm doing wrong?

Sub getDates()
'
' getDates Macro
' Keyboard Shortcut: Ctrl+r
'
ActiveCell.Offset(-8, 0).Range("A1:B7").Select
Selection.Copy
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Mon "
ActiveCell.Select
End Sub

 

[Gavona]

The code you posted works in that it copies a block of cells.
What formula is in the date cells that you copy? Please post an example. I would guess you are deducting 7 from the values in the previous week rather than adding seven.


Gavin

 

[Gavona]

Your code does make unnecessarily use of Select. This won't resolve your issue but it would be in cleaner code:

Sub test()
ActiveCell.Offset(-8, 0).Range("A1:B7").Copy
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Mon " 'not sure why you need this
End Sub


Gavin

 

[xlbo]

For future ref, please post VBA questions in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question