none
Pivot Table Filtered Data on Separate Worksheets RRS feed

  • Question

  • Excel created 50 different sheets from a filtered Pivot table using the "Show Report Filter pages..." under the Analyze tab.   I would like to use VBA to place these 50 sheets into new workbooks and naming them the same as the sheet name.   Help?

    Mike

    Wednesday, February 22, 2017 8:41 PM

Answers

  • Hi,

    You could use the following code to copy each sheet into a new workbook.

    Sub test()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim path As String
    For Each ws In ThisWorkbook.Worksheets
    path = "D:\test\" & ws.Name & ".xlsx"
    Set wb = Application.Workbooks.Add
    ws.Copy Before:=wb.Sheets(1)
    wb.SaveAs (path)
    wb.Close
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mzzfmly5 Monday, February 27, 2017 4:00 PM
    Thursday, February 23, 2017 3:00 AM
    Moderator

All replies

  • Hi,

    You could use the following code to copy each sheet into a new workbook.

    Sub test()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim path As String
    For Each ws In ThisWorkbook.Worksheets
    path = "D:\test\" & ws.Name & ".xlsx"
    Set wb = Application.Workbooks.Add
    ws.Copy Before:=wb.Sheets(1)
    wb.SaveAs (path)
    wb.Close
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mzzfmly5 Monday, February 27, 2017 4:00 PM
    Thursday, February 23, 2017 3:00 AM
    Moderator
  • Thank You!

    What code would I use as a copy/paste feature to change the Pivot tables to just Values & Source Formatting?   


    Mike

    Monday, February 27, 2017 4:24 PM
  • Hello Mike

    Since the original issue has been resolved, I suggest you post a new thread for this new question. Besides, I suggest you give detail explanation and show your expected result in your new thread.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 1, 2017 1:25 AM
    Moderator