Archive Macro

M

Michael

Hi All
I have a macro (see below), that archives information but
needs some fine tuning.
I have two questions that I hope someone can answer.
Firstly, in the selected ranges, they are not always fully
populated. Is it possible for the macro to detect if there
is an entry in P1:p60 AND AC1:AC60 and only allow those
lines that are populated to be archived.
Secondly, I want to add the current date to each row that
has been archived in column "A".

I would appreciate any assistance that can be offered.

Regards
Michael

Sheets("STROKE").Select
Range("E5:p60").Select
Selection.Copy
Sheets("ARCHIVE").Select
Range("B1").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("STROKE").Select
Range("R5:AC60").Select
Selection.Copy
Sheets("ARCHIVE").Select
Range("B1").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False
End Sub
 
B

BrianB

<<only allow those lines that are populated to be archived>>
The quickest way is to sort the range so the blanks are put to th
bottom, otherwise you need a macro to delete the empty rows.

<<add the current date to each row>>
Worksheets("STROKE").Range("A1:A60").Value = No
 
M

Michael

Brian
Thanks for your response. The date part was perfect.
However, due to the layout of the range of cells, I can't
sort and put the blanks to the bottom......so the Macro is
the better option.
Ideally the Macro would look in "P1:p60" and if there was
nothing in the cell it would delete the row. Is this
possible with a Macro.
Regards
Michael
 
P

Paul B

"Ideally the Macro would look in "P1:p60" and if there was
nothing in the cell it would delete the row."

Michael, this should do it

[P1:p60].SpecialCells(xlCellTypeBlanks).EntireRow.Delete

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Top