Need to create table that shows last rolling 30 business daysdifferences on 4 accounts

[f252863]

Hi,

Each day I create a new worksheet which shows the differences for the
prior business day on 4 accounts. I would like to be able to see the
aggregate differences for the past 30 business days on each account.
To make it simple I post a simplified version of this here. "Data
Entry" is the part of the worksheet that shows the differences for the
prior business day. "Table" is one way I envision getting the totals
for each person over the last 30 business days. I have no clue how to
program this however.

On this day (4/29) I need to update the table. Since this is day 31
the data for March 17 needs to be erased, and April 28 is appended to
the end. The revised totals should show John 3, Frank 1, Sally 2, and
Roger 1 (since he had a difference today). The next day I will copy
this worksheet, calculate the new differences and repeat the process.
Thanks to any of you who take the time to look at this.

Data Entry:

Prepared By: John Smith
Today's Date: 4/29 Reconciled For: 4/28

Person Balances Differences

John 127,500.00 $-
Frank 86,155.55 $-
Sally 99,528.27 $-
Roger 111411.22 $155.00
-----------------------------------------------------------------------------------------------------------------
Table:

John Frank Sally Roger
17-Mar 0 1 0 0
18-Mar 0 0 0 0
19-Mar 0 0 0 0
20-Mar 0 0 0 0
21-Mar 1 0 0 0
24-Mar 0 0 0 0
25-Mar 0 1 0 0
26-Mar 0 0 0 0
27-Mar 0 0 0 0
28-Mar 0 0 0 0
31-Mar 0 0 0 0
1-Apr 0 0 0 0
2-Apr 0 0 0 0
3-Apr 1 0 0 0
4-Apr 0 0 0 0
7-Apr 0 0 0 0
8-Apr 1 0 0 0
9-Apr 0 0 0 0
10-Apr 0 0 1 0
11-Apr 0 0 0 0
14-Apr 0 0 0 0
15-Apr 0 0 0 0
16-Apr 0 0 1 0
17-Apr 0 0 0 0
18-Apr 0 0 0 0
21-Apr 0 0 0 0
22-Apr 0 0 0 0
23-Apr 0 0 0 0
24-Apr 0 0 0 0
25-Apr 0 0 0 0

Total 3 2 2 0

 

[Joel]

I assume the total at the bottom of the worksheet are fgormulas like
=sum(B2:B31). You don't want to delete and add rows because it will effect
the formulas. Te best was is simply copy rows 3 to 31 to rows 2 to 30. Ten
enter your new data in row 31. Try this one line macro

Range("A3:A31").Copy _
Destination:=Range("A2")

 

[Joel]

I only copied column A. You need to copy columns A to E

Range("A3:E31").Copy
Destination:=Range("A2")