Copy the Current active row from one worksheet to another

Question

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

ok, you select any row in sheet1 (say row №100). In what row in sheet2 you want to paste your data? in the next after last non empty row? — Dmitry Pavliv, Feb 27 '14 at 22:01
@simoco. I want the data to appear in the next blank row in sheet2 — user3331363, Feb 27 '14 at 22:04
I notice that you are shifting the data right one column -- is that what you want to do? — Wayne G. Dunn, Feb 27 '14 at 22:06
@simoco my current code only copies from one row in Sheet 1 :) — user3331363, Feb 27 '14 at 22:15
@user3331363, 1) does your command button placed in sheet1? and 2) `my current code only copies from one row in Sheet 1` - clarify what your code should do. As I understand it should copy single row (where activecell is placed) — Dmitry Pavliv, Feb 27 '14 at 22:15
@WayneG.Dunn Yes, so I have similar column headers in sheet 2. So I am copying them accordingly. Hope that answers your question. — user3331363, Feb 27 '14 at 22:16
@simoco 1)Command button is placed in Sheet1. So once update button is clicked my code currently copies the data in Cell A2,B2,C2,D2,E2 of Sheet1 and in the first blank cell of Sheet2. — user3331363, Feb 27 '14 at 22:22
@user3331363, ok, but what you need to copy? As I understood you select any cell in column Name and you need to copy **single row** whith selected name in sheet2? — Dmitry Pavliv, Feb 27 '14 at 22:27
@simoco Yes, So say i'm on Row 1000 of Sheet1 and I have just updated the E1000 cell (zipcode). Then I click Update button on sheet1. This should then copy the data(active row) from Cell A1000, B1000, C1000, D1000, E1000 from Sheet1 to the first blank row of sheet 2. Hope this is clear :) — user3331363, Feb 27 '14 at 22:35

score 3 · Accepted Answer · answered Feb 27 '14 at 22:36

3

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

answered Feb 27 '14 at 22:36

Dmitry Pavliv

35,333
13
79
80

1

WOW! this absolutely nails it. great one Simoco!! Could you please explain me the code if its ok. Thanks! – user3331363 Feb 27 '14 at 22:49
I noticed from your code that you starts finding last non empty row from row №4 : `If Worksheets("Sheet2").Range("B4").Offset(1, 0) <> "" Then`. That's why I'm using `lastrow = Application.Max(4,...)` - lastrow never would be less 4 (for more details how to find last row see this link: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920). Next part: `.Range("B" & lastrow)` returns you `.Range("B5")` (if lastrow equals to 5). and resize just stretch it to the right until range width would be 5: `.Range("B5").Resize(, 5)` gives you `.Range("B5:F5")` – Dmitry Pavliv Feb 27 '14 at 22:55
1

This is awesome! Guess I should ask you more :) – user3331363 Feb 27 '14 at 23:11
no problem;) If something is still unclear to you - fell free to ask:) – Dmitry Pavliv Feb 27 '14 at 23:14
1

@simoco congrats on 10K :) you're on fire :) – Feb 28 '14 at 12:34
1

@mehow, thanks:) It very pleasant to me to get your congratulations:) – Dmitry Pavliv Feb 28 '14 at 12:35
1

@simoco no problem, enjoy the ride :) – Feb 28 '14 at 12:39
1

@simoco Congratulation on 10k, Your the Best!! :) – user3331363 Feb 28 '14 at 18:43
@simoco Please help me with this. In case I wanted 2 more things here, how would the code changed. 1) Check if a column F is set to "Yes", only then copy the row to Sheet 2. 2) Don't allow duplicates to be copied in Sheet2. Please let me know if possible :) – user3331363 Mar 07 '14 at 17:09
Yes, it's possible, but to meet your requirements, code should be fully rewritten. Post please new question - and I'll try to help you:) – Dmitry Pavliv Mar 07 '14 at 17:12
1

@simoco Thanks a lot i'll open a new question!! :) – user3331363 Mar 07 '14 at 17:40

Copy the Current active row from one worksheet to another

1 Answers1