Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-11-2017, 08:53 AM
BenKal BenKal is offline embedding macros in daughter worksheet from showdetail executed on a pivot table Windows 10 embedding macros in daughter worksheet from showdetail executed on a pivot table Office 2016
Novice
embedding macros in daughter worksheet from showdetail executed on a pivot table
 
Join Date: Nov 2017
Posts: 2
BenKal is on a distinguished road
Default embedding macros in daughter worksheet from showdetail executed on a pivot table

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
Reply With Quote
  #2  
Old 11-13-2017, 03:45 PM
BenKal BenKal is offline embedding macros in daughter worksheet from showdetail executed on a pivot table Windows 10 embedding macros in daughter worksheet from showdetail executed on a pivot table Office 2016
Novice
embedding macros in daughter worksheet from showdetail executed on a pivot table
 
Join Date: Nov 2017
Posts: 2
BenKal is on a distinguished road
Default Thanks, got it

I figured it out. Thanks to anyone who took the time to think on this.
Reply With Quote
Reply

Tags
navigation, pivot table, vba code



Similar Threads
Thread Thread Starter Forum Replies Last Post
Two macros in one worksheet meowmeow Excel Programming 2 11-01-2017 06:19 AM
Changing Existing Pivot Table Source from a DB to a worksheet chrisdenslow Excel 3 07-24-2017 05:01 AM
Problems with protected worksheet and macros JohnGanymede Excel Programming 7 07-20-2017 04:55 PM
embedding macros in daughter worksheet from showdetail executed on a pivot table Create PIVOT chart using few rows in pivot table Santhosh_84 Excel 1 08-31-2015 06:22 AM
Copying pivot table and graph to a new worksheet artner0112 Excel 0 02-02-2013 07:19 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft