Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Make a work sheet template?

Make a work sheet template?

Make a work sheet template?

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

RE: Make a work sheet template?

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

RE: Make a work sheet template?

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

RE: Make a work sheet template?

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
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close