Excel – Fix a (Today) Date in Excel

In some cases where you want to get a static answer from a formula you can do what you want without using a formula.

In your example Excel doesn't know whether TODAY() was meant to mean today's date or the date that it was entered in the spreadsheet.

You can, however insert the current date and/or time in a cell: You can select a cell and press CTRL+; or CTRL+SHIFT+; for current date and current time respectively (for both CTRL+; then SPACE then CTRL+SHIFT+;).

In other cases, I tend to copy and then paste special as value.

Since you're starting with a macro solution, I'm assuming you have enough programming knowledge to put the final result together; so given that, the main pieces I think you need are:

Get started

Make sure the developer ribbon is showing, then switch to the VBA editor (Alt-F11).
Read through this page to get an appreciation of the overall process; some of the examples may be very close to what you want (e.g. 'Automatically Creating Workbook Files from Worksheet Data').

Reading a cell

dim myVar1 as string
myVar1 = Sheets("Sheet1").Range("A1")

That will allow to read from any particular cell on any particular sheet.

Aggregating cell values

Dim myvar2 As Integer
myvar2 = Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("B4:B6"))

That will allow you to use any of the standard worksheet functions, on any range of cells.

Writing to a cell

Sheets("Sheet2").Range("A1") = myVar1

(simply the reverse of reading)

Connecting the macro to a button on a sheet

After you create your macro, add a button to your master sheet wherever makes the most sense, following this guide.

Good luck!

EDIT 1:

Writing based on offset

To write to a particular cell based on a date offset, you could use the DateDiff function to get the distance to offset, e.g.:

Dim startDate As Date
Dim currDate as Date
Dim dateOffset As Integer

startDate = #10/21/2011#
currDate = Sheets("Summary Sheet").Range("F3")
dateOffset = DateDiff("d", startDate, currDate)

Then, you can write to a particular cell in the same fashion you already are:

ActiveSheet.Range("A1").Offset(0, dateOffset) = Total1
ActiveSheet.Range("A1").Offset(0, dateOffset+1) = Total2

etc

EDIT 2:

Finding a date

Based on your comment: you can find a date in another sheet as follows:

Dim selectedDate As String
Dim rangeFound As Range
selectedDate = ActiveCell.Value
Set rangeFound = Sheet2.Cells.Find(CDate(selectedDate))

Obviously you'd change Sheet2 to whatever you wanted. That will give you the address of the cell containing the date on the summary sheet. If you use rangeFound.Address you will get a value similar to $D$9. If you use rangeFound.Row and rangeFound.Column you will get the actual row and column numbers. Note that you also have the address of the active cell through the same principle (e.g. ActiveCell.Row). From there, you can copy values using something like

If Not (rangeFound Is Nothing) Then
    ActiveCell.Offset(0, 1) = rangeFound.Offset(0, 1)
    ActiveCell.Offset(0, 2) = rangeFound.Offset(0, 2)
End If