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" wrote:

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
SpecialTranspose 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 D4:D4 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

--
Regards
Roger Govier

"Rob" wrote in message
...
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.