Write and Save Historical Weekly Data - How To?

[BrettOlbrys1]

If someone has a sample file or tutorial, it would be very helpful.

Here is what I want to do, but I have no idea how to accomplish this task:

- Every week, I need to show Sales data from the "prior week" and Sales data from the "current week." The Sales data file is dynamic, so as time passes, the Sales information changes.
- This week's "Current Week" sales data will be next weeks' "Prior Week" data, so it is constantly changing.
- I need to produce a PDF of the final report and I will be doing this with a Macro (to make the PDF creation easy for anyone who wants to create themselves)

When I click the Macro button to create the PDF, I also want the Excel file to write the "Current Week" sales data to another sheet so that even if the raw Sales information changes over time, I will have a record of what it was on the DATE when it was written to the second sheet. And every week the file runs, the new "Current Week" data is captured and DOES NOT overwrite the older captured data, it should just keep capturing onto new rows with the date of when it was run.

Any idea how to do this?

Thanks

Brett