VBA For New Column, moving right, saving previous value

[bmpreston]

So this I believe is difficult. Difficult just to explain.

I'm looking to take a column and make each cell equal to values from throughout the workbook. (C1=worksheet.B1) etc...

Then this file will be saved, next week, the sheet is reopened and I want to copy the formulas to a new column... Column D, and it grabs new values of (D1=worksheet.B1)....

Each week the column moves and new values are saved in the corresponding column.

Add a column, grab values, save values permanently, then run it again, and it adds a column, gets the new values, saves them permanently.

Currently, I have two separate spreadsheets. I update the values, save as a unique sheet, then open the sheet which contains the below... I then copy and paste to the right, and edit the file name slightly to point to the new permanently saved file previously updated and saved.

Thanks in advance. I hope this can be done.

Initial Values
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]DATE of WEEK ONE[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]VALUE FROM WORKSHEET AS EQUALS FORMULA (WorksheetName.H4)[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]VALUE FROM WORKSHEET AS EQUALS FORMULA (DifferentWorksheetName.H4)[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]VALUE FROM WORKSHEET AS EQUALS FORMULA (CompletelyDifferentWorksheetName.H4)[/TD]
[/TR]
</tbody>[/TABLE]

First Time Macro Runs
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]DATE OF WEEK ONE[/TD]
[TD]DATE of WEEK Two (=B1+7)[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]VALUE OF WORKSHEET AS EQUALS FORMULA[/TD]
[TD]NEW VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]VALUE OF WORKSHEET AS EQUALS FORMULA[/TD]
[TD]NEW VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]VALUE OF WORKSHEET AS EQUALS FORMULA[/TD]
[TD]NEW VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
</tbody>[/TABLE]

Second Time Macro Runs
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]DATE OF WEEK ONE[/TD]
[TD]DATE of WEEK Two (=B1+7)[/TD]
[TD]DATE OF WEEK Three (C1+7)[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]VALUE OF WORKSHEET AS EQUALS FORMULA[/TD]
[TD]NEW VALUE OF WORKSHEET FORMULA[/TD]
[TD]NEWEST VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]VALUE OF WORKSHEET AS EQUALS FORMULA[/TD]
[TD]NEW VALUE OF WORKSHEET FORMULA[/TD]
[TD]NEWEST VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]VALUE OF WORKSHEET AS EQUALS FORMULA[/TD]
[TD]NEW VALUE OF WORKSHEET FORMULA

[/TD]
[TD]NEWEST VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 576px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]NEWEST VALUE OF WORKSHEET FORMULA[/TD]
[/TR]
</tbody>[/TABLE]
</body>