write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Saturday, 23 July 2011

Pivot table : on click create new workbook & save

with below code when you double click on any values in pivot table
the new workbook will be created & save in the same folder

copy below code in this workbook module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim nwk As String, sPath As String, sFileName As String
nwk = ActiveWorkbook.Name
Workbooks(nwk).ActiveSheet.Name = Cells(2, 1).Value & "_" & Cells(2, 3).Value
'Save the new workbook
sPath = ThisWorkbook.Path & "\"
sFileName = ActiveSheet.Name
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (sPath & sFileName)
ActiveWorkbook.Close True
End Sub

No comments:

Post a Comment