I'm passing on your suggestion to my collegue, who is partnering me on this,
for their opinion. We are using Office 2000. I will get back to you next
week with more information.
Thanks,
Dragon

"OssieMac" wrote:

Hi Dragon,

I copied your data to my worksheets and studied it. I now believe that I
understand what you are trying to achieve. However, it is a complex issue to
create code and be sure that you will always have the correct results because
there are too many variables to be controlled and even a minor error by a
user in entering data will result in the wrong cells being referenced for the
reports.

I think that you would be better to change the method of recording the data
and then use AutoFilter to produce your Daily, Weekly and Monthly reports.
The whole lot can go on one worksheet and the reports are fully dynamic and
far less prone to problems that would be created if a macro picks up data
from the wrong row.

I can give you some more guidance on setting up AutoFilter and then how to
set up the worksheet if you want to adopt this method. However, let me know
what version of XL you are using because XL2007 has more functionality than
earlier versions and I would do it a little differently. The method I would
use for earlier versions will still work in xl2007 if you upgrade later.

The AutoFilter method will give you Daily, Weekly and Monthly totals
including the YTD totals. You can have them on a separate worksheet but I
like them on the same worksheet above the actual data because I put the
column headers down about row 10 and then freeze the panes from the column
headers up and the reports are above the data. This way I do not have to
scroll up and down the screen or change worksheets to view reports after
setting the filters.

Another advantage is that all the reports are always available for any Day,
Week or Month just by re-setting the filters. They are not overwritten.

By the way your example was great. An example is like a picture. Worth a
thousand words.

--
Regards,

OssieMac
"OssieMac" wrote:
Perhaps you can you post samples of the data to go with the explanation. I
really don't understand what you are trying to achieve. However, naming
cells/ranges is a good start in retrieving the last data copied and then
using offset to move down and re-naming to the same name ready for the next
time.
You can record macros when naming cells/ranges to get the syntax. To name
cells/ranges:
Versions before xl2007: Menu item Insert - Name - Define

"Dragon" wrote:
In our excel file we have 11 workbooks with running totals that update daily,
weekly and monthly reports in 3 other workbooks. We would like to make up
Macros so that the reports will update automatically when run. To do this we
need the daily cell reference to move one down during the week, 4 down at
week end, and 2 at month end. I think it will take 3 Macros to do this but I
have tried to make the reference move down one but it doesn't seem to work.
How can I set up Macros to do what I need?