Mourning the loss of our friend, WhitPhil.
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
 
Business Applications
Tag Cloud
access audio black screen blue screen boot bsod connection crash dell desktop driver drivers dvd email error excel firefox hard drive hardware hijackthis internet keyboard laptop malware monitor motherboard network networking outlook problem ram recovery router safe mode screen slow sound spyware trojan upgrade vba video virus vista vundo windows windows 7 windows vista windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Pivot table drill down issue

Tip: Click here to scan for System Errors and Optimize PC performance
[ Sponsored Link ]

Closed Thread
 
Thread Tools
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
04-Aug-2009, 12:47 PM #1
Pivot table drill down issue
Hi,

I have created a dashboard in excel and am wondering if the following is possible? If i double click on a pivot table cell which opens a new worksheet to show the drilldown details can VB code ensure this opens in a new workbook rather than the current?

Thanks

Ashley
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 12:05 PM #2
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
05-Aug-2009, 06:05 PM #3
Hi bomb thanks for your reply,

I read through the post but it doesnt solve my problem. I have a dashboard and i cant really have the tab which the pivot table drilldown goes to being deleted because as a finished sheet tabs are hidden, so any sheet need to include a button with macro attached to take it back to the main dashboard. If i constantly delete this tab unless i write more code to include a button on the new sheet it wont work. Can this be done?

Do you know of any way to just have the drilldown data in a new workbook? i think this would be the easiest solution.

Thanks
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 06:36 PM #4
"because as a finished sheet tabs are hidden, so any sheet need to include a button with macro attached to take it back to the main dashboard."

No offence intended but the grammar's kind of mangled there. Can you "translate" it?
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
05-Aug-2009, 06:53 PM #5
ok basically i have a dashboard with links to individual sheets which have pivot charts and tables. When finished all tabs are not viewable so the sheets have a text box linked to a macro which when clicked returns to the dashboard. Because there are so many sheets i dont really want all of the tabs to appear at the bottom so using the options property i dont display the tabs. Does this make a little more sense?

All i require is that whenever a cell of a pivot table is clicked the sheet opens in a new workbook not the current one. If i use the above option the new sheet created wont have the text box to divert the user back to the main dashboard when they have finished. Let me know if you need more clarification or screenshots.

Thanks for your time
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 07:06 PM #6
The short answer to "is there a way?" is "dunno".

Maybe it's possible to utilise the Workbook_SheetActivate event, assuming that fires when a sheet is added. You might be able to have that fire some code to check that the (activated) sheet name (the one added by the drilldown doubleclick) isn't one of the "static" sheets then cull it to a new wb. Let me check.
__________________
"Love All The People."
Bill Hicks, 1961 - 1994 -- R.I.P.
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 07:15 PM #7
K, try this.

The attached has 1 sheet "Master". Doubleclicking E3/4/5 will give you a new sheet which will then be copied to a new wb & deleted from the original.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name <> "Master" Then
Application.ScreenUpdating = False
x = ActiveSheet.Name
ActiveSheet.Copy
Application.DisplayAlerts = False
Workbooks("Dashboard.xls").Worksheets(x).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub
Attached Files
File Type: xls dashboard.xls (20.0 KB, 40 views)
__________________
"Love All The People."
Bill Hicks, 1961 - 1994 -- R.I.P.
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
06-Aug-2009, 01:34 PM #8
Sounds cool to me...ill check this and get back to you might be a few days thou. thanks for this i appreciate it..and the lesson in excel lanuage!
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
06-Aug-2009, 05:29 PM #9
No problem.

A little more info. You already know that when you drill down, the new sheet name will be Sheetn.

Assuming that your "static" sheets have "custom" names, you could use:

If InStr(ActiveSheet.Name, "Sheet") = 0 Then Exit Sub

at the start of the code. In simple terms, that's just "if the string Sheet isn't found in the name of the activated sheet, terminate the procedure".

That might be the simplest way to supress Workbook_SheetActivate when switching between the dashboard and the other "static" sheets. So in full, that would be:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If InStr(ActiveSheet.Name, "Sheet") = 0 Then Exit Sub
Application.ScreenUpdating = False
x = ActiveSheet.Name
ActiveSheet.Copy
Application.DisplayAlerts = False
Workbooks("Dashboard.xls").Worksheets(x).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


to replace what you already have. HTH
__________________
"Love All The People."
Bill Hicks, 1961 - 1994 -- R.I.P.
kaiser03's Avatar
Member with 30 posts.
 
Join Date: Aug 2009
08-Aug-2009, 02:11 AM #10
If i constantly delete this tab unless i write more code to include a button on the new sheet it wont work. Can this be done?
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
08-Aug-2009, 04:48 AM #11
I would guess so
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
08-Aug-2009, 06:18 AM #12
Doh!

Here's the one you really need.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
ActiveSheet.Move
End Sub
Attached Files
File Type: xls dashboard.xls (23.0 KB, 43 views)
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
10-Aug-2009, 07:39 AM #13
Hi,

Is there any chance this code can run automatically when the user double clicks into a pivot table?

Thanks
bomb #21's Avatar
Distinguished Member with 7,166 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
10-Aug-2009, 07:56 AM #14
Runs automatically when I double click on the pivot table. That was kind of the point.
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
10-Aug-2009, 01:09 PM #15
Hi Bomb,

Sorry had a mouse problem...code is perfect. Thanks for your help. Problem SOLVED!!!!
Closed Thread Bookmark and Share

Tags
excel, microsoft, vba

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Smart Search

Find your solution!



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.

Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -5. The time now is 01:16 AM.
Copyright © 1996 - 2009 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2009, Jelsoft Enterprises Ltd.
Powered by Cermak Technologies, Inc.