Copy Active Cell Range

February 19, 2009 at 08:16:17
Specs: Windows XP
I am trying to create a macro to copy and paste data from a row selected from another macro and paste it into a new Worksheet.

I have a spreadsheet of clients. Each row is a different client and the columns are defined by advertiser code, name, address, etc etc. I setup a macro search form to find an advertiser by its code and looking to then have a macro button once they find the advertiser they are looking for to copy the information from that row over to a new spreadsheet so i can have them fill out a work order for that client using their existing information.

The advertise list sheet is "AI" and the potential Work Order Sheet is "PWO".

This is the code I have for the lookup button

Private Sub CommandButton1_Click()
Dim MyText As String
Dim MyMsg As String
Dim MyType
Dim MyTitle As String

MyText = TextBox1.Value
MyMsg = "No matches were found."
MyType = vbOKOnly + vbExclamation
MyTitle = "No Matches"

On Error GoTo ErrorHandler
Cells.Find(What:=MyText, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Exit Sub
MsgBox MyMsg, MyType, MyTitle

End Sub

Any help at all would be greatly appreciated!!

See More: Copy Active Cell Range

Report •

February 19, 2009 at 10:52:00
Do you need an extra button to do the copying? You could do the copying right after the Find.

Obviously you could put the Copy process onto a different button, but either way I think using the .EntireRow.Copy method is what you are looking for.

Something like...

With Cells
 Set c = .Find(What:=MyText)
  If Not c Is Nothing Then
   c.EntireRow.Copy Destination:=Sheets("PWO").Range("A1")
  End If
End With

Report •
Related Solutions

Ask Question