Printing out different dates

[Jonathan D]

I have a timetable that needs to have dated columns but I don't want to
create 52 work sheets for each week and each year have to change them all.

Is there anyway to create a macro to print 52 pages with correct dates and
all starting from Monday to Sunday

Thanks

 

[joel]

A very simple macro can create a new workbook with 52 sheets and add dates
into to each sheet automatically. I need to know how your work weeks are set
up and what you want on each sheet. There are lots of variations in work
weeks. Start Saturday, Start Sunday, Start Monday? Does you year Start on
the 1st of January, or the first monday of the year. Are days from previous
year shown? wehat do you want the tabs to show on each sheet? Do you want
header rows on each sheet and what do they contain. Dou you want any headers
and footers put on each sheet? What do you want the new work book called.

This is all really very simple, but there are millions of solutions. the
macro will run in about 10 seconds and take about 10 minutes to write. It
will save your hours of time.

 

[Jonathan D]

It would start the first Monday of every year and tabs can just show the date
of that beginning week

The workbook can have any name really.

 

[joel]

Try this macro

Sub CreateNewYear()

NewYear = InputBox("Enter Year : ")

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName = False Then
MsgBox ("Cannot create new Workbook - Exiting Macro")
Exit Sub
End If

Jan1 = DateSerial(NewYear, 1, 1)
Jan1Day = Weekday(Jan1, firstdayofweek:=vbMonday)
FirstMon = Jan1 + (8 - Jan1Day) Mod 7
SheetDate = FirstMon

First = True
Do While Year(SheetDate) = Val(NewYear)
If First = True Then
'create new workbook with one sheet
'Use copy which creates a one sheet workbook
Sheets(1).Copy
Set Newbk = ActiveWorkbook
Set NewSht = Newbk.Sheets(1)
NewSht.Cells.Clear
First = False
Else
With Newbk
Set NewSht = .Sheets.Add(after:=.Sheets(.Sheets.Count))
End With
End If

NewSht.Name = Format(SheetDate, "MM_DD_YY")
SheetDate = SheetDate + 7
Loop
Newbk.SaveAs fileSaveName
End Sub