• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

Help Needed in VBA

JBird100

New Coder
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:

AddressLocationWorksActionCompleted
1 Smith StreetENTRANCE HALLWAYPipework patched near Front doorpaint pipeworkY

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

New Coder
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....

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.
 

Top