Selecting dynamic range within macro.

Would really appreciate any help you guys can offer.
Background
I am creating a workbook that calculates current week payroll figures (hours worked, overtime, holidays etc), which is straightforward. The results of formula that perform the calculations are then stored in a number of separate database sheets in which each column refers to a particular week. The data in these sheets is placed there via a series of vlookup’s. These sheets contain 53 columns of data, reflecting a full years worth of weekly figures.
The problem
Once a week is complete, I need to clear the weekly input sheet in readiness for the following weeks input. Before doing this, the data that is subject to the vlookups needs to be fixed (for want of a better phrase). If doing this manually, I would copy/paste values for the relevant week in the database sheets prior to deleting the input data. The question is, how do I achieve this within a macro when the column on which to perform the copy pastevalues could be any one of 53 columns, depending upon which week has just been completed. Each column has the week number at the top, as does the input sheet. So manually it is easy i.e. if week 4 is complete, then copy paste value on the column with week 4 at the top. Can this be done relatively simply using a macro?
Attached is an example of the sheets involved. Thanks for your help .