0

I ran into a problem while trying to write a VBA code to copy the content of a column to another sheet in the same project. The problem is that instead of copying the entire columns' data it only copies the last row's data.

This is the VBA code I have. 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 'this will iterate from the 2nd row, as first one is the header

    Sheet1.Cells(i, 1).Copy 'copy value of first row i and column one
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheet1.Paste Destination:=Worksheets("Sheet4").Cells(erow, 1)
    'paste it on Sheet4's first empty row and column 1.

    Sheet1.Cells(i, 4).Copy 'copy value of first row i and column 4
    Sheet1.Paste Destination:=Worksheets("Sheet4").Cells(erow, 3)
    'paste it on Sheet4's first empty row and column 3
    
    Sheet1.Cells(i, 5).Copy 'copy value of first row i and column 5
    Sheet1.Paste Destination:=Worksheets("Sheet4").Cells(erow, 2)
    'paste it on Sheet4's first empty row and column 2
    
    Sheet1.Cells(i, 8).Copy 'copy value of first row i and column 8
    Sheet1.Paste Destination:=Worksheets("Sheet4").Cells(erow, 4)
    'paste it on Sheet4's first empty row and column 4

    Sheet1.Cells(i, 9).Copy 'copy value of first row i and column 9
    Sheet1.Paste Destination:=Worksheets("Sheet4").Cells(erow, 5)
    'paste it on Sheet4's first empty row and column 5
        

Next i 'go to the next row

'will implement this clean up commands after code works
    'Application.CutCopyMode = False
    'Sheet2.Columns.AutoFit
    'Range("A1").Select
    
End Sub
RicardoBB97
  • 11
  • 1
  • Not sure why you are attempting to copy/paste in a loop. Wouldn't it be better to just copy each column in question? – BigBen Nov 24 '20 at 21:05
  • Not sure if this is the most efficient way as I am just starting to learn VBA, but it is part of an assignment where I need to copy certain data from a sheet to a different sheet and run the command by clicking a button. Thanks :) @BigBen – RicardoBB97 Nov 24 '20 at 21:09
  • Then I would scrap the loop. – BigBen Nov 24 '20 at 21:09
  • If I recollect well the loop is what makes the code go 'down' one row correct. Is there any other way you think it would be more efficient? Or even better a way to copy all the data in each column, like just copy the column and paste the column? – RicardoBB97 Nov 24 '20 at 21:12
  • 1
    `Sheet1.Range("A2:A" & lastRow).Copy Sheet4.Cells(Sheet4.Rows.Count, 1).End(xlUp).Offset(1).` One red flag in your current code is that you're determining `erow` based on `Sheet2`, but then pasting to `Worksheets("Sheet4")`. If those are different sheets, then `erow` never changes as you loop. – BigBen Nov 24 '20 at 21:14
  • Makes much more sense! That way if I use `Sheet1.Range("A2:A" & lastRow).Copy Sheet4.Cells(Sheet4.Rows.Count, 1).End(xlUp).Offset(1)` then I can use `Sheet1.Paste Destination:=Worksheets("Sheets4").Cells(erow,1)` to just copy and paste the entire column starting from the first empty row correct? – RicardoBB97 Nov 24 '20 at 21:23
  • Well you need to determine `erow` based on `Sheet4` – BigBen Nov 24 '20 at 21:27
  • Yes, I made a mistake there since it was initially going to be paste into sheet2! Thanks very much, your idea is also much more efficient since it will not have to iterate through all the rows, simply copying columns! :) – RicardoBB97 Nov 24 '20 at 21:29
  • "but it is part of an assignment" Ask your teacher, or fellow students, for help! – alowflyingpig Nov 25 '20 at 06:05

0 Answers0