Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste loop
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste loop
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Loop to Find then Copy & Paste | Excel Discussion (Misc queries) | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
copy,paste and loop through workbook | Excel Discussion (Misc queries) | |||
constructing a copy-paste loop that skips rows | Excel Programming | |||
search & copy with loop | Excel Programming |