View Full Version : [SOLVED:] Next without For error
Old Bloke
08-12-2018, 09:42 AM
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.
Paul_Hossler
08-12-2018, 10:19 AM
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
Old Bloke
08-12-2018, 11:07 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.