Search Column name with VBA code & Open Cell Reference


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 VBA code that will open the "POLRES" "Process Total" cell reference and just enter the total number into the tab. 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
'Where my VBA code errors out and is highlighted
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





Hi there! Sorry I forgot to reply to your other message. I believe that this is what you are looking for:

Range("I33").ShowDetail = True

Find the cell you want, which it seems like you know how to do and then just use .ShowDetail = True and the new worksheet with all the data opens.

Let me know if this works!)




 I can't seem to get past this point. (Search for named column in "B" for "POLRES" and then to open what corresponds in Column "I" for Process Total. Search function is necessary because sometimes the report will push down row numbers so getting a specific lock on named column criteria to open specific cell reference would be so very helpful. 

I appreciate all your help on this as this will unlock so much more potential on accuracy and time. 

The error highlights this part below:

"Ptotal = Range("I" & Pfind.Row).showdetail = true"
Sroncey21 (rep: 62) May 27, '19 at 6:19 pm
Hi Sroncey
I have updated you latest post suggesting what I think is the problem. (It is unusual to have 2 = signs in a single line.)
Also as this createsa new worksheet all the following code will need to be updated.
I suggest you will need to check to see if the Polres sheet exists and delete if it does then rename the new sheet. 
k1w1sm (rep: 197) May 30, '19 at 6:00 pm
Add to Discussion

Answer the Question

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