Stack Exchange
invalid qualifier error in VBA while renaming with search criteria
i am writing a code to rename the existing sheets as per search criteria.
eg. if sheet1 in A:A range contains name "Demo" then rename it to "NewDemo"
here is my code but i am getting invalid qualifier error.
Dim s As String
     Dim ws As Workbook

     s = ActiveWorkbook.Worksheets.Count                            'returns no of sheets in excel
     Set ftab = Range("A:A").Find("Summary Compensation Table")     'find key from 1st tab
     Set stab = Range("A:A").Find("Components of Pay")              'find key from 2st tab
     Set ttab = Range("A:A").Find("Target Bonus & Most Recent Pay") 'find key from 3st tab
     Set fotab = Range("A:A").Find("Cash & Equity Benchmarking")     'find key from 4st tab
     For Each ws In Worksheets
     s = ActiveWorkbook.Sheets(1).Activate
       If s.Contains(ftab) Then
       ActiveSheet.Name = "Summary Compensation Table"
     s = ActiveWorkbook.Sheets(2).Activate
       If s.Contains(stab) Then
       ActiveSheet.Name = "Components of Pay"
     s = ActiveWorkbook.Sheets(3).Activate
       If s.Contains(ttab) Then
       ActiveSheet.Name = "Target Bonus & Most Recent Pay"
     s = ActiveWorkbook.Sheets(4).Activate
       If s.Contains(fotab) Then
       ActiveSheet.Name = "Cash & Equity Benchmarking"

       Else
       MsgBox "Not Found"
       MsgBox ws.Name
     Next ws


Sub FindAndRenameSheets()

Dim ws As Worksheet 'changed from Workbook

For Each ws In Worksheets
    With ws
        If Not .Range("A:A").Find(what:="Summary Compensation Table", LookIn:=xlValues) Is Nothing Then
            .Name = "Summary Compensation Table"
        ElseIf Not .Range("A:A").Find("Components of Pay", LookIn:=xlValues) Is Nothing Then
            .Name = "Components of Pay"
        ElseIf Not .Range("A:A").Find("Target Bonus & Most Recent Pay") Is Nothing Then
            .Name = "Target Bonus & Most Recent Pay"
        ElseIf Not .Range("A:A").Find("Cash & Equity Benchmarking") Is Nothing Then
            .Name = "Cash & Equity Benchmarking"
        Else
            MsgBox "Not Found on sheet: " & .Name
        End If
    End With
Next ws

End Sub

