Excel VBA - Copying specific columns from one worksheet to another worksheet

Question

This is the code that I have, but something seems to be incorrect, Only the first row of data seems to pop up in the second worksheet. Can somebody help with it? Thanks in advance!!

Sub copycolumns()
    Dim lastrow As Long, erow As Long
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastrow
        Sheet1.Cells(i, 3).Copy
        erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)

        Sheet1.Cells(i, 4).Copy
        Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)

        Sheet1.Cells(i, 6).Copy
        Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 3)
    Next i

    Application.CutCopyMode = False
    Sheet2.Columns().AutoFit
End Sub

Hii!! editing my previous code.

I found a code more efficient than my previous code,

Sub CopyPastingColumns()

Dim erow As Long

Worksheets("Sheet1").Select

erow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count + 1

    Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range("D6").Select

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy Sheets("Sheet2").Cells(erow, 1)

erow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count + 1

    Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range("I6").Select

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy Sheets("Sheet2").Cells(erow, 2)

End Sub

How to concatenate two rows from sheet 1 and paste it in any column in Sheet 2?

For example, both the columns are numbers,

In Sheet1, column A has 123456, column B has 1

I want output on Sheet2 column C as 1234561

Please help, thanks!!

Although it is not the best practice, I'd suggest trying lastRow = Sheet1.UsedRange.Rows.Count — Rachcha
Hi! Is there a way to concatenate column 3 and column 4 from worksheet 1 and paste it in 5th column of worksheet 2? Both column 3 and column 4 are numerical values. Please help me out! — abishek sai

Tim Williams Tim Williams · Accepted Answer · 2018-06-21T05:54:24

Sub copycolumns()
    Dim lastrow As Long, erow As Long
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1

    For i = 2 To lastrow
        Sheet1.Cells(i, 3).Resize(1, 2).Copy Sheet2.Cells(erow, 1)
        Sheet1.Cells(i, 6).Copy Sheet2.Cells(erow, 3)
        'Edit: added line below
        Sheet2.Cells(erow, 5).Value = Sheet1.Cells(i, 3).Value & ", " & _
                                      Sheet1.Cells(i, 4).Value
        erow = erow + 1
    Next i

    Application.CutCopyMode = False
    Sheet2.Columns().AutoFit
End Sub

Excel VBA - Copying specific columns from one worksheet to another worksheet

End Sub

1 Answers