Finding and Copying
Posted by Kathi Budden on February 01, 2001 7:49 AM
DDE
1
2
3
4
5
KILL
DDE
6
7
8
9
10
KILL
DDE
11
12
13
14
15
KILL
I need to loop through and find DDE and then find KILL, select the row after DDE through KILL, and cut into new sheet.
So at the end of sheet 1,
1-5 are left in Sheet 1
6-10 are in Sheet 2
11-15 are in Sheet 3
and so on
PLEASE HELP!!!!!
Posted by Faster on February 01, 2001 4:03 PM
Sub DDE_KILL()
'make a backup of your work before you start
'++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++
'you should make a backup of your spreadsheet before you start
'++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++
'+++++++++++++++assumptions++++++++++++++++++
'Name your DDE KILL sheet "Main"
'you are starting on the first DDE
'there are no blank cells between DDE and KILL
'good luck
Dim RangeStart
RangeStart = ""
Dim RangeEnd
RangeEnd = ""
Do While Selection <> ""
Do Until RangeEnd <> Empty
ActiveCell.Select
If Selection = "DDE" Then
Selection.Offset(1, 0).Select
RangeStart = ActiveCell.Address
ElseIf Selection = "KILL" Then
Selection.Offset(-1, 0).Select
RangeEnd = ActiveCell.Address
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop
Range(RangeStart, RangeEnd).EntireRow.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
'the name of your spreadsheet must be "main" to work
Sheets("Main").Select
Range(RangeEnd).Select
Selection.Offset(2, 0).Select
RangeStart = ""
RangeEnd = ""
Loop
End Sub
