Next without For error

Hi everyone

I have a 42 sheet workbook and I need to collect data from 18 of them.
The 18 sheets have varying amounts of data that is updated separately.
The data is to be pasted onto a summary sheet - called "Day by Day (2)".
The code should loop through all the sheets looking for those that have "update" in cell A1.

The issue I have encountered is that my code generates a Next without For error.

I am attaching my code - if anyone can spot my error I would be grateful.

I can't compile or test it, but from a desk check it looks like you're missing a End With (marked <<<<<<<<<<<<<<<<<<<<<<<<<<<)

I find that good use of indenting makes looking at each logical block of code easier to see

BTW -- you can use the [#] icon to paste your macro between the CODE tags, so you don't need to attach a word document

Code:

---

Option Explicit
Sub NewUpdateDayByDay()
    'Sunday 13 August18
    'Define variables
    'SourceNewRow = SNR
    'Source will be target data sheet defined by "update" in A1
   
    Dim SNR As Long
    'DestinationNewRow = DNR
    'Destination will always be Day by Day (2)
   
    Dim DNR As Long
   
    Dim ws As Worksheet
   
    ' Stop calculation screen updating etc
    Call TurnEverythingOff
   
    'Clear existing data from Day by Day (2)
    'F19 is first cell below the headers
    'I is the last of the four columns to be cleared
    ' F19:I50000 can be changed to use last row up etc
   
    Sheets("Day by Day (2)").Select ' This probably not needed as button is only on that sheet
   
    'Clears previous data
    Range("F19:I50000").ClearContents
    Range("F19").Select
    'We only want to get data from some of the sheets - those with "update" in A1
   
    'Start For Next Loop
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("A1").Value = "update" Then Worksheet.Activate
       
        With ActiveSheet
       
            'Copy data
            SNR = Worksheets("ws").Cells(Worksheets("ws").Rows.Count, "P").End(xlUp).Row
            SNR = SNR + 1
            Range("P10:S" & SNR).Copy
   
            'Establish where to paste
           
            Sheets("Day by Day (2)").Select
            DNR = Worksheets("Day by Day (2)").Cells(Worksheets("Day by Day (2)").Rows.Count, "F").End(xlUp).Row
            DNR = DNR + 1
   
   
            'Paste to Day by Day (2)
            Sheets("Day by Day (2)").Select
            Range("F" & Rows.Count).End(xlUp).Offset(1).Select
            ActiveSheet.Paste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With    '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   
    Next ws
   
    Call SortDate
    Call FindFirstDateThatMatchesL1
    Call HideRows
    Call TurnEverythingOn
   
End Sub

---

Hi Paul

That's fixed it. As we say "I couldn't see it for looking". Very obvious really, just needed a fresh pair of eyes.
Now I need to understand why it is taking so long to do its stuff.

Thanks a lot for your help