Copying Rows from Worksheet to End of New Worksheet Excel VBA

Question

I am using vba in Excel to create a Macro. I have a for loop that cycles through all the rows and copies certain rows that match criteria to a new worksheet. Currently, the macro works, I have it copying the rows to the same row number, but this causes blanks rows to exists, which I get rid of. Is there a way to do this better by copying the row to after the last used row?

Here is my code:

' Set the lastRow variable
    lastRow = Worksheets("Original").Cells.Find("*", [A1], , , xlByRows, xlPrevious).ROW

    ' The Total is in column K and the Class is in Column C
    On Error Resume Next
    For i = lastRow To 1 Step -1
        ' Check the columns for Total and Class values
        If (Worksheets("Original").Cells(i, "K").Value2) <> 0 Then
            Worksheets("Original").Rows(i).Copy Worksheets("NEW WS").Cells(i, 1)
            'Worksheets("Original").Rows(i).Copy Worksheets("NEW WS").Cells(Worksheets("NEW WS").Rows.Count, col).End(xlUp).ROW
        ElseIf ((Worksheets("Original").Cells(i, "K").Value2) = 0) Then
            If (Worksheets("Original").Cells(i, "C").Value2) < 81 Or (Worksheets("Original").Cells(i, "C").Value2) > 99 Then
                Worksheets("Original").Rows(i).Copy Worksheets("NEW WS").Cells(i, 1)
                'Worksheets("Original").Rows(i).Copy Worksheets("NEW WS").Cells(Worksheets("NEW WS").Rows.Count, col).End(xlUp).ROW
            End If
        End If
    Next i
Worksheets("NEW WS").Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

The commented lines: Worksheets("Original").Rows(i).Copy Worksheets("NEW WS").Cells(Worksheets("NEW WS").Rows.Count, col).End(xlUp).ROW

Do not work. I am not sure why. Excel won't copy anything from the original worksheet to the new one at all when I try to use this code. How can I get it to work?

you need to find the last row of the new sheet, add 1 to it and then copy the data. To find last row see [this](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Siddharth Rout, Dec 18 '13 at 13:12

Siddharth Rout · Accepted Answer · 2013-12-18T13:34:45.580

Further to my comments, see this

UNTESTED

Option Explicit

Sub sample()
    Dim wsO As Worksheet, wsI As Worksheet
    Dim wsOLRow As Long, wsILRow As Long

    Set wsO = ThisWorkbook.Worksheets("NEW WS")
    Set wsI = ThisWorkbook.Worksheets("Original")

    With wsO
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            wsOLRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row + 1
        Else
            wsOLRow = 1
        End If
    End With

    With wsI
        wsILRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 1 To wsILRow
            If .Cells(i, "K").Value2 <> 0 Then
                .Rows(i).Copy wsO.Rows(wsOLRow)
                wsOLRow = wsOLRow + 1
            ElseIf .Cells(i, "K").Value2 = 0 Then
                If .Cells(i, "C").Value2 < 81 Or .Cells(i, "C").Value2 > 99 Then
                    .Rows(i).Copy wsO.Rows(wsOLRow)
                     wsOLRow = wsOLRow + 1
                End If
            End If

        Next i
    End With
End Sub

This does copy everything I want over, but it still contains blank rows. — RXC, Dec 18 '13 at 13:27

score 2 · Answer 2 · answered Dec 18 '13 at 13:16

2

Worksheets("NEW WS").Cells(Worksheets("NEW WS").Rows.Count, col).End(xlUp).ROW -- returns a number not a location to paste your copied row

Create a new variable for it -- newLastRow = Worksheets("NEW WS").Cells(Worksheets("NEW WS").Rows.Count, col).End(xlUp).ROW

Then when you want to copy paste:

Worksheets("Original").Rows(i).EntireRow.Copy Worksheets("NEW WS").Range("A" & CStr(newLastRow + 1)).EntireRow should work or maybe without the .EntireRow don't have a chance to test this sorry.

answered Dec 18 '13 at 13:16

Tylor Hess

639
5
15

The `.EntireRow` does not affect anything. It still copies the row over, but Excel copies everything to the first row. I assign the newLastRow variable every iteration, at the beginning of the loop. – RXC Dec 18 '13 at 13:33

Copying Rows from Worksheet to End of New Worksheet Excel VBA

2 Answers2