Help Needed in VBA

[JBird100]

I am new to VBA and this is the first Macro I've tried to write. I have an excel table which has five columns titled Address, location , works , action and completed. I want to create a new worksheet for each unique address and then copy the relevant rows for that address on that new worksheet. However, I only want to copy and paste the unique rows if the value in "Completed" is "N". The Value in completed can only be "Y" or "N". I keep getting the " Next Without For" error

Here is how my excel is structured:

Address	Location	Works	Action	Completed
1 Smith Street	ENTRANCE HALLWAY	Pipework patched near Front door	paint pipework	Y

Here is the code I've written:

Dim AddressField As Range
Dim AddressName As Range
Dim CompletedField As Range
Dim NewWSheet As Worksheet
Dim WSheet As Worksheet
Dim WSheetFound As Boolean
Dim DataWSheet As Worksheet

Set DataWSheet = Worksheets("Data")
Set AddressField = DataWSheet.Range("A4", DataWSheet.Range("A4").End(xlDown))
Set CompletedField = DataWSheet.Range("E4", DataWSheet.Range("E4").End(xlDown))


Application.ScreenUpdating = False

'Loop through each property name in column A


For Each AddressName In AddressField

'Check whether the current branch name corresponds with an existing sheet name

For Each WSheet In ThisWorkbook.Worksheets
If CompletedField = "No" Then
If WSheet.Name = AddressName Then
WSheetFound = True
Exit For ' if it does assign True to the WSheetFound variable and exit the For Each Next Loop
Else
WSheetFound = False ' if it doesn't assign False to the WSheetFound variable
End If
Next WSheet


If WSheetFound Then 'if WSheetFound = True

'copy and paste the record to the relevant worksheet, in the next available row
AddressName.Offset(0, 0).Resize(1, 5).Copy Destination:=Worksheets(AddressName.Value).Range("A3").End(xlDown).Offset(1, 0)

Else 'if WSheetFound = False

Set NewWSheet = Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' insert a new Worksheet
NewWSheet.Name = AddressName 'named after that branch

DataWSheet.Range("A3", DataWSheet.Range("A3").End(xlToRight)).Copy Destination:=NewWSheet.Range("A3") 'and copy the headings to it

AddressName.Offset(0, 0).Resize(1, 5).Copy Destination:=NewWSheet.Range("A4") ' then copy and paste the record to i

End If

Next AddressName

'autofit columns in each sheet in the workbook

For Each WSheet In ThisWorkbook.Worksheets

WSheet.UsedRange.Columns.AutoFit

Next WSheet

Application.ScreenUpdating = True

End Sub

 

[Regy]

I only had a quick look at your code but it looks like the problem is that you're missing an 'End If' statment in this code....

For Each WSheet In ThisWorkbook.Worksheets
If CompletedField = "No" Then
If WSheet.Name = AddressName Then
WSheetFound = True
Exit For ' if it does assign True to the WSheetFound variable and exit the For Each Next Loop
Else
WSheetFound = False ' if it doesn't assign False to the WSheetFound variable
End If
Next WSheet

FWIW.... When you get error messages like 'Next Without For' or 'Do Without Loop' the error may not be about the For or Do loops. Instead the error may be in or around the loop.