Search Column name with VBA code & Open Cell


Been trying to look up VBA code to search a specific column name "Process Total" in "column I" of tab name "AWD List" based on Column D name "POLRES"

Is there a way to find that "Process Total" row in Column I that corresponds to Column B name "POLRES" and then have it open that cell in "Column I" into a new sheet named "POLRES"

I've been working with part of the code, but cannot find anything that shows how to find and open the cell I need.

Sub Find_POLRES_ProcessTotal

.Find("Process Total", After:=Range("I:I"), MatchCase:=True)


.Find("POLRES", After:=Range("B:B"), MatchCase:=True)

End Sub




I atttached my worksheet updated after running the VBA code. It appears to be finding POLRES, naming the tab but it appears to just be entering what the total number is in the tab. 

Is there a way to get the pivot table to open in this POLRES tab (just like if I was to double click that number)
Sroncey21 (rep: 62) May 21, '19 at 10:28 am
Sorry can't help there. I do not use Pivot tables. You may need to post a new question to get this last bit done.
k1w1sm (rep: 197) May 21, '19 at 4:46 pm
Add to Discussion



Here is some code that does stuff. 

Sub OpenPolresTotal()
' K1w1 21/5/19

Dim Pfind As Object
Dim Ptotal
Dim TotalSheets As Integer
Dim TestSheetNum As Integer
Dim TestSheetName As String
Dim NewSheet As String
Dim SheetFound As Boolean

With Sheets("AWD List").Columns("D")
  Set Pfind = .Find(What:="polres", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

End With

If Not Pfind Is Nothing Then
   Ptotal = Range("I" & Pfind.Row).Value
    MsgBox "not Found"
End If

'check to see if sheet exists
TotalSheets = Sheets.Count
SheetFound = False
For TestSheetNum = 1 To TotalSheets
    If Sheets(TestSheetNum).Name = "Polres" Then
        SheetFound = True
        Exit For
    End If

 If Not SheetFound Then
    Sheets.Add.Name = "Polres"
    Sheets("Polres").Cells(1, 1) = Ptotal
 End If
End Sub

Answer the Question

You must create an account to use the forum. Create an Account or Login