I've spent the time to learn through the code used to Search Rows by name "POLRES" with a static column (I") was used as my example. And then to rename the sheet.
As you expand it becomes more difficult. In the last example, The Column address with the text "Process Total" can change from Column "I" to another column if another title pushes it over to say "J" - Having a static column "I" will cause future issues i've found.
Is there a way to search both Column & Row and show detail on that specific location. In this example, I'm trying to (.showdetail) on "Grand Total" in column (C:D) and then also Column "M" but since this location can change if new data is introduced, is there a way to search both "Grand Total" and "Question" and then open a pivot with (.showdetail) - I've attempted to follow all the steps required but I'm not sure I can do it separately like I'm attempting. - I've bolded my problem area with any changes I made)
Code_Goes_Here
Sub Open_OPEN_PHS_TOTAL()
Dim Pfind As Object
Dim Popen As Range
Dim TotalSheets As Integer
Dim TestSheetNum As Integer
Dim TestSheetName As String
Dim NewSheet As String
Dim SheetFound As Boolean
With Sheets("Master Report").Columns("C:D")
Set Pfind = .Find(What:="PHS 3.1 Total", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
Range("I" & Pfind.Row).ShowDetail = True
Else
MsgBox "Not Found"
End If
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "3.1 SLA"
Sheets("Master Report").Select
With Sheets("Master Report").Columns("C:D")
Set Pfind = .Find(What:="PHS 3.2 Total", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
Range("I" & Pfind.Row).ShowDetail = True
Else
MsgBox "Not Found"
End If
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "3.2 SLA"
Sheets("Master Report").Select
With Sheets("Master Report").Columns("C:D")
Set Pfind = .Find(What:="PHS 3.3 Total", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
Range("I" & Pfind.Row).ShowDetail = True
Else
MsgBox "Not Found"
End If
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "3.3 SLA"
Sheets("Master Report").Select
With Sheets("Master Report").Columns("E:AL")
Set Pfind = .Find(What:="Grand Total", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
With Sheets("Master Report").Columns("E:AL")
Set Popen = .Find(What:="QUESTION", LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
End With
If Not Pfind Is Nothing Then
'''''Problem Area
Range("Popen" & Pfind.Row).ShowDetail = True
Else
MsgBox "Not Found"
End If
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Question Que"
End Sub