VBA Open Cell Reference based on column search

0

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 need it to open it up as if i'm double clicking and creating a pivot table (tab example in workbook)

Here is code I'm using:

Sub OpenPolresTotal()

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
''''Part that errors out
Ptotal = Range("I" & Pfind.Row).showdetail = true
Else
    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
Next

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

Discussion

I saw your last post but never saw the error that you mentioned; you should go back to that question and edit it and put a section in bold "Update" and then paste in the error that you got so I can see it.
don (rep: 1705) May 29, '19 at 11:04 pm
I can do that. And done. Thank you.

https://www.teachexcel.com/talk/3565/search-column-name-with-vba-code-open-cell-reference
Sroncey21 (rep: 62) May 29, '19 at 11:41 pm
*UPDATE*

I've attached another workbook after I ran your updated code and it opened what i needed to open so thank you so much, that is so amazing. But it looks like it did create a new sheet (n) and name a blank tab "POLRES" 

What change do I need to make to ensure it names my opened pivot "Polres" instead of sheet(n) and that is the only tab that opens?
Sroncey21 (rep: 62) May 30, '19 at 6:08 pm
Hi Sroncey21
The objective of this site is to teach excel techniques rather than provide free coded answers. So I have been remiss in providing you with your initial solution. My fault for showing off. However in keeping with the objectives I will provide the following guide lines.
Once the macro has created the new worksheet you will need to find out what its name is and rename it. You could play around with the sheets collection having a wild guess that the new sheet will be the last. This you will need to rename (record a naming of a sheet to see what the code looks like.)
There is a "gotcher" here in that the code may only run once if you try to rename your sheet to an existing name.  You might like to check for your new name and delete it first.
k1w1sm (rep: 102) May 30, '19 at 10:34 pm
Figured out how to rename the sheet. I think it's difficult to work through someone else's code. I started from scratch and went with the basics. I didn't think I was asking for anything difficult but I definelty understand the need to work through our own problems.
Sroncey21 (rep: 62) Jun 1, '19 at 10:42 am
Add to Discussion

Answers

0
Selected Answer

try changing

Ptotal = Range("I" & Pfind.Row).showdetail = true

TO

Range("I" & Pfind.Row).showdetail = true

Also 

this code will need to be changed to rename the sheet created 

'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
Next

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

End Sub

Discuss

Answer the Question

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