An Elusive Table with chosen data from multiple worksheets

[BAPortes]

Have a workbook with 70 different worksheets, all the worksheets are the same "Form", all these forms have a "comments" section (cells A35:G36), and a cell F32 which if an X is placed in it, indicates a failed inspection. Trying to add a table to the main page in essence a summary of failed inspections, where if the F32 cell has an X in it on any of the 70 worksheets, then the name of the sheet and the cells that contain the comments (A35:G36) get copied into that table.

I didn't create this Workbook, all the worksheets have names that contain spaces which drives me crazy in having tried to come up with a way of referencing the different worksheets.

is there a way to create a loop of sorts that queries all the worksheets and extracts the worksheet name and comments from those forms with failed inspections (F32 has the dreaded X in them) that may solve my problem?

 

[Fluff]

Hi & welcome to MrExcel
This will create a new sheet called main & copy the data to that

Sub CopyData()

    Dim Ws As Worksheet
    Dim MainSht As Worksheet
    
    Worksheets.Add before:=Sheets(1)
    ActiveSheet.Name = "Main"
    Set MainSht = ActiveSheet
    
    For Each Ws In Worksheets
        If Ws.Range("F32").Value = "X" Then
            With MainSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Resize(2).Value = Ws.Name
                .Offset(, 1).Resize(2, 7).Value = Ws.Range("A35:G36").Value
            End With
        End If
    Next Ws

End Sub

 

[BAPortes]

Will definitely give it a try and keep you posted....thanks so much.

 

[BAPortes]

Alright Fluff...thanks for the code...ran it but getting an "Invalid Outside Procedure" compile error on the following line: Worksheets.Add before:=Sheets(1), the one is highlighted in the line.

Any ideas?

 

[Fluff]

Does the code begin with

Sub CopyData()

 

[BAPortes]

Does the code begin with

Sub CopyData()

No...will add that

 

[Fluff]

It needs to
Delete the code you have & then copy the entire code from post#2 & try it.

 

[BAPortes]

Can't thank you enough, that fixed the compiling error...last thing if I may impose, when the data gets copied to the page created, it creates two entries for each instance found...I was looking at the code, but I'm not savvy enough to understand by looking at it, how is it even possible. But again, truly help you for all the help provided.

 

[Fluff]

Because you need A35:G36 copying over (ie 2 rows), I added the sheet name in column A for both rows.
Is this what you mean by copying everything twice?