Search sheets for cell containing a word, copy paste row(s) to sheet 1, copy sheet name

Can you attach an Excel file to help to prepare the macro.

duplicate data already pulled

It means that all values from column A to I are identical or is there only some cells to check ???
Do we need to check duplicate with data already recorded in sheet Attained

Here a first step

Please Login or Register  to view this content.

Some comments:
Code for duplicate will be added when duplicate question is clear
Rows are not pasted to sheet ""Attained" but inserted because you have formulas at the bottom that we have to care
BTW formulas ' range is curious, not starting from row 3
It would be more confortable to put the last row at the top: More flexibility

the data already recorded on sheet Attained does not duplicate. Is there a way to do that?

Yes of course, just give more details what is dupplicate ? is it when ALL cells are identical or does exist some key cells to rest

is this to support a chronological order?

I do not see what you mean ...this row is only a concern of formulas

There were a miss with the previous code when exist already some data in sheet Attained
If you shift up the formula row keep in mind to attach the new file

Please Login or Register  to view this content.

I can always clear the Attained sheet and re-pull the info

If you want it , it is possible to include the Cleaning in the macro

Option Explicit

Please Login or Register  to view this content.

Stil to point out the formula in the last row ... curious, the sum does not start at row 3

Good, you are welcome
Have a look on the star for rep...!

See here last adjustment following your last comment

Please Login or Register  to view this content.

and thank you for the rep

@jolivanes

Please Login or Register  to view this content.

Not sure because the macro do not paste only but insert also. We could expect that at the beginning there is always some empty rows before the last 2 one and these empty row are kept.
Insertion is also mendatory to keep the formulas updated while the formulas are not very clear
Note : It was suggested to shift up these 2 rows in the header which makes more sense and wil lead to a code
more simple
Thank you for your comment, I have the feeling not be alone
PCI

Originally Posted by jmemiller

By duplicate, what I mean […]

As a beginner starter :

PHP Code: 

Sub Demo1()
         Dim F&, Ws As Worksheet, L&, S$
             F = 3
             Application.ScreenUpdating = False
    With Sheet1
           .UsedRange.Offset(2).Clear
        For Each Ws In Worksheets
          If Not Ws Is Sheet1 And Ws.Visible < 0 Then
            With Ws.UsedRange.Rows
                With .Item("2:" & .Count)
                     .AutoFilter
                     .AutoFilter 10, "Attained"
                     .Offset(1).Copy Sheet1.Cells(F, 1)
                     .AutoFilter 10
                End With
            End With
               L = .Cells(1).End(xlDown).Row
            If L >= F Then
               .Range("J" & F & ":J" & L).Value = Ws.Name
                F = L + 1
            End If
          End If
        Next
        If F > 3 Then
                  S = "=SUM(R[" & 3 - F & "]C:R[-1]C)"
            With .Cells(F, 7).Resize(, 3)
                 .Offset(-1).Copy .Cells
                 .Interior.ColorIndex = 24
                 .FormulaR1C1 = Array(S, S, "=RC[-1]/RC[-2]")
            End With
        End If
    End With
             Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !