CODEWITHSUNDEEP
vbaexcelexcel-2010
Robert Okon
Robert Okon

Reputation: 35

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

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

Upvotes: 0

Views: 884

Answers (2)

Robert Okon
Robert Okon

Reputation: 35

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

Upvotes: 0

WGS
WGS

Reputation: 14179

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. :)

Upvotes: 2

Related Questions