Results 1 to 8 of 8

Speeding up creating worksheets based upon items in pivot table

Threaded View

  1. #1
    Registered User
    Join Date
    08-17-2016
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    14

    Speeding up creating worksheets based upon items in pivot table

    Hi members,

    This is a repost, but this time with the attachment added (hopyfully)

    I have a workbook for which i want to create new worksheets for every item in the filter selection.
    Since i want to modify the created worksheets and only keep the values and not the pivot itself, i created a code which seems to work, but is quite slowly.

    Is there a way how i can speed up this code?

    Sub GenerateWS()
     
    Dim PT As PivotTable
    Dim PI As PivotItem
    Dim PI2 As PivotItem
     
    Application.ScreenUpdating = False
    
    '1)Worksheet name where PIVOT Table is located
    MyWs = "Pivot"
    '2)PIVOT table name/number, note by default the first one created is PivotTable1
    MyPIV = "PivotTable1"
    '3)Field Name that you want to use for breaking out by, i.e. the filter name
    MyField = "File name"
     
    Set PT = Worksheets(MyWs).PivotTables(MyPIV)
    With PT
     
    For Each PI In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
    PI.Visible = True
     
    For Each PI2 In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
    If Not PI2.Name = PI.Name Then PI2.Visible = False
    Next PI2
    Set NewWs = Worksheets.Add
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    NewWs.Name = PI
     
    
    'You will need to amend the range below to copy the correct amount of data for your file
    Worksheets(MyWs).Select
        Range("B4").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    
    'This pastes into cell A1 of the new sheet
    NewWs.Select
    
     Range("B5").Select
    'ActiveSheet.xlPasteValuesAndNumberFormats
        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveWindow.Zoom = 80
    
    Next PI
    End With
        Sheets("Sheet1").Activate
            Response = MsgBox("WS generated Successfully.", 64)
    End Sub
    I cannot seem to upload the file ....
    The problem is that the following code

    "For Each PI In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
    PI.Visible = True

    For Each PI2 In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
    If Not PI2.Name = PI.Name Then PI2.Visible = False
    Next PI2'"

    is comparing each item to each other (>500 options).

    What i actually want is that the code will unselect all items, except for each PI in worksheet but do not seem to get the code working.
    Goal is to unselect all items in pivot table and then only choose the unique PI and create a new worksheet for it. I want to continue doing this, untill all worksheets has been created
    Attached Files Attached Files
    Last edited by hdinkie; 09-23-2016 at 01:34 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Speed up creating worksheets for all filters in pivot table
    By hdinkie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2016, 08:01 AM
  2. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  3. Replies: 10
    Last Post: 01-08-2013, 04:03 PM
  4. Replies: 0
    Last Post: 05-28-2012, 02:43 AM
  5. Creating a pivot table from multiple worksheets in one workbook
    By kiran654 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2010, 07:25 PM
  6. Populate combo box based on Pivot Table Field items
    By Khaos176 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2009, 07:33 PM
  7. Replies: 1
    Last Post: 08-20-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1