View Full Version : Userform - Copy active row and move to last row
Loss1003
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.
Basically:
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
Loss1003
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)
Else
ClearData
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
'DisableSave
ElseIf r = 1 Then
ClearData
Else
ClearData
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.
Loss1003
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,
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.