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
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