CODEWITHSUNDEEP
vbaexcelworksheet
user3331363
user3331363

Reputation: 345

Copy the Current active row from one worksheet to another

I have a question on copying the current active row from one worksheet to another. Say I have the following columns Name, Address, City, Tel, Zip Code in Sheet1 and I have similar columns in Sheet 2. Say i'm currently say on Row 100 or 500 or 1000 of Name column of Sheet 1. I want to only copy that row to sheet 2 and populate the columns in Sheet 2. So far I have this code. Please let me know how to proceed.

Private Sub CommandButton1_Click()
Dim CustomerName As String, Customeraddress As String, Customercity As String, Custtel As String, Custzip As String
Worksheets("sheet1").Select
CustomerName = Range("A2")
Customeraddress = Range("B2")
Customercity = Range("C2")
Custtel = Range("D2")
Custzip = Range("E2")
Worksheets("sheet2").Select
Worksheets("Sheet2").Range("B4").Select
If Worksheets("Sheet2").Range("B4").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Customeraddress
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Customercity
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Custtel
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Custzip
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("C4").Select
End Sub

Upvotes: 1

Views: 2996

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Command button is placed in Sheet1

Try this code:

Private Sub CommandButton1_Click()
    Dim lastrow As Long

    With ThisWorkbook.Worksheets("Sheet2")
        lastrow = Application.Max(4, .Cells(.Rows.Count, "B").End(xlUp).Row + 1)
        .Range("B" & lastrow).Resize(, 5).Value = _
            Range("A" & ActiveCell.Row).Resize(, 5).Value
    End With
End Sub

Upvotes: 3

Related Questions