Run Time Error 91

[JoeExcelHelp]

I keep getting this RTE 91

I tried inserting these lines and the error started

Code: Select all

With wsh.Range("A2:A5000")
                .FormulaR1C1 = "=If(C3="""","""",If(isnumber(search(""AO"",C4)),""AO"",""GO""))))"
                .Value = .Value
    End With

Full code

Code: Select all

Sub CombineAllData4_Headcount()
    Dim shM As Worksheet
    Dim Sht As Worksheet
    Dim wsh As Worksheet
    Dim r As Long
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Set shM = Worksheets("Data4")
    ' Clear the Data sheet
    shM.Range("2:" & shM.Rows.Count).ClearContents

    For Each Sht In ActiveWorkbook.Worksheets
        Select Case Sht.Name
            Case "Programs", "Schedule", "Hours", "Attrition", "DataDates", "HoursR36", "Transfers", "ActualSAP", "ActualSAPTable", "RecruitingOrientation", "HiringPlan", "HiringForecast", "Data", "Data2", "Data3", "Data4"
                ' Ignore these sheets
            Case Else
                r = shM.Range("C" & shM.Rows.Count).End(xlUp).Row + 1
                shM.Range("C" & r).Resize(8).Value = Sht.Name
            ' Copy range, then paste values
            
            Sht.Range("E52:E59,G52:CD59").Copy
                shM.Range("D" & r).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            
        End Select
    Next Sht
    With wsh.Range("A2:A5000")
                .FormulaR1C1 = "=If(C3="""","""",If(isnumber(search(""AO"",C4)),""AO"",""GO""))))"
                .Value = .Value
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

[JoeExcelHelp]

Nevermind forgot to set the sheet
Set wsh = Worksheets("Data4")

[HansV]

Glad you found it.