find value in multiple sheets, copy offset and paste to summary sheet

Pholic,

Welcome to the forum! Give the following a try:

Sub tgr()
    
    Static wsLFB As Worksheet: Set wsLFB = Sheets("LFBsummary")
    
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsLFB.Name Then
            With Intersect(ws.UsedRange, ws.Columns("G"))
                .AutoFilter 1, "LFB"
                .Offset(1, 2).Copy wsLFB.Cells(Rows.Count, "H").End(xlUp).Offset(1)
                .AutoFilter
            End With
        End If
    Next ws
    
    wsLFB.Select
    Application.ScreenUpdating = True
    
End Sub

Pholic,

Can you post a sample workbook?

unfortunately my boss won't let my post a scrubbed example of our data, so i can't help you there... on the other hand i can post the working code that and that may help point you in some direction... whether or not we're moving forward or side to side is your call, haha.

Option Explicit

Sub CreateSummary()
Dim wsSum As Worksheet
Dim ws As Worksheet
Dim NR As Long

Set wsSum = Sheets("LFBsummary")
wsSum.Cells.ClearContents
NR = 1
Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name <> "LFBsummary" Then
        With ws
            wsSum.Cells(NR, "B").Value = .[G13].Value
            wsSum.Cells(NR, "C").Value = .[C8].Value
            wsSum.Cells(NR, "D").Value = .[C4].Value
            wsSum.Cells(NR, "E").Value = .[I22].Value
            wsSum.Cells(NR, "G").Value = .[K22].Value
            
        End With
        NR = NR + 1
    End If
Next ws

Application.ScreenUpdating = True
End Sub

now this is a bit simpler considering it just copy/paste info in static cells to a summary page vs matching a value and copying an offset, but at this point even capturing the whole row with "LFB" in it would help me create a summary of the data i need much faster than manually going through 1000 worksheets in 20 workbooks... thanks again for all the help, i greatly appreciate it... nothing more frustrating than being so close to the correct code and not knowing enough scripting to polish it off. - nicholas

Nicholas,

The code you posted takes data from several cells on different rows. It also doesn't look for LFB in column G at all, let alone return corresponding values, so I'm not sure if this contradicts your original request. Additionally, what's the error you're getting? The "LFB" is a string value, not a variable, so its definitely not looking for a variable value. If items in column G have text that contains "LFB" instead of equaling "LFB" then you would want to change that to "*LFB*"

Without a sample workbook however, there's not much more I can do. Attached is an example workbook so you can see how the macro works when it runs successfully. Perhaps I've got the wrong idea of what you need.