[ashleywanless]

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]

Drill-down Pivot Table Results in New File

[ashleywanless]

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]

"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]

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]

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.

[bomb #21]

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

[ashleywanless]

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]

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

[kaiser03]

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]

I would guess so

[bomb #21]

Doh!

Here's the one you really need.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
ActiveSheet.Move
End Sub

[ashleywanless]

Hi,

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

Thanks

[bomb #21]

Runs automatically when I double click on the pivot table. That was kind of the point.

[ashleywanless]

Hi Bomb,

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