Excel Macro

[cclgroup]

Hey I'm trying to create a macro which searches for a certain range and then copies and pastes it into a new sheet. The problem i'm having is finding and defining the range. The range starts with a portfolio code and end with that same portfolio code; and I want to select everything in between those two codes and copy and paste into another sheet, this what I have so far. I'm pretty new at this so please bear with me. Thanks in advance:

Sub Find()

Sheets("Extract").Select
Application.ScreenUpdating = False

Dim myLastCell$
Dim myFirstCell$

Dim myRange As Range


Cells.Find(What:="(866)", After:=[A1], LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate
myFirstCell = ActiveCell.Address


Cells.FindNext(After:=ActiveCell).Activate
myLastCell = ActiveCell.Address


Set myRange = Range(myFristcell, myLastCell).EntireRow.Select

Application.CutCopyMode = False
Selection.Copy
Sheets("Macro").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub

 

[PHV]

You don't need to define a range:
Range(myFirstCell, myLastCell).EntireRow.Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cclgroup]

Thanks!

I made the change and now the code doesn't produce any errors...but it still doensn't select the whole range. Only the first row gets copied and pasted...is there a way around that?

 

[PHV]

Even this (without all that select/activate stuff) ?
Range(myFirstCell, myLastCell).EntireRow.Copy Sheets("Macro").Range("A1")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886