Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Macro

Status
Not open for further replies.

cclgroup

Programmer
Aug 9, 2007
5
CA
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
 
You don't need to define a range:
Range(myFirstCell, myLastCell).EntireRow.Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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?
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top