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

AmandaRae

New Member
Joined
Dec 14, 2013
Messages
1


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>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,045
Office Version
  1. 2019
Platform
  1. Windows
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
Rich (BB code):
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,978
Messages
5,767,424
Members
425,411
Latest member
sarmientojayexce03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top