The project I'm working on will require navigation capabilities be embedded into daughter worksheets that are the result of running the following script on a pivot table (at end for clarity.) This script is adapted so that variations will run for each month for each of several possible search parameters (it's not elegant, but it works and allows me to attach it to buttons on summary work sheets.)
What I am asking for is help writing the code for the navigation portion to automatically insert two navigation buttons in a daughter spreadsheet. Ideally, I would like to navigate back to one of two sites (the summary sheet or the navigation sheet) and to delete the daughter spreadsheet in the process.
Thanks, Ben
Begin quoted script:
Sub January_CSR_NBVC_Detail()
'defines initial variables
Dim FoundCell As Variant
Dim Found As Range
Dim A As Variant
'Activates the pivot table sheet to run script
Sheets("eContracts Pivot Tables").Activate
'Defines search range
With Worksheets("eContracts Pivot Tables").Range("A3:A22")
'Search function with guiding constraints. Will stop on first value matching search parameter. If no value found
'will return an error and follow error handler string
Set Found = .Find(What:="SW: VENTURA", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Directs script to follow error string on no result
On Error GoTo Errhandler1
'Sets variable A as the address of the value
A = Found.Address
'Makes the address associated with A the range and selects it
Range(A).Select
'Moves over one cell from the cell associated with A
ActiveCell.Offset(0, 1).Select
'Generates the detail sheet associated with address A in a new worksheet.
'This is the same as double clicking on one of the cells in the pivot table
Selection.ShowDetail = True
'Names the new sheet
ActiveSheet.Name = "Ventura January CSR Detail"
'Selects cell A1 of the new sheet as active
Range("A1").Select
Exit Sub
'String for handling no values returned
Errhandler1:
MsgBox "Search term not found."
'Wraps up the with and sub scripts
End With
End Sub