Before a macro can be written some questions need to be answered. My
comments are below.

1. The user needs to begin a new pay period which really could be 15 or 16
days depending on how many days in the month.

I assume that you pay periods are bi-monthly with the 1st one being 1 to 15
and the second starts with the 16th and ends either on the 28th, 29th, 30th,
or 31st.

2. The user clicks icon on the desktop, excel opens with a dialog with 2
choices: Edit Worksheet or Start New Worksheet.

This is best done with a Yes/No response using an Input box with the
question Do you want to start a new worksheet. this could automatically be
done based on the date. A check can be made to see if a sheet exists for all
dates up to the current date.

With two pay periods a month I would recommend having a seperate workbook
for each yeear. Have 24 worksheets in each workbook.

3. If user choice is = to 1, then open worksheet in edit mode (which brings
up another issue which is how to you manage each workbook that already
exists?)

You say you want a new worksheet but in 5 below you mention new workbook.
Are you creating newworkbooks or new wroksheets? What you can do is name
each worksheet a different name with the date included (or start and end
date). Place the worksheets in order of dates and Automatically open the
Latest worksheet. Let the user choose which worksheet they need.

If you want an edit mode this implies you want the workbook protected? How
do you want to manage when a workbook is protected. This response may vary
depending if you are talking about worksheets or workbooks.

4. If user choice is = 2, then show dialog with a calendar dropdown or
static calendar. User chooses a date to start the pay period (or draw
period), and user choosed an end date.

This isn't really needed. the macro can automatically detedrmine if a new
pay period is required based on the current date and the
workbooks/worksheets that already exists.

5. Display dialog to show " confirm dates ", user chooses a create button
and excel opens a new workbook, with the name of the dates (ex:
080108-081508.xls)

Again this isn't needed.

6. Worksheet displays the following columns: RouteNum, Carrier, Fri Aug-01,
Sat Aug-02, Sun Aug-03 and so forth, Daily Totals, Weekend Totals, Sunday
Totals, Grand Totals.

I recommend Putting the Date in Row 1 and the Day of the week in Row 2.
then list each carrier in Column A. I recommend setting up a templet with
all the carriers name. The Templet can either bee in the same workbook as a
sheet called Carriers or a seperate workbook with a sheet caleed Carriers.


"Rob" wrote:

Hello, this is my first day in this forum... Lots of sharp minds in here, and
I'm hoping there are a few who could help me resolve an issue...

The scenario:
A worksheet used to track the number of papers issued to 50 news carriers
each day. Carriers are paid every 15 days based on the total amount. The
person assigned to maintain this does not have the skills to properly setup
and format a new worksheet to include a column for the route number, the
carrier and columns for the next 15 days, and columns for totals, etc.

The task:
1. The user needs to begin a new pay period which really could be 15 or 16
days depending on how many days in the month.

2. The user clicks icon on the desktop, excel opens with a dialog with 2
choices: Edit Worksheet or Start New Worksheet.

3. If user choice is = to 1, then open worksheet in edit mode (which brings
up another issue which is how to you manage each workbook that already
exists?)

4. If user choice is = 2, then show dialog with a calendar dropdown or
static calendar. User chooses a date to start the pay period (or draw
period), and user choosed an end date.

5. Display dialog to show " confirm dates ", user chooses a create button
and excel opens a new workbook, with the name of the dates (ex:
080108-081508.xls)

6. Worksheet displays the following columns: RouteNum, Carrier, Fri Aug-01,
Sat Aug-02, Sun Aug-03 and so forth, Daily Totals, Weekend Totals, Sunday
Totals, Grand Totals.

Would appreciate any feedback on how this could be accomplished or where I
could go to get the info.

Thanks in advance.