Requesting Help Please on Auto Worksheet Setup

[Rob]

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.

 

[Joel]

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.

 

[Roger Govier]

Hi Rob

My approach to this would be slightly different, using three sheets which do
not alter.

The first sheet called Data Entry, would have just 2 columns
"Date" in A1 and Carrier names in A2:A52
Input Date in B1
Followed by entry of number of papers that day for each carrier down through
B2:B52

The second Sheet called Data, would have column A copied, then Paste
Special>Transpose to cell A1

On the Data Entry sheet, I would have a button with the following macro
attached

Sub MoveData()
Dim lrs As Long, lrd As Long
Dim wss As Worksheet, wsd As Worksheet
Set wss = ThisWorkbook.Sheets("Data Entry")
Set wsd = ThisWorkbook.Sheets("data")
lrs = wss.Cells(Rows.Count, "A").End(xlUp).Row
lrd = wsd.Cells(Rows.Count, "A").End(xlUp).Row + 1
wss.Range("B1:B" & lrs).Copy
wsd.Range("A" & lrd).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wss.Range("B1:B" & lrs).Clear
End Sub

So there would be a simple task for the operator to enter the date in B1 of
Data Entry, followed by Paper Numbers, then press the button.
This would move the data to the next line of the Data sheet, and clear
column B ready for the next day's input.
All data would be collected for the whole year on the sheet called Data
I would set up dynamic ranges on Sheet Data for
Carrier, Date and Data

Carrier =Data!$B$1:INDEX(Data!$1:$1,COUNTA(Data!$1:$1))
Date =Data!$A$2:INDEX(Data!$A:$A,COUNT(Data!$A:$A)+1)
Data =INDEX(Data!$2:$65536,1,MATCH(Report!$A2,Customer,0)+1):
INDEX(Data!$2:$65536,COUNT(Date),MATCH(Report!$A2,Customer,0)+1)


On a third sheet called Reports, I would have
"From" in A1, "To" in A2 blank A3 and Carrier Names in A4:A54
In B1 enter the Start date for Billing, and B2 End date for Billing
In B3 I would put 5, C3 6, and D3 7
In B4 I would use the formula
=SUMPRODUCT((Carrier=Report!$A4)*(Date>=Report!$B$1)*(Date<=Report!$B$2)*(WEEKDAY(Date)<=B$3)*Data)
In C4
=SUMPRODUCT((Carrier=Report!$A4)*(Date>=Report!$B$1)*(Date<=Report!$B$2)*(WEEKDAY(Date)=C$3)*Data)
and copy this to D4
Copy D44 down through B4:B54

You would then, having set your dates, have the number of papers for
Weekdays, Saturdays and Sundays for each Carrier to use for your billing

 

[Joel]

There is no reson to run a macro everyday. when you set up the new sheet
fill in all the headers for the columns and make the total columns a formula
that would automatically update when each days totals were added.

 

[Roger Govier]

Hi Joel

I'm probably being very dense, but I don't fully understand what you are
saying.
Sure, you could just use 2 sheets and have the user fill in the daily entry
on the next available row, going across 50 columns to make the days entry
However OP said the user is not very spreadsheet "aware".
In my experience, keeping the "user" away from the main data repository, and
giving them a simple input "form" where they can see the full names of each
carrier and having less scrolling to do to complete the series of data
entry, leads to fewer problems.
Just my opinion.

 

[Joel]

I don't see wha is so complicated about having the Carriers names in column A
and the 15 day of the pay period across rows 1 and 2 is very complicated.
The 3 totals can ber at the right of the 15 dates. The only complication is
the peopel who enter the information aren't skilled enough to copy templetes
and get the 15 days of the pay period set up properly.