PDA

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