Pivot Details into New WorkBook

Hello,

Just got my first problem solved and another one arises. (Thanks again to DonkeyOte)

So with the code below I can now get pivot details with a mouse click and simultenously it deletes "unwanted" columns. This works perfectly.

Now my question is : can I get pivot details open into new workbook rather than ?

If this is possible, then the second question is as follows :

This is not mandatory, but would be really cool.
Is there a way to give the new workbook a name ? ideally it would be Waste Proposal + Current datetime.

Sub Hylkyehdotus()
'
' Hylkyehdotus Macro
'

    Dim rngArea As Range
    With Sheets("TOY Mill Stocks").PivotTables("PivotTable2").TableRange1
        .Cells(.Cells.Count).ShowDetail = True
        For Each rngArea In Range("B:B,E:G,J:J,L:AZ,BB:BG").Areas
            rngArea.Delete
        Next rngArea
    End With
          
End Sub

AFAIK you can't get the drill through to open in a new workbook automatically... you can however use code however to move the sheet to a new book as part of the routine:

Sub Hylkyehdotus()
    Dim rngArea As Range
    With Sheets("TOY Mill Stocks").PivotTables("PivotTable2").TableRange1
        .Cells(.Cells.Count).ShowDetail = True
        ActiveSheet.Move
        For Each rngArea In Range("B:B,E:G,J:J,L:AZ,BB:BG").Areas
            rngArea.Delete
        Next rngArea
    End With          
End Sub

(point being when the drilldown is activated the detail sheet becomes the active sheet)

In terms of file name - yes that's possible but where do you wish to save it ?

Hey,

Thanks for the reply. Don't have the file with me now, I'm not at the office anymore. I will test first thing tomorrow morning.

About that file name and save location, that's yet to be decided where this file will be saved, but it will be a network drive.
If that is possible to save on a network drive will it make any difference if we refer it to with UNC-path or just a letter mapped to the drive.
So it will start either with Y:\ or with UNC path \\servername\and so on...

I will revert tomorrow,

Jyri

UNC is always best given it's possible (if not desired) for different clients to use different mappings.

In reality - untested:

Sub Hylkyehdotus()
    Dim rngArea As Range
    Dim strPath As String
    Dim vData As Variant
    On Error GoTo Handler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    strPath = "\\servername\yhteiset\Wasteproposals.xls"
    With Sheets("TOY Mill Stocks").PivotTables("PivotTable2").TableRange1
        .Cells(.Cells.Count).ShowDetail = True
        For Each rngArea In Range("B:B,E:G,J:J,L:AZ,BB:BG").Areas
            rngArea.Delete
        Next rngArea
        vData = ActiveSheet.UsedRange
        Workbooks.Open strPath
        With Sheets("Wasteproposals")
            With .Cells(.Rows.Count, "A").End(xlUp).Offset(2)
                .Resize(UBound(vData, 1), UBound(vData, 2)) = vData
                With .Offset(2, 8).Resize(3)
                    .Value = Application.Transpose(Array("Timestamp:", "Username", "Computer Name"))
                    .Offset(, 2).Value = Application.Transpose(Array(Now, Environ("username"), Environ("computername")))
                End With
            End With
        End With
        ActiveWorkbook.Close True
        ActiveSheet.Delete
    End With
ExitPoint:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    Exit Sub
    
Handler:
    MsgBox "Error Has Occurred" & vbLf & vbLf & _
        "Error Number: " & Err.Number & vbLf & vbLf & _
        "Error Desc.: " & Err.Description, _
        vbCritical, _
        "Fatal Error"
    Resume ExitPoint
End Sub