Cycle Through Certain Worksheets and Find/Copy Particular Row To First Sheet in Workbook RRS feed

  • Question

  • Hello everyone.  I have written the macro below because I am trying to loop through specific worksheets and copy from each worksheet the entire row that has "Totals For: *" in the first cell of that row.  I want to copy this row to a sheet named "Data Total Validation UAT2".  Any ideas what is wrong with this code?  I am getting a run-time error at

    "If ws.Cells(i, 0) = "Totals For" Then"

    Thank you :D


    Sub WorksheetLoop()
        Dim ws As Worksheet
        Dim i As Integer
        Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Data Total Validation UAT2")
        For Each ws In Worksheets
            Select Case LCase(ws.Name)
                Case "Data Total Validation UAT2", "Overall Summary", "Detail Report", "Summary by State Category", "Summary By State"
                    'do nothing
                Case Else
                For i = 2 To ws.Range("A65536").End(xlUp).Row
                    If ws.Cells(i, 0) = "Totals For" Then
                        ws.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
                    End If
                Next i
            End Select
    End Sub


    • Edited by greenthnkr Thursday, July 2, 2015 5:24 PM
    Thursday, July 2, 2015 5:21 PM

All replies