Make a work sheet template?

[Ralph2]

Hello
Can any one advise on how to make a work sheet template. I wish to make weekly worksheets (i.e. 52) with each one showing a seven day stretch with the correct date.
My current solution is to "copy" the last week, open a new worksheet and paste... then convert all the wrong days to the right ones and re name the worksheet (week 1, week 2.. etc).
I was thinking "a" solution might be a macro... but perhaps there is some built in wizzards or other means??
Thank for any advice
Ralph

 

[IFRs]

For the stating date, you can create another spreadsheet that contains only the desired start date. Make a macro or button in your working spreadsheet that reads the start date, changes the start date, saves the start date spreadsheet and then copies the start date to your working spreadsheet. Formulas in other cells can increment the day of the week. We do this for purchase order number incrementing.

The macro looks like this:

Private Sub NextPO_Click()
Dim PONumberFile As String
PONumberFile = "\\ATI\D-Drive\Purchase Orders\Last PO Number.xls"
Workbooks.Open Filename:=PONumberFile, UpdateLinks:=0, Editable:=False
ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + 1
PO = ActiveSheet.Range("A1").Value
ActiveWindow.Close SaveChanges = YES
Worksheets("Purchase Order Template").Range("F2").Value = PO
ActiveWorkbook.SaveAs Filename:="\\ATI\D-Drive\Purchase Orders\PO " & PO & " " & Worksheets("Purchase Order Template").Range("C3").Value & ".xls"
End Sub

 

[Ralph2]

Thanks.. I will try your approach and see if I can make it work for us.

 

[johnwm]

To save a template use File|SaveAs and select .XLT as your filetype from the dropdown. When you next open Excel and do a File|Open you will find the template in General Templates.

To do a weekly spreadsheet use a Calendar control with its Linked Cell set to an off-screen cell (example X1).

Set X2 to '=WEEKDAY(X1,2)-1'
Set the cell you want (say A1) for the Monday to '=X1 - X2'
This gets the Monday of the selected week.
For Tuesday (say A2) set to '=A1 + 1' and so on.

I usually add a command button with caption set to "Set Date" with this code:

Private Sub CommandButton1_Click()
Calendar1.Visible = True
End Sub

and put this in the Calendar1 code:

Private Sub Calendar1_Click()
Calendar1.Visible = False
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk