Stopping a Macro

Hi,
Ive got the macro below to run, the only problem is stopping it when it gets
to the bottom of the sheet and ending in an orderly fashion. Ideally I would
like it to go back to A1 when it has got to the bottom of the sheet.
If anyone has any suggestions, they would be very much appreciated.
Thanks for looking.
Paul

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 07/12/2005 by paul
'
' Keyboard Shortcut: Ctrl+z
'
Do
Cells.Find(What:="top 50", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Selection.Copy
ActiveCell.Offset(0, -2).Select
Cells.Find(What:="Total of all issues", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Select
Loop
End Sub

Range("A1").Select

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> Ive got the macro below to run, the only problem is stopping it when it
gets
> to the bottom of the sheet and ending in an orderly fashion. Ideally I
would
> like it to go back to A1 when it has got to the bottom of the sheet.
> If anyone has any suggestions, they would be very much appreciated.
> Thanks for looking.
> Paul
>
> Sub Macro4()
> '
> ' Macro4 Macro
> ' Macro recorded 07/12/2005 by paul
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> Do
> Cells.Find(What:="top 50", After:=ActiveCell, LookIn:=xlValues, LookAt
_
> :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> MatchCase _
> :=False, SearchFormat:=False).Activate
> ActiveCell.Offset(0, -1).Select
> Selection.Copy
> ActiveCell.Offset(0, -2).Select
> Cells.Find(What:="Total of all issues", After:=ActiveCell, LookIn:= _
> xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
> SearchDirection:= _
> xlNext, MatchCase:=False, SearchFormat:=False).Activate
> ActiveCell.Offset(0, 1).Select
> ActiveSheet.Paste
> ActiveCell.Offset(0, 2).Select
> Loop
> End Sub
>

Sorry, I don't understand. Range("A1").Select will take it back to A1, but I
think the tricky bit is working out if the macro has got to the bottom row.
The sheet is normally 10 - 20000 rows deep. It needs some code like"if this
row is the last row, go to A1 and stop"

Paul

"Bob Phillips" wrote:

>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Paul" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > Ive got the macro below to run, the only problem is stopping it when it
> gets
> > to the bottom of the sheet and ending in an orderly fashion. Ideally I
> would
> > like it to go back to A1 when it has got to the bottom of the sheet.
> > If anyone has any suggestions, they would be very much appreciated.
> > Thanks for looking.
> > Paul
> >
> > Sub Macro4()
> > '
> > ' Macro4 Macro
> > ' Macro recorded 07/12/2005 by paul
> > '
> > ' Keyboard Shortcut: Ctrl+z
> > '
> > Do
> > Cells.Find(What:="top 50", After:=ActiveCell, LookIn:=xlValues, LookAt
> _
> > :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> > MatchCase _
> > :=False, SearchFormat:=False).Activate
> > ActiveCell.Offset(0, -1).Select
> > Selection.Copy
> > ActiveCell.Offset(0, -2).Select
> > Cells.Find(What:="Total of all issues", After:=ActiveCell, LookIn:= _
> > xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
> > SearchDirection:= _
> > xlNext, MatchCase:=False, SearchFormat:=False).Activate
> > ActiveCell.Offset(0, 1).Select
> > ActiveSheet.Paste
> > ActiveCell.Offset(0, 2).Select
> > Loop
> > End Sub
> >
>
>
>

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 07/12/2005 by paul
'
' Keyboard Shortcut: Ctrl+z
'
Dim rw as Long
Range("A1").Select
Do
Cells.Find(What:="top 50", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
if rw = 0 then
rw = activecell.row
else
if activecell.row = rw then
Range("A1").Select
exit sub
end if
End if
ActiveCell.Offset(0, -1).Select
Selection.Copy
ActiveCell.Offset(0, -2).Select
Cells.Find(What:="Total of all issues", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Select
Loop
End Sub

--
Regards,
Tom Ogilvy

"Paul" <[email protected]> wrote in message
news:[email protected]...
> Sorry, I don't understand. Range("A1").Select will take it back to A1, but
I
> think the tricky bit is working out if the macro has got to the bottom
row.
> The sheet is normally 10 - 20000 rows deep. It needs some code like"if
this
> row is the last row, go to A1 and stop"
>
> Paul
>
> "Bob Phillips" wrote:
>
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Paul" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > > Ive got the macro below to run, the only problem is stopping it when
it
> > gets
> > > to the bottom of the sheet and ending in an orderly fashion. Ideally I
> > would
> > > like it to go back to A1 when it has got to the bottom of the sheet.
> > > If anyone has any suggestions, they would be very much appreciated.
> > > Thanks for looking.
> > > Paul
> > >
> > > Sub Macro4()
> > > '
> > > ' Macro4 Macro
> > > ' Macro recorded 07/12/2005 by paul
> > > '
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Do
> > > Cells.Find(What:="top 50", After:=ActiveCell, LookIn:=xlValues,
LookAt
> > _
> > > :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> > > MatchCase _
> > > :=False, SearchFormat:=False).Activate
> > > ActiveCell.Offset(0, -1).Select
> > > Selection.Copy
> > > ActiveCell.Offset(0, -2).Select
> > > Cells.Find(What:="Total of all issues", After:=ActiveCell,
LookIn:= _
> > > xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,
> > > SearchDirection:= _
> > > xlNext, MatchCase:=False, SearchFormat:=False).Activate
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveSheet.Paste
> > > ActiveCell.Offset(0, 2).Select
> > > Loop
> > > End Sub
> > >
> >
> >
> >

Mr. Paul,
I have also been in a tricky situation like this. I found a way out
to get the last row of the sheet, but under one condition. The sheet
should have a column of data in which every row has data, "Row number"
or "Sl. No." feilds, for example. You know the starting row of the
first data in this column (let it be A5). To find the last row, use
the code...

Range("A5").xldown.row

It is the same as pressing control and down arrow when you are at A5.
And it would be better to use the for each loop as given below

For each c in Range("A5:A" & Range("A5").xldown.row).cells

<Write your code>

next



Understood?