Need VBA that scours multiple sheets, reviews all columns (after column A and the header row) and if all cells in a row are empty, return the value in

[TaylorEX]

Ok, so I have a blank sheet 1. Then several additional sheets (5-10) with data loaded to them. Every time the VBA is run it should move to sheet 2 and search through every single row with a value in column A (excluding the header row). Any row that doesn't have a value in any of the columns (excluding column a) copy the value of column A in that row to sheet 1, then go back to that sheet and continue looking at all the rows, when the next criteria is met it should go back to sheet 1 and paste to the next row. Once this is complete it needs to go the next sheet and repeat the process. Then on to the next sheet, etc until all sheets have been reviewed.

The other catch is for each new sheet it reviews it needs to skip a line and then put the sheet name as the next value so I can determine which values come from what sheet (if these can be bolded and highlighted as in the example that would be even better). I've made a very simplified version of what I'm looking for. The actual version has 100s of additional rows and several more columns. Please see the images below. Thanks to anybody who can help.

After the vba runs the Results sheet would look like this based on the Group 1 and Group 2 example sheets

 

[Peter_SSs]

I understand that you now have a working solution, but for my own benefit at least I would like to follow through to also make mine work so hoping you would give this one a try too.

There are a couple of differences between my code & Robert's code.

1. If a sheet contains data from row 4 down, but none of those rows need to be transferred to the results sheet (that is, all rows below row 4 that have data in column A also have data in at least one other column) then my code will still list that sheet name on the summary sheet (with no column A data below it) whereas Robert's code just moves to the next sheet. My code could easily be modified to act like Robert's if that is what you wanted.

2. Depending on how much data is on the supplementary sheets, you could find a noticeable difference in speed between the codes.

Sub CollectInfo()
  Dim i As Long, lr As Long
  Dim rCrit As Range
  Dim ws As Worksheet
  
  With Sheets(1)
    Set rCrit = .Range("J1:J2") '<- Edit if required
    For i = 2 To Sheets.Count
      Set ws = Sheets(i)
      lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
      If lr > 4 Then
        rCrit(2).Formula = Replace("=AND('#'!A5<>"""",COUNTA('#'!5:5)=1)", "#", ws.Name)
        ws.Range("A4", ws.Cells(lr, ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        ws.Range("A4:A" & lr).SpecialCells(xlVisible).Copy Destination:=.Range("A" & Rows.Count).End(xlUp).Offset(2)
        If ws.FilterMode Then ws.ShowAllData
        With .Range("A" & .Rows.Count).End(xlUp).CurrentRegion.Cells(1)
          .Value = ws.Name
          .Font.Bold = True
          .Interior.Color = 14994616
        End With
      End If
    Next i
    rCrit.ClearContents
  End With
End Sub