Consulting

Results 1 to 3 of 3

Thread: Next without For error

  1. #1
    VBAX Regular
    Joined
    Dec 2017
    Location
    UK Midlands
    Posts
    11
    Location

    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.
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Dec 2017
    Location
    UK Midlands
    Posts
    11
    Location

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •