Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming gpu hard drive hardware hdmi internet laptop malware memory monitor motherboard music network printer problem ram registry router server slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Pivot table drill down issue

Reply  
Thread Tools
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
04-Aug-2009, 01: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 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 01:05 PM #2
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
05-Aug-2009, 07: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 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 07: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, 07: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 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 08: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 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
05-Aug-2009, 08: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, 236 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, 02: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 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
06-Aug-2009, 06: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, 03: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, 05:48 AM #11
I would guess so
bomb #21's Avatar
Distinguished Member with 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
08-Aug-2009, 07: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, 281 views)
ashleywanless's Avatar
Computer Specs
Junior Member with 22 posts.
 
Join Date: Jul 2009
Experience: Beginner
10-Aug-2009, 08: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 8,082 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
10-Aug-2009, 08: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, 02:09 PM #15
Hi Bomb,

Sorry had a mouse problem...code is perfect. Thanks for your help. Problem SOLVED!!!!
Reply

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.

Search Tech Support Guy

Find the solution to your
computer problem!




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



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 06:07 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.