Solved: Row copy

[Sephir]

Hello!

I am trying to do simple find cell, find 2nd cell, copy between them function. Code:

workbook1.Activate
Cells("1", "A").Select
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
first = ActiveCell.Row

Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
second = ActiveCell.Row

Rows(stra:stra2).Copy ' getting error on this!!!

workbook2.Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
workbook1.Activate

I am getting error on Rows.copy line. Any help with this? Can anybody suggest any better solution? I want to keep it simple... Thank you

[Bob Phillips]

Dim rngFound As Range
Dim first As Long, second As Long

workbook1.Activate
Set rngFound = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
first = rngFound.Row

Set rngFound = Cells.Find(What:="2", _
After:=ActiveCell, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
second = rngFound.Row

Rows(first & ":" & second).Copy

workbook2.Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
workbook1.Activate
End If
End If

[Sephir]

Dim rngFound As Range
Dim first As Long, second As Long

workbook1.Activate
Set rngFound = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
first = rngFound.Row

Set rngFound = Cells.Find(What:="2", _
After:=ActiveCell, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
second = rngFound.Row

Rows(first & ":" & second).Copy

workbook2.Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
workbook1.Activate
End If
End If


Thank you xld. just one question:

how to make it always start to search from A1?

[Bob Phillips]

Sorry, missed that bit



Dim rngFound As Range
Dim first As Long, second As Long

workbook1.Activate
Set rngFound = Cells.Find(What:="1", _
After:=Range("A1"), _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
first = rngFound.Row

Set rngFound = Cells.Find(What:="2", _
After:=rngFound, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
second = rngFound.Row

Rows(first & ":" & second).Copy

workbook2.Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
workbook1.Activate
End If
End If

[Sephir]

Sorry, missed that bit



Dim rngFound As Range
Dim first As Long, second As Long

workbook1.Activate
Set rngFound = Cells.Find(What:="1", _
After:=Range("A1"), _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
first = rngFound.Row

Set rngFound = Cells.Find(What:="2", _
After:=rngFound, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngFound Is Nothing Then
second = rngFound.Row

Rows(first & ":" & second).Copy

workbook2.Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
workbook1.Activate
End If
End If

Thank you. Probably (I hope) last question. ActiveCell.Offset(1, 0).Select selects next row and then copies another stuff into it. How can I move to the end of copied stuff (last cell), but to the column A? I was thinking of:

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Select

But how to move to the column A?

Nevermind! I did:
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Select
Cells(ActiveCell.Row, 1).Select
and it seems to work. Is there some better solution? Or is this OK?

[Bob Phillips]

Apart from all of the unnecessary selecting, that is as good a way as any.

[Sephir]

Apart from all of the unnecessary selecting, that is as good a way as any.

Thank you for all