Make a work sheet template?
Make a work sheet template?
(OP)
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
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





RE: Make a work sheet template?
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
RE: Make a work sheet template?
RE: Make a work sheet template?
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:
CODE
Calendar1.Visible = True
End Sub
and put this in the Calendar1 code:
CODE
Calendar1.Visible = False
End Sub
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk