Creating Summary Tab based on information in other tabs

[SkyeS]

So I am looking to make a macro to do the following:

• Find the number of tabs with "Test" in the first cell (A1)
• For each tab:
  • Copy sheet name and paste into Sheet named "Main"
  • Copy row 6 from sheet and paste into Sheet named "Main"
  • Continue until all sheets with the "test" in the first cell are completed

Any Help is welcome!

 

[Fluff]

How about

Sub Copytest()

   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If InStr(1, Ws.Range("A1").Value, "test", vbTextCompare) > 0 Then
         With Sheets("Main").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Value = Ws.Name
            Ws.Rows(6).Copy .Offset(1)
         End With
      End If
   Next Ws
End Sub

 

[SkyeS]

This worked perfectly!! Thank you so much for your help! I was really struggling to figure out an efficient way to do this.

Once again thank you !

 

[Fluff]

Glad to help & thanks for the feedback