0

I'm rather new with VBA so please bear with me.

I'm trying to write a VBA code that helps me copy and paste data from multiple columns, into a single column on a different sheet.

My current code is able to copy data until the last filled row, and paste it after the last filled entry for each iteration. However, when a subsequent column is blank or has only 1 filled row, the code stops running completely. Can anyone help me figure out why this is the case please? 

Sub FirstVBA()
    Dim Criteria As Integer
    Criteria = Range("G1").Value

    If Criteria <> 0 Then
        Worksheets("Sheet2").Range("A2:A" & Rows.Count).Clear

        Worksheets("Sheet1").Range("B4", Range("B4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("C4", Range("C4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("D4", Range("D4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("E4", Range("E4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("F4", Range("F4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("G4", Range("G4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("H4", Range("H4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Worksheets("Sheet1").Range("I4", Range("I4").End(xlDown)).Copy
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    End If
End Sub
Teamothy
  • 2,000
  • 3
  • 16
  • 26
chewchewtrain
  • 1
  • 1
    The problem is caused by xlDown when you copy. See [this question](https://stackoverflow.com/questions/9404654/how-to-paste-the-selected-range-passed-as-rng-to-the-end-of-the-worksheet). – Dávid Laczkó Dec 21 '19 at 17:35
  • 1
    If you get the point from the link above, the question is really what to do instead of xlDown. As this has been asked by many people I'm sure, it would be a duplicate. I think you may remove this question. – Dávid Laczkó Dec 21 '19 at 17:47

0 Answers0