Hi,

We are updating the excel spreadsheet that we enter all the sales details in
at the end of the day. We currently have the system where we open the
worksheet template we've made and save as for that week ending and enter
monday's date in a cell on one tab titled monday. All other day's tabs until
sunday then simply fill themselves in (Obviously just a +1 format to the
previous day's tab. i was wondering if i can use Macros or something so that
when we open the template. a box pops up asking for the week ending date
(i.e. 13/12/09) which then fills in all the dates for that week to their
respective tabs?

And also can i set it so it only pops up that one time when we open the new
template to start that new week?

Phil,
This is what I came up with as a solution:
Make the sheets in the file as "Monday, Tuesday, Wednesday, Thursday,
Friday, Saturday, Sunday" and save it as a Template.
On sheet "Tuesday" in Cell A1 type this formula, "=Monday!A1 + 1".
On sheet "Wednesday" in Cell A1 type, "=Tuesday!a1 +1"
On sheet "Thursday" in Cell A1 type, "=Wednesday!A1+1".
On sheet "Friday" in Cell A1 type, "=Thursday!A1+1".
On sheet "Saturday" in Cell A1 type, "=Friday!A1+1".
On sheet "Sunday" in Cell A1 type, "=Saturday!A1+1".
Save As a Template.
Open the file, right click on the first tab, "Monday" and select "View Code".
In the left-hand pane, select "Sheet1 (Monday)" by double left-click.
Enter the folowing:
" Private Sub Workbook_Open()

Dim DateofWeek As String
Dim BeginDate As Date

BeginDate = InputBox("What is the date of Monday (mm/dd/yy)?")
DateofWeek = InputBox("What is the Date of Week (mm_dd_yyyy)?")


Application.Worksheets("Monday").Select
Range("A1").Select
Range("A1") = BeginDate


ActiveWorkbook.SaveAs Filename:= _
"C:\Sales Reports\Week of " & DateofWeek & ".xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

End Sub"
Without the first and last quotation marks ("").

This part "C:\Sales Reports\Week of ...." is where ever you want to save the
information. Your filepath will be different from mine.

Hope this helps and is what you meant.

"Phil" wrote:

Hi,

We are updating the excel spreadsheet that we enter all the sales details in
at the end of the day. We currently have the system where we open the
worksheet template we've made and save as for that week ending and enter
monday's date in a cell on one tab titled monday. All other day's tabs until
sunday then simply fill themselves in (Obviously just a +1 format to the
previous day's tab. i was wondering if i can use Macros or something so that
when we open the template. a box pops up asking for the week ending date
(i.e. 13/12/09) which then fills in all the dates for that week to their
respective tabs?

And also can i set it so it only pops up that one time when we open the new
template to start that new week?

is the "" at the beginning and the end of that the only things i need to
remove? what about the writing in the brackets etc?

"FloMM2" wrote:

Phil,
This is what I came up with as a solution:
Make the sheets in the file as "Monday, Tuesday, Wednesday, Thursday,
Friday, Saturday, Sunday" and save it as a Template.
On sheet "Tuesday" in Cell A1 type this formula, "=Monday!A1 + 1".
On sheet "Wednesday" in Cell A1 type, "=Tuesday!a1 +1"
On sheet "Thursday" in Cell A1 type, "=Wednesday!A1+1".
On sheet "Friday" in Cell A1 type, "=Thursday!A1+1".
On sheet "Saturday" in Cell A1 type, "=Friday!A1+1".
On sheet "Sunday" in Cell A1 type, "=Saturday!A1+1".
Save As a Template.
Open the file, right click on the first tab, "Monday" and select "View Code".
In the left-hand pane, select "Sheet1 (Monday)" by double left-click.
Enter the folowing:
" Private Sub Workbook_Open()

Dim DateofWeek As String
Dim BeginDate As Date

BeginDate = InputBox("What is the date of Monday (mm/dd/yy)?")
DateofWeek = InputBox("What is the Date of Week (mm_dd_yyyy)?")


Application.Worksheets("Monday").Select
Range("A1").Select
Range("A1") = BeginDate


ActiveWorkbook.SaveAs Filename:= _
"C:\Sales Reports\Week of " & DateofWeek & ".xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

End Sub"
Without the first and last quotation marks ("").

This part "C:\Sales Reports\Week of ...." is where ever you want to save the
information. Your filepath will be different from mine.

Hope this helps and is what you meant.

"Phil" wrote:

Hi,

We are updating the excel spreadsheet that we enter all the sales details in
at the end of the day. We currently have the system where we open the
worksheet template we've made and save as for that week ending and enter
monday's date in a cell on one tab titled monday. All other day's tabs until
sunday then simply fill themselves in (Obviously just a +1 format to the
previous day's tab. i was wondering if i can use Macros or something so that
when we open the template. a box pops up asking for the week ending date
(i.e. 13/12/09) which then fills in all the dates for that week to their
respective tabs?

And also can i set it so it only pops up that one time when we open the new
template to start that new week?