July 20, 2015

Copy Column Data into Different Columns in Another Excel Worksheet

Posted on by Dinesh Kumar Takyar

I had published this video ‘Automate copying of column data from one worksheet to another using Excel VBA’ on YouTube https://youtu.be/1OtJeS0NWCo
After watching the training video Debi Putnam had the following question:
This is exactly what I need. However, when I run the macro, it is writing over the same row instead of moving down a row. Here is my code. Can you tell me what I’m doing wrong?

She was using the following code:

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, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Sheet2.Cells(erow, 7)
Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Sheet2.Cells(erow, 24)
Sheet1.Cells(i, 5).Copy
Sheet1.Paste Destination:=Sheet2.Cells(erow, 25)
Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select
End Sub

My original code was:

Sub copycolumns()

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

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

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

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

Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select

End Sub

Apart from the fact that I was using a condition to copy data from columns in Sheet1 to columns in Sheet2, my erow variable took reference to column 1 and also pasted the data from sheet1 into sheet2 starting from column 1:

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)

Debi was copying data from sheet1 and pasting into different columns and also not starting in column 1:

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Sheet2.Cells(erow, 7)

Now every time the ‘for next’ loop runs, it finds there is no data in column1 and the relevant row. It keeps on copying the data in the columns in row 2. Watch the training video to understand the solution to Debi’s problem:

3 thoughts on “Copy Column Data into Different Columns in Another Excel Worksheet”

  1. Hi, I want to copy data from one sheet to another, my variables are coded and sheet 1 is not having all the variables in source sheet and so I just want to copy and paste from the source sheet, variables that are in sheet 1 with the aim of updating it.Please help.

  2. Hi Sir, I want also an Automated copying of data from one worksheet to another using Excel VBA, but my scenario is little different. ex. I have a series of access numbers in sheet2 ex.(culumnB), and in sheet 1 is my data entry template. What I want to happen is when I assign an access number and fill his data in sheet1 the script will find the value of access number that I assigned into sheet 2 then paste/populate it’s data into the relevant row.

  3. Hi,
    I have tried your code over and over but it is not pasting any data into worksheet 2. Here is my code (which is the same as yours)

    Sub CopyColumns()

    Dim Lastrow As Long, Endrow As Long

    Lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To Lastrow
    Sheet1.Cells(i, 1).Copy
    Endrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(Endrow, 1)

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

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

    Next i

    Application.CutCopyMode = False
    Sheet2.Columns().AutoFit
    Range(“A1”).Select

    End Sub

    When stepping into the code I get “Set destination and press Enter or choose Paste” at the bottom of Sheet 1. It cycles through but never prints.

    I also tried the below code but it doesn’t loop all the way through each row (it stops at the last CA before a NY, but there is still one remaining CA).

    Public Sub CopyMe()

    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(1)
    Set wks1 = wkb.Sheets(2)
    endrows = False
    thisrow = 2
    While endrows = False
    If wks.Cells(thisrow, 6) = “CA” Then
    With wks
    .Cells(thisrow, 1).Copy Destination:=wks1.Cells(thisrow, 1)
    .Cells(thisrow, 3).Copy Destination:=wks1.Cells(thisrow, 2)
    .Cells(thisrow, 6).Copy Destination:=wks1.Cells(thisrow, 3)
    ‘ .Rows(thisrow).Copy Destination:=wks1.Rows(thisrow)
    thisrow = thisrow + 1
    End With
    Else
    endrows = True
    End If
    Wend

    End Sub

    My data looks like this:

    First Name Last name House No Street City State Zip
    Jacob Gibson C R Parl Apt. 101 San Francisco CA 94102
    Mason McGill GK II W12 San Francisco CA 94131
    William Gentry GK II E 49 San Francisco CA 94131
    Jayden Jones GK I M 98 San Francisco CA 94045
    Roger Baptist C R Parl Apt. 101 San Francisco CA 94102
    Michael Vincent Mission C 19 San Francisco CA 94116
    Ethan Hawke GK I R 121 San Francisco CA 94045
    Robin White Rogers Apt. 201 New York NY 24103
    Karin Victoria Stephens Apt. 3C New York NY 24124
    Evelyn Smith Royal Pettah House #20D Las Vegas NV 84356
    Ali Robinson Beach Candy Apt. 2B New York NY 27951
    Alexis Richards GK II Apt. 12P San Francisco CA 94131

    Sorry about the long message but wanted to give as much info as possible.

    I can’t figure out where I am going wrong. Any help would be greatly appreciated.
    Thanks

Comments are closed.