View Full Version : Userform - Copy active row and move to last row

11-30-2010, 02:02 PM
I have a user form with an account name field, location number field, and various miscellaneous fields. However, I’m at a lost trying to copy the active row and move to the last row in the worksheet.


I want a copy command button that copys the currect rowdata showing in the userform and paste it to the last row of the worksheet.

Private Sub CommandButton1_Click()
Dim lastrow As Long

lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Rows(lastrow).Copy Destination:=Rows(lastrow).Offset(1)

something like the above.

Any help is appreciated

Kind Regards,
: pray2:

Kenneth Hobs
11-30-2010, 02:41 PM
Private Sub CommandButton1_Click()
Dim lastrow As Long
If WorksheetFunction.CountA(Cells) = 0 Then Exit Sub
lastrow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Rows(ActiveCell.Row).Copy Destination:=Rows(lastrow + 1)
Application.CutCopyMode = False
End Sub

11-30-2010, 02:55 PM
Thanks for the quick reply..

I'm having difficutly with the above code due to when viewing the records in the userform, the active cell in the worksheet is stagnet and does not move to the selected row of data per the viewed data record.

Any way to correct that?

I'm using the standard get data, put data codes

Get Data example:

Private Sub GetData()
Dim r As Long
lastrow = FindLastRow + 1
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)

MsgBox "Illegal Row Number"
Exit Sub
End If

If r > 1 And r <= lastrow Then
UserForm1.ActName.Value = Cells(r, 1).Value
UserForm1.Formnum.Value = Cells(r, 2).Value
UserForm1.ActEdNo.Value = Cells(r, 3).Value
UserForm1.RepDate.Value = Cells(r, 4).Value
UserForm1.RepDueDate.Value = Cells(r, 5).Value
UserForm1.MailAdd.Value = Cells(r, 6).Value
UserForm1.CITY1.Value = Cells(r, 7).Value

UserForm1.STATE1.Value = Cells(r, 8).Value

UserForm1.Formnum.Value = RowNumber.Text - 1

ElseIf r = 1 Then
MsgBox "Invalid Row Numer"
End If

Kenneth Hobs
11-30-2010, 03:36 PM
Set the userform's ShowModal property to False or add the vbModeless parameter to your userform1.show method.

12-01-2010, 09:00 AM
How do I reverse the method.. While I'm in the userform or selectling the first, previous, next, or view last record I’d like to row in the worksheet to match the record I'm viewing.

Kind Regards,