none
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)
                    Else
                    End If
                Next i
                    
            End Select
        Next
    End Sub


    Robert


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

All replies