create individual files from worksheets in a workbook

[jmurck]

I use a pivot table to create a summary report. From it, I drill down
on and save subset results into a separate worksheets. Each worksheet
is copied to its own new book. Final result - from one workbook file
with 30 sheets I produce 30 individual files. I am looking for an
automated way to do this rather than 1-drill down, 2-copy sheet to new
book, 3 - save new book, etc., one at a time X 30. Any suggestion is
appreciated.

 

[Bernie Deitrick]

Since any suggestion is appreciated <vbg> - "Don't do that!"

The beauty and power of pivot tables is that any time you want to
view, for example, November 2003 sales of widgets in the NorthEast,
you simply open the pivot table and drill down to that specific view
of the data. All 30 of your individual files are already there,
waiting.

All right - enough soapbox. If you want to continue using separate
files, then you can use a macro to do the 30 drill down/ saves: Change
the range to include all the cells you would normally drill down on.
You can also change the logic for the file names.

Sub DrillAndSave()
Dim myCell As Range
For Each myCell In Range("D534")
myCell.ShowDetail = True
ActiveSheet.Move
ActiveWorkbook.SaveAs "C:\Excel\Drilled\File" & myCell.Row &
".xls"
ActiveWorkbook.Close False
Next
End Sub

HTH,
Bernie
MS Excel MVP

 

[jmurck]

LOL - believe me, I wouldn't if I didn't have to. The 'whole' contain
confidential information for mulitiple customers, so the drill down i
the customer level - the only data they can see. I will definitel
give this macro a try - many thanks