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
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