VBA: Worksheet loop only staying on the active sheet instead of looping through all specified worksheets in the workbook

Question

first-time poster for an issue that I can't seem to resolve on my own. I am running into the issue of VBA not looping through all the applicable sheets in the workbook for a portion of the code.

Background I have a workbook that may have anywhere from 1 to n worksheets to loop through. These workbooks are to be used for pricing/contracting and will be utilized by a third-party. The ws within the workbook are built similarly, like a formatted table with some drop-downs and some manual inputs-- essentially a standard template for the user to fill out. There is only 1 table/template per tab. For additional context, there is the option for the user to perform a lookup when/if needed to help fill out this template correctly by clicking a button. This lookup occurs on a separate hidden ws, "Lookup", and by pulling the user selections made in 2 columns, Range("D7:E28"). The resulting lookup results are dynamic and change per template (per ws) that is filled out.

Goal So that leads to a new ws, "Summary List", created with the idea that it would hold ALL of the code lookup results that exist in the workbook at its final stage ("finalized" into a read-only file with that summary ws available for viewing). It would store them as ranges one after the other with a border/horizontal line separating each ws's results and the specific ws name to help id each result.

My code attempts to:

Copy/= values in each ws

Range("A4") - contains the ws name that is sent to a "Summary List" ws
Range("D7:E28") - contains user input/selections (same column type across ws) that are sent to a hidden "Lookup" ws (where a table is located for a cross ref/match)

Copy/paste the ws's lookup results to the "Summary List" ws
Add border to separate new results
Make "Summary List" visible after looping is completed

**My Code: **

Dim ws As Worksheet
    'Dim rngNm As Range: Set rngNm = Range("A4")
    'Dim rngLU As Range: Set rngLU = Range("D7:E28")
    Dim lookupWS As Worksheet: Set lookupWS = Sheets("Lookup")
    Dim SummaryCodeWS As Worksheet: Set SummaryCodeWS = Sheets("Summary List")
    Dim lookupLR As Long: lookupLR = lookupWS.Range("K10000").End(xlUp).Row
    Dim summaryLR As Long: summaryLR = SummaryCodeWS.Range("B10000").End(xlUp).Row

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    For Each ws In ActiveWorkbook.Worksheets
        'With ws
            If ws.Visible = xlSheetVisible And ws.Name <> " " Then
  
                'summary ws obt ea. ws name
                SummaryCodeWS.Range("A" & summaryLR).Offset(2, 0).Value = [A4].Value  
                'Have also ran it as = rngNm (reads) / = ws.Range("A4").Value (doesn't read)
                         
                'Master list obt ea. term & code type for lookup
                lookupWS.Range("G3:H24").Value = [D7:E28].Value 
                'have also ran it as = rngLU (doesn't read) / = ws.Range("D7:E28").Value (doesn't read)
        
                'Copy/paste LU table into summary ws
                lookupWS.Range("K4:O" & lookupLR).Copy
                SummaryCodeWS.Range("B" & summaryLR).Offset(2, 0).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
                                    
                'Add border to ea. range
                With SummaryCodeWS.Range("A" & summaryLR, "F" & summaryLR).Offset(2, 0).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
                
                'MsgBox ws.Name  'Testing loop: currently working as it reads all sheets
    
            End If
        'End With
    Next ws
    
    'Make Code Summ vis (loc: end of wb)
    With SummaryCodeWS
        .Visible = xlSheetVisible
        .Move After:=Sheets(Sheets.Count)
    'End With
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

I think it's a formatting/referencing issue with the very beginning part after the If,Then that's causing the loop to not move on to the next sheet but I can't figure out exactly what it is at this point. The reason I say that is I can see the looping is happening when including MsgBox ws.Name.

I have reformatted in several ways which is why there's several commented out portions of my code. I've used the 'With ws" and the results are the same as below which is why I have taken it out.

I have also tried the worksheets.count method with similar results. I even tried using .Activate but it kept jumping to only the next ws and I want to avoid it if possible.

Also, I'm not getting any errors but I'm:

not getting any results (ws name and lookup results) in the "Summary List" ws AND/OR
just getting the results of one ws not ALL the applic. ones

I have seen recommendations like using .Activate but I would like to try to stay away from that if possible? I would appreciate any additional insight. If there is anything else I can provide, please let me know!

You have `SummaryCodeWS.Range("A" & summaryLR).Offset(2, 0).Value = rngNm` but the declaration for `rngNm` is commented out? — Tim Williams, Mar 24 '23 at 19:33
Sorry, I didn't put my code back to what was at least getting on to the summary page: SummaryCodeWS.Range("A" & summaryLR).Offset(2, 0).Value = [A4].Value — StillLearningThisStuff, Mar 24 '23 at 19:36
`summaryLR` is set outside the loop and you don't increment it inside the loop. `[A4]` for example has no worksheet qualifier, so I would not use that notation but always use a qualified `Range()` or `Cells()` call. — Tim Williams, Mar 24 '23 at 19:41
@TimWilliams that resolved it! I greatly appreciate your time and feedback! — StillLearningThisStuff, Mar 24 '23 at 19:52

score 1 · Accepted Answer · answered Mar 24 '23 at 19:51

Should be close...

Sub Tester()
    
    Dim ws As Worksheet, wb As Workbook
    Dim lookupWS As Worksheet, SummaryCodeWS As Worksheet
    Dim lookupLR As Long, summaryRow As Range, arr As Variant

    'Don't mix declarations with assignments on the same line
    '  it makes your code hard to read...
    Set wb = ActiveWorkbook 'ThisWorkbook?
    Set lookupWS = wb.Worksheets("Lookup")
    Set SummaryCodeWS = wb.Worksheets("Summary List")
    
    lookupLR = lookupWS.Range("K10000").End(xlUp).Row
    
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible And ws.Name <> SummaryCodeWS.Name Then
            
            Set summaryRow = SummaryCodeWS.Range("B10000").End(xlUp).Offset(2).EntireRow
            summaryRow.Columns("A").Value = ws.Range("A4").Value
            
            lookupWS.Range("G3:H24").Value = ws.Range("D7:E28").Value
            arr = lookupWS.Range("K4:O" & lookupLR).Value
            summaryRow.Columns("B").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    
            With summaryRow.Range("A1:F1").Borders(xlEdgeTop) 'Range() is *relative* here
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            
        End If
    Next ws
End Sub

VBA: Worksheet loop only staying on the active sheet instead of looping through all specified worksheets in the workbook

1 Answers1

Linked