View Full Version : Solved: Row copy
Sephir
08-28-2007, 12:51 AM
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
08-28-2007, 12:57 AM
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
08-28-2007, 01:34 AM
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
08-28-2007, 01:57 AM
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
08-28-2007, 05:39 AM
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
08-28-2007, 06:19 AM
Apart from all of the unnecessary selecting, that is as good a way as any.
Sephir
08-28-2007, 07:06 AM
Apart from all of the unnecessary selecting, that is as good a way as any.
Thank you for all
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.