I have a suite of reports driven by daily bottom line numbers. One of
these numbers is an aged backlog, which is fine for today however
retrospectively does not work (Log is updated with no audit trail)

Due to this I have recreated the log to work retrospectivley i.e. by
entering a date the report will generate all my aged backlogs correct
for that day. The resulting data for that date is a simple range.

What I'd like to do to automate the creation of this data quickly is to
have a macro which starts on the date my data begins (01/04/03), copies
the range, pastes as values into a separate sheet (next blank cell
down), then loops by incrementing the date, until date equals todays
date.


Any assistance would be most appreciated

Steve S


---
Message posted from http://www.ExcelForum.com/

Try this:

CurrentDay = "1/4/03"
Counter = 0
Do While Format(CurrentDay, "m/d/yy" < Format _
(Date, "m/d/yy")
Range("A1").Select
Set CurrentDay = Cells.Find(what:=Format(DateAdd("d", _
Counter, CurrentDay), "m/d/yy"), _
after:=ActiveCell, LookIn:= xlValues, _
lookat:=xlPart, Searchorder:=xlByRows)
If Not CurrentDay Is Nothing Then
CurrentDay.Select
Range(ActiveCell, ActiveCell.Offset(X,Y)).Copy
Sheets(SeparateSheet).Activate
ActiveCell.PasteSpecial
ActiveCell.Offset(X + 1, 0).Select
Sheets(OriginalSheet).Select
End If
Counter = Counter + 1
Loop

1) You should change "X" and "Y" to the numbers of cells
you need to offset to copy the whole range.
2) You will need to set the variable "SeparateSheet" to
the sheet where you want to copy the range
and "OriginalSheet" to the sheet where you are copying
from.
3) Depending on how you have recorded your dates you may
need to change all the formatted dates in the code above
to something like "mm/dd/yy" instead of what I have
entered - "m/d/yy"
4) I use something like this in a macro of mine but I have
not actually run this code so you may need to adjust it a
bit before it does what you want.

-IA

-----Original Message-----
I have a suite of reports driven by daily bottom line
numbers. One of
these numbers is an aged backlog, which is fine for today
however
retrospectively does not work (Log is updated with no
audit trail)

Due to this I have recreated the log to work
retrospectivley i.e. by
entering a date the report will generate all my aged
backlogs correct
for that day. The resulting data for that date is a
simple range.

What I'd like to do to automate the creation of this data
quickly is to
have a macro which starts on the date my data begins
(01/04/03), copies
the range, pastes as values into a separate sheet (next
blank cell
down), then loops by incrementing the date, until date
equals todays
date.


Any assistance would be most appreciated

Steve S


---
Message posted from http://www.ExcelForum.com/

.