"Next Without For" Error

S

srbaker

New Member
Joined
Jan 20, 2017
Messages
16
Hi I have this Macro I was given by you guys and i've made some tweaks to it to work with my stuff and I am now getting a "Next Without For" error and I don't know why

the Entire Macro is listed Below Also if you see any other errors in my macro please let me know

Sub reconsheets()
Dim Current As Worksheet
Dim startrow As Long
Dim mlastrow As Long
Dim slastrow As Long
Dim mastname As String

mlastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

'****CONFIG****
startrow = 2 'change to row AFTER header on all sub sheets
mastname = "AllEmployeesCombined" 'change to master sheet's exact name

For Each Current In Worksheets
If Current.Name <> mastname Then
slastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'sub sheet lastrow

For x = slastrow To startrow Step -1


If Division = York Then 'this is where we need to look at unique reference
Sheets(YorkDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(YorkDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(YorkDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(YorkDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(YorkDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(YorkDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(YorkDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(YorkDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Cumberland Then 'this is where we need to look at unique reference
Sheets(CumberlandDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Coastal Then 'this is where we need to look at unique reference
Sheets(CoastalDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(CoastalDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(CoastalDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(CoastalDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(CoastalDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(CoastalDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(CoastalDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(CoastalDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Learship / Senior Then 'this is where we need to look at unique reference
Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Learship / Senior Then 'this is where we need to look at unique reference
Sheets(SeniorTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(SeniorTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(SeniorTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(SeniorTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(SeniorTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(SeniorTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(SeniorTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(SeniorTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Learship Then 'this is where we need to look at unique reference
Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Sussman Then 'this is where we need to look at unique reference
Sheets(SussmanHouse).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(SussmanHouse).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(SussmanHouse).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(SussmanHouse).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(SussmanHouse).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(SussmanHouse).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(SussmanHouse).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(SussmanHouse).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End if

Next x

Next
MsgBox "Complete"
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
You only need the last "Next x", remove all the others.

You will most likely also need to wrap your if statement string conditions in quotes, like If Division = "York" Then
 
Upvote 0
A quick look and you have the line "Next x" several times, but the line "For x = ..." appears only once!
 
Upvote 0
I made your recommended changes and all I have now is the "Next x" at the end however now I am receiving an error stating Compile Error "Block If without End If" with End sub Highlighted when error pops up
 
Upvote 0
Also, these two if statements appear to do the same thing:

Code: 
If Division = Learship / Senior Then 'this is where we need to look at unique reference
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 End If

Code: 
If Division = Learship Then 'this is where we need to look at unique reference
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 End If

In which case, you could simplify as follows:

Code: 
If Division = "Learship"  OR Division = "Learship / Senior" Then 'this is where we need to look at unique reference
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 End If

Regards,

CJ<strike></strike>
 
Upvote 0
srbaker said:
I made your recommended changes and all I have now is the "Next x" at the end however now I am receiving an error stating Compile Error "Block If without End If" with End sub Highlighted when error pops up
Click to expand...

You need one more End If becase of this statement:

Code: 
If Current.Name <> mastname Then

Regards,

CJ
 
Upvote 0
I think you need to make that one IF statement. And get rid of all but the last End If. So start with an IF, and change the 2nd through last IF to ElseIf and and Else just before the End If:

Code: 
 Sub reconsheets()
 Dim Current As Worksheet
 Dim startrow As Long
 Dim mlastrow As Long
 Dim slastrow As Long
 Dim mastname As String

 mlastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

 '****CONFIG****
 startrow = 2 'change to row AFTER header on all sub sheets
 mastname = "AllEmployeesCombined" 'change to master sheet's exact name

 For Each Current In Worksheets
 If Current.Name <> mastname Then
 slastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'sub sheet lastrow

 For x = slastrow To startrow Step -1


 If Division = York Then 'this is where we need to look at unique reference
 Sheets(YorkDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(YorkDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(YorkDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(YorkDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(YorkDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(YorkDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(YorkDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(YorkDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 

 

 elseIf Division = Cumberland Then 'this is where we need to look at unique reference
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(CumberlandDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 

 

 elseIf Division = Coastal Then 'this is where we need to look at unique reference
 Sheets(CoastalDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(CoastalDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 

 

 elseIf Division = Learship / Senior Then 'this is where we need to look at unique reference
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 


 elseIf Division = Learship / Senior Then 'this is where we need to look at unique reference
 Sheets(SeniorTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(SeniorTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 

 elseIf Division = Learship Then 'this is where we need to look at unique reference
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 

 elseIf Division = Sussman Then 'this is where we need to look at unique reference
 Sheets(SussmanHouse).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
 Sheets(SussmanHouse).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
 mlastrow = mlastrow + 1
 else
End if

 Next x

 Next
 MsgBox "Complete"
 End Sub [\code]
 
Last edited:
Upvote 0
srbaker said:
Hi I have this Macro I was given by you guys and i've made some tweaks to it to work with my stuff and I am now getting a "Next Without For" error and I don't know why

the Entire Macro is listed Below Also if you see any other errors in my macro please let me know

Sub reconsheets()
Dim Current As Worksheet
Dim startrow As Long
Dim mlastrow As Long
Dim slastrow As Long
Dim mastname As String

mlastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

'****CONFIG****
startrow = 2 'change to row AFTER header on all sub sheets
mastname = "AllEmployeesCombined" 'change to master sheet's exact name

For Each Current In Worksheets
If Current.Name <> mastname Then
slastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'sub sheet lastrow

For x = slastrow To startrow Step -1


If Division = York Then 'this is where we need to look at unique reference
Sheets(YorkDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(YorkDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(YorkDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(YorkDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(YorkDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(YorkDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(YorkDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(YorkDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Cumberland Then 'this is where we need to look at unique reference
Sheets(CumberlandDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(CumberlandDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Coastal Then 'this is where we need to look at unique reference
Sheets(CoastalDivision).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(CoastalDivision).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(CoastalDivision).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(CoastalDivision).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(CoastalDivision).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(CoastalDivision).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(CoastalDivision).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(CoastalDivision).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Learship / Senior Then 'this is where we need to look at unique reference
Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Learship / Senior Then 'this is where we need to look at unique reference
Sheets(SeniorTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(SeniorTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(SeniorTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(SeniorTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(SeniorTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(SeniorTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(SeniorTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(SeniorTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Learship Then 'this is where we need to look at unique reference
Sheets(LeadershipTeam).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(LeadershipTeam).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End If

Next x

If Division = Sussman Then 'this is where we need to look at unique reference
Sheets(SussmanHouse).Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
Sheets(SussmanHouse).Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
Sheets(SussmanHouse).Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
Sheets(SussmanHouse).Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
Sheets(SussmanHouse).Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
Sheets(SussmanHouse).Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
Sheets(SussmanHouse).Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
Sheets(SussmanHouse).Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
mlastrow = mlastrow + 1
End if

Next x

Next
MsgBox "Complete"
End Sub
Click to expand...

I made all the changes you recommended but now I am receiving a Compile Error "Next without For" again Highlighting the Last Next in the Macro what does Next without for mean
 
Upvote 0
I was able to get the macro to work in terms of it is showing a "Complete" Message stating that it did what it was supposed to do but the problem is that it is not done it did not copy any of the information to the other cells that I wanted done did I screw something up
 
Upvote 0
You need to use code indentation so you can see the beginning and end of each block. Every "FOR" needs a "NEXT". Every "IF" needs an "ENDIF". Code indentation allows you to see the blocks at a glance.

I'm not sure where you declare "Division" but if you can make its value identical to the sheet name you can get this all done at once like this:

Code: 
Sub reconsheets()
Dim Current As Worksheet
Dim startrow As Long
Dim mlastrow As Long
Dim slastrow As Long
Dim mastname As String

    mlastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

'****CONFIG****
    startrow = 2 'change to row AFTER header on all sub sheets
    mastname = "AllEmployeesCombined" 'change to master sheet's exact name

    For Each Current In Worksheets
        If Current.Name <> mastname Then
            slastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'sub sheet lastrow

            For x = slastrow To startrow Step -1
                With Sheets(Division)
                    .Cells(mlastrow + 1, 1) = Current.Cells(PreferredName.Last, 1)
                    .Cells(mlastrow + 1, 2) = Current.Cells(PreferredName.First, 2)
                    .Cells(mlastrow + 1, 3) = Current.Cells(Description.Location, 3)
                    .Cells(mlastrow + 1, 4) = Current.Cells(PrimaryLocation, 4)
                    .Cells(mlastrow + 1, 5) = Current.Cells(EmployeeWorkEmailAddress, 5)
                    .Cells(mlastrow + 1, 6) = Current.Cells(Cell, 6)
                    .Cells(mlastrow + 1, 7) = Current.Cells(DID / EXT, 7)
                    .Cells(mlastrow + 1, 8) = Current.Cells(Division, 8)
                    mlastrow = mlastrow + 1
                End With
            Next x
        End If
    Next
MsgBox "Complete"
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Project plan does not take working days into account when adding offset
Replies
0
Views
94
KeBr
K
M
  • Solved
Find 6 digit PO using Xlookup in a string of POs in a cell.
Replies
5
Views
165
Peter_SSs
Peter_SSs
C
  • Question
ComboBox and TextBox
Replies
0
Views
160
Cr4ZyPP
C
A
  • Question
VBA run-time error
Replies
2
Views
275
Aaro23
A
I
  • Question
Copy column value but skip over to next column if empty
Replies
0
Views
312
inwonderland7
I

Forum statistics

Threads
1,214,921
Messages
6,122,280
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top