Variable Range

Martin:

Try the AutoFilter command. Select the first cell in the date column, click on Data>Filter>AutoFilter. A drop down arrow will then appear in the cell allowing you to select any date that appears in the column. The nice feature is that the other columns will follow suit and show only rows that match the date you select.

Once selected, you can copy and paste only those rows of data related to the selected date from the first column.

Scott

Some other alternatives

Martin
Some other alternatives :-

1. Merge the two workbooks.
Then each day do something like this to retain the data/files you need:
-save the file as the current date
-for sheets that need to be retained in “yesterday’s” file, convert all cells to values
-delete the unwanted sheets from “yesterday’s” file.
Whatever saving/converting/deleting/etc is necessary re the above could be done by a macro.

OR

2.Use a macro to automatically update the refs in the formulas.
Try this :-
On each sheet in your dependant workbook reserve two unused cells (let’s say cells A1 and B1) and do the following :
-format cell A1 as Text
-format cell B1 as Date in the format d-m-yy
-in cell A1, enter the date that is currently used in the formulas on the sheet (i.e. the date being used as the name of the current source file)
-in cell B1 enter the formula =TEXT(NOW(), “d-m-yy”)
The above steps are only required to set things up. You will not have to do them again.

Put the following procedures in their appropriate place in the dependant workbook. Whenever the workbook is opened, the formulas will be automatically updated to link with the file name that uses the current date. Please note that it will only update once at the time it is opened, it will not update again while it remains open.

Private Sub Workbook_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Activate
With ws.Range("B1")
.Value = Now()
.Formula = "=TEXT(NOW(),""d-m-yy"")"
End With
UpdateLinks
Next
End Sub

Sub UpdateLinks()
Dim oldDate As Range, newDate As Range, rngToCheck As Range, cell As Range
Set oldDate = Range("A1")
Set newDate = Range("B1")
Set rngToCheck = ActiveSheet.UsedRange
Application.ScreenUpdating = False
For Each cell In rngToCheck
If cell.HasFormula Then
cell.Replace What:=oldDate.Value, Replacement:=newDate.Value, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
End If
Next
oldDate = newDate
End Sub

If you prefer not to update the links automatically when the dependant workbook is opened, then delete the line “UpdateLinks” from the Workbook_Open macro, change the UpdateLinks macro per below, and run it whenever you want to update.

Sub UpdateLinks()
Dim oldDate As Range, newDate As Range, rngToCheck As Range, cell As Range, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Activate
Set oldDate = Range("A1")
Set newDate = Range("B1")
Set rngToCheck = ActiveSheet.UsedRange
For Each cell In rngToCheck
If cell.HasFormula Then
cell.Replace What:=oldDate.Value, Replacement:=newDate.Value, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
End If
Next cell
oldDate = newDate
Next ws
End Sub

Celia