Copying a series of dates to specific cells

selitac

New Member
Joined
Feb 11, 2005
Messages
5
This is probably quite simple, but I would appreciate some help...

I am trying to produce a series of weekdates in the following format:
31-Jan
31-Jan
31-Jan
31-Jan
01-Feb
01-Feb
01-Feb
01-Feb
02-Feb
02-Feb
02-Feb
02-Feb

So I need weekdates only, and I need each date in 4 consecutive cells. Is there a quick way of doing this without having to copy and paste each date? I have tried using the blanks function, but in doing this, I still had to manually add each new date to each cell as required.

I would most appreciate some ideas.
Thanks
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
If it was just 1 day asimple formula would do, but you need a macro :-
Code:
Sub test()
    Dim ws As Worksheet
    Dim MyRow As Long
    Dim ThisDate As Date
    '----------------------------------------------
    Set ws = ActiveSheet
    '- 2 extra copies of first cell
    ws.Range("A2:A3").Value = ws.Range("A1").Value
    MyRow = 4
    '- loop 25 times (change for any other number)
    For d = 1 To 25
        ThisDate = ws.Cells(MyRow - 1, 1).Value + 1
        '- If Saturday add 2 days
        If WeekDay(ThisDate, vbMonday) = 6 Then
            ThisDate = ThisDate + 2
        End If
        '- repeat 3 times
        For n = 1 To 3
            ws.Cells(MyRow, 1).Value = ThisDate
            MyRow = MyRow + 1
        Next n
     Next
End Sub
'-- eop --------------------------------------------
 

selitac

New Member
Joined
Feb 11, 2005
Messages
5
Thank you very much BrianB, this works great and is very useful for what I am trying to do. Thanks again!
Selitac
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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