Problem with For Loop: Trying to get data from multiple worksheets with certain name format dropped in table rows on a separate sheet

[AmandaRae]

I have created the code below to grab specific data from multiple sheets within the workbook, and placing them in rows on a summary sheet. It either populates nothing or populates every row over and over again. It needs to occur automatically. <code>Sub Summary() Dim WkSht as Worksheet Dim Row as Integer 'A15 - A29: Pull over (A1) if > 0 'B15 - B29: Pull over (C1) if > 0 'C15 - C29: Pull over (D7) if > 0 For Each WkSht In ThisWorkbook.Sheets For Row = 15 To 29 If WkSht.Name Like "Tr. [0-9]*" Then If WkSht.Range("D7").Value > 0 Then If IsEmpty(Row) Then Cells(Row, 1).Value = WkSht.Range("A1").Value Cells(Row, 2).Value = WkSht.Range("C1").Value Cells(Row, 3).Value = WkSht.Range("D7").Value End If End If End If Next Row Next WkShtEnd Sub</code></pre>

<tbody>
</tbody>

 

[dmt32]

Hi welcome to the board.
not sure I have fully understood your code but see if these changes are going in right direction. If not, post back I or someone else here should be able to assist you.


I have assumed you summary worksheet is named "Summary" but change as required where show in red

Sub Summary()
    Dim WkSht As Worksheet
    Dim wsSummary As Worksheet
    Dim LastRow As Long
    
    Set wsSummary = Worksheets("Summary")
    For Each WkSht In ThisWorkbook.Sheets
        If WkSht.Name Like "Tr. [0-9]*" Then
            If WkSht.Range("D7").Value > 0 Then
                With wsSummary
                    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                    .Cells(LastRow, 1).Value = WkSht.Range("A1").Value
                    .Cells(LastRow, 2).Value = WkSht.Range("C1").Value
                    .Cells(LastRow, 3).Value = WkSht.Range("D7").Value
                End With
            End If
        End If
    Next WkSht
End Sub

Hope Helpful

Dave