Excel 2010 VBA: Copy a row to a new sheet based on value

Question

As stated in the Title, I am trying to first check the value in a particular cell, then if it matches, copy the entire row into a new sheet. No Errors are thrown but the result is empty. Please assist.

Public Function freshSheet(inPart As String)
    Dim mag As Worksheet
    Dim currRow As Long
    Dim iohd As Worksheet
    Dim magCount As Integer

    Set iohd = ActiveWorkbook.Worksheets("IOHD")
    'TODO: Create Magic Sheet.
    Set mag =   ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
    mag.Name = "Magic"

    'TODO: Iterate through IOHD Sheet.
    For currRow = iohd.Rows.Count To 2 Step -1
        'TODO: IS PART EQUAL TO INPART? IF SO, COPY TO MAGIC SHEET
        If iohd.Cells(currRow, 2).Value = inPart Then
            magCount = mag.UsedRange.Rows.Count + 1
            iohd.Cells(currRow, 2).EntireRow.Copy Destination:=mag.Cells(magCount, 1)
        End If
    Next

End Function

Hello there, Robert. First, usage of `Active` in `Workbook`,`Sheet`, or `Cell` has more disadvantages than advantages. Second, to get the last row of a sheet, `.Cells(Rows.Count, 1).End(xlUp).Row` is the ideal method. Please see my modification of your code below. :) Also, declaring `magCount` as `Long` is much better in the long run. — WGS, Nov 26 '13 at 07:33
Why not use [AUTOFILTER](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s)? — Siddharth Rout, Nov 26 '13 at 08:17
Because the sheet (IOHD) is already being filtered. I would need to reset those filters every time I wanted to do this. I will look into your advice BK. Thanks. — Robert Okon, Nov 26 '13 at 22:15
may want to see this also as it covers the same method. [link](http://stackoverflow.com/questions/19815763/excel-vba-debug-loop-not-searching-through-the-whole-range) — Takedasama, Nov 26 '13 at 23:01

score 2 · Answer 1 · answered Nov 26 '13 at 07:31

Try this:

Public Function freshSheet(inPart As String)
    Dim mag As Worksheet
    Dim currRow As Long
    Dim iohd As Worksheet
    Dim magCount As Long
    Dim lRow As Long

    Set iohd = ThisWorkbook.Sheets("IOHD")
    'TODO: Create Magic Sheet.
    Set mag =   ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    mag.Name = "Magic"
    lRow = iohd.Cells(Rows.Count, 1).End(xlUp).Row

    For currRow = lRow To 2 Step -1
        If iohd.Cells(currRow, 2).Value = inPart Then
            magCount = mag.UsedRange.Rows.Count + 1
            iohd.Cells(currRow, 2).EntireRow.Copy Destination:=mag.Cells(magCount, 1)
        End If
    Next

End Function

Let us know if this helps. :)

Will try this out tomorrow. Out of the office today. Thank you for the input! — Robert Okon, Nov 26 '13 at 22:12

score 0 · Accepted Answer · answered Dec 03 '13 at 03:40

In the end, I tried the above method and found it was much easier to simply clear the filters and reapply them. Personally, I do not really like that idea because I find the idea of removing something only to add it again; however, in code its much simpler.

Credit goes to: Siddharth Rout

Excel 2010 VBA: Copy a row to a new sheet based on value

2 Answers2