Hello I have a workbook, that has multiple sheets the sheets are called the following Supervisors 1 , Supervisors 2, Supervisors 3, Supervisors 4, Staff 1, Staff 2, Staff 3, Staff 4 and a sheet called Summary.
My issue is that when I try to look for Total Supervisors or Total Staff, I get a 'Error code 91 , variable not set'
I am still new to vba and people on here have been so supportive and helpful sO can I ask again for all your help and advice.
My issue is that when I try to look for Total Supervisors or Total Staff, I get a 'Error code 91 , variable not set'
I am still new to vba and people on here have been so supportive and helpful sO can I ask again for all your help and advice.
Code:
Private Sub Worksheet_Activate()Dim r As Range, rc As Range, rd As Range, rng As Range, rngC As Range, rndD As Range, snRow As Range, TmRow As Range, snRowC As Range
Dim x As Integer, xc As Integer, xd As Integer, ETRow As Long, LTRow As Long, NTRow As Long, ETRowC As Long, LTRowC As Long
Dim TMName As String, TMNameC As String
Application.ScreenUpdating = False
ETRow = 10: LTRow = 10: NTRow = 10
ETRowC = 10: LTRowC = 10
ActiveSheet.Range("C4:AG9").ClearContents
For x = 1 To Sheets.Count
For xc = 1 To Sheets.Count
If Sheets(x).Name <> "Summary" Then
TMName = Left(Sheets(x).Name, 6)
Set snRow = Sheets(Sheets(x).Name).Range("C:C").Find("Total Supervisors", LookIn:=xlValues, LookAt:=xlWhole)
Set rng = Sheets(Sheets(x).Name).Range("D5", "AH5")
For Each r In rng
If InStr(1, r.Value, "ET") > 0 Then
Sheets("Summary").Cells(4, r.Column - 1) = Sheets(Sheets(x).Name).Cells(snRow.Row, r.Column).Value
ETRow = ETRow + 1
ElseIf InStr(1, r.Value, "LT") > 0 Then
Sheets("Summary").Cells(5, r.Column - 1) = Sheets(Sheets(x).Name).Cells(snRow.Row, r.Column).Value
LTRow = LTRow + 1
ElseIf InStr(1, r.Value, "NT") > 0 Then
Sheets("Summary").Cells(6, r.Column - 1) = Sheets(Sheets(x).Name).Cells(snRow.Row, r.Column).Value
NTRow = NTRow + 1
ElseIf Sheets(xc).Name <> "Summary" Then
TMNameC = Left(Sheets(xc).Name, 6)
Set snRowC = Sheets(Sheets(xc).Name).Range("C:C").Find("Total Cleaners", LookIn:=xlValues, LookAt:=xlWhole)
Set rngC = Sheets(Sheets(xc).Name).Range("D5", "AH5")
For Each rc In rngC
If InStr(1, rc.Value, "ET") > 0 Then
Sheets("Summary").Cells(8, rc.Column - 1) = Sheets(Sheets(xc).Name).Cells(snRowC.Row, rc.Column).Value
ETRowC = ETRowC + 1
ElseIf InStr(1, rc.Value, "LT") > 0 Then
Sheets("Summary").Cells(9, rc.Column - 1) = Sheets(Sheets(xc).Name).Cells(snRowC.Row, rc.Column).Value
LTRowC = LTRowC + 1
End If
Next
End If
Next
End If
Next
Next
Application.ScreenUpdating = True
End Sub
