Macro to copy data on a weekly basis for trend analysis

Dear all,

Since I'm fairly new to the whole concept of VBA/Macro I need help. I've attached a sample document in order to create a more tangible situation.

I'm in the process of creating a management dashboard and I want to include a trend analysis. I'm receiving several data dumps (Forecast & Budget) every week and made a Dashboard sheet linked to these data dumps with the use of formula's. Included in these formula's is an IF formula where blank cells return as "". This enables me to stretch the formula's down in order to always have all data in that one sheet eventhough the data dump get's longer for example.

The Macro I would like to create is to copy the weekly data from the Dashboard (Location of the Macro button) sheet into the Trend data sheet with exception of the project manager column. Every week we copy the data it pastes the data below last weeks data. To seperate the two weeks I would the Macro to automatically add the week number of TODAY() (the week in which we used the Macro).

Problems arise because of the IF formula. The cells aren't actually empty so the Macro copies 'empty cells'. Resulting in loads of empty rows every week in between. Secondly I have no idea how to add a data stamp in every row of which the Macro copied data.

I've started the code in my original file, it actually copies the data and pastes it under the previouse week:

Please Login or Register  to view this content.

Can someone please help?

Thanks in advance.

Regards,
Max

Excel Forum Test file.xlsx

Are Budget and Forecast sheets deleted every weeks? I mean you start from scratch every week. This means your Dashboard sheet is a view of only one week of data refreshing itself as the data in the other 2 sheets are changed?
But you want to keep track of old values in the Trend sheet, right?
Would you like a macro to populate your Dashborad sheet instead of using formulas?
Data transfer to the Trend sheet must be run only once, right? Or do you want to run it several times during the week? If this is the case, we'll have to make sure not to create duplicates.

Let me know how you see all of this.

BTW, look at rule #3 and make sure you use the CODE TAGS around your lines of code.

try this macro, you can adjust the ranges to suit your actual sheets' names.

Please Login or Register  to view this content.