Compare data on separate worksheets

[Wally7552]

Hello, I do not actually know exactly what I need to ask to get the results I am looking for so I will explain what it is I am trying.

I have a spreadsheet with five tabs, one for each week in a month and one for an 'active week'.

The point of this is that data is downloaded from a different system each week and put into the relevant tab. The problem with this data is that it is a cumulative total for the month thus far, and does not represent the data of any given week.

I am trying to set up the 'active week' sheet to display the difference between the most recently downloaded data, and that of the previous week.

The problem I am having is getting the system to identify which week is the most recent week and therefore which sheets to compare to get the data required.

Could anyone give me an idea of the area I am looking for (if it is possible) so I can investigate further in the right direction? as I say, I don't even know the right question to ask at this point, let alone what the answer would be!

I appreciate this post has an air of vagueness about it, but any advice would be helpful.

Many thanks

[stevebriz]

Can you post a saple of the workbook ...and Is it the same workbook you use for every month?

[Charlize]

If you current week is named 'current week' you could name the tabs of the other weeks by using some kind of date ie 20080525 for this week. So next week current week becomes 20080525. On monday you perform a new week macro to rename the current week to date-1 and reformat the tabname.

Based on this workflow you could use the dateserial() function to loop through every worksheet of your workbook, exept the one named current week.

Compare the datevalue of the worksheettab with a variable. The variable starts with an empty value so the first tab is higher than the variable. Store the tabname in a datevariable because tabname is higher. Go on until every worksheet is processed.

Charlize

[Wally7552]

Thank you for your quick replies, yes it is the same workbook we use every month - the data will be written over each time. Charlize I willl try what you have suggested and repost if I have any problems.

Thanks again!