+ Reply to Thread
Results 1 to 4 of 4

Copy Filtered data from Pivot Table to another Worksheet

  1. #1
    Registered User
    Join Date
    05-03-2017
    Location
    Brisbane
    MS-Off Ver
    2013
    Posts
    3

    Copy Filtered data from Pivot Table to another Worksheet

    I have a pivot table I am trying to filter based on an array value and copy that filtered data to a worksheet that has the same name as the array filter then go back to the pivot table and select the next value in the array and copy that data into the worksheet of the same name. I can't get it to select the data to copy. Pivot table worksheet is called "Dept Transaction Listing" and the worksheets are called "BkHireDPC", "BusTech" and "BusMgtServices" respectively. This is only a sample of the overall database. The full database has approx. 24 different worksheets that make up the array. I get a 1004 runtime error on the VisibleItemsList code.

    My code so far is:

    Sub CopyPivotDatatoCCWSheets()

    ' *************************************************************************************************************
    ' Goes through each worksheet in the array and copies pivot table data for each cost centre to
    ' their respective worksheets
    ' Author:
    ' Date Created:
    ' Date Modified:
    ' *************************************************************************************************************

    Dim WshtNames As Variant
    Dim WshtNamebyCC As Variant
    Dim PvtTbl As PivotTable
    Set PvtTbl = Worksheets("Dept Transaction Listing").PivotTables("PivotTable1")

    'Turns screen updating off
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.ErrorCheckingOptions.BackgroundChecking = True

    'Copies pivot table section to worksheet
    WshtNames = Array("BkHireDPC", "BusTech", "BusMgtServices")

    For Each WshtNamebyCC In WshtNames

    With Worksheets(WshtNamebyCC)

    'select the PivotTable data you want to copy
    PvtTbl.PivotFields("WSValue").ClearAllFilters
    PvtTbl.PivotFields("WSValue").VisibleItemsList = Array("WshtNamebyCC")
    Selection.Copy

    'select the worksheet where you want the PivotTable to be pasted:
    Worksheets("WshNamebyCC").Select

    'paste the selected PivotTable to the selected worksheet:
    Worksheets("WshNamebyCC").Range("A5").End(xlDown).Offset(5, 0).PasteSpecial

    End With

    Sheets(WshtNamebyCC).Activate

    ' Loops to next worksheet
    Next WshtNamebyCC

    ' At end of loop returns to Dept Transaction Listing worksheet
    Worksheets("Dept Transaction Listing").Select
    Range("A1").Select

    ' Turns screen updating on
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.ErrorCheckingOptions.BackgroundChecking = False

    End Sub

    Where am I going wrong?

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy Filtered data from Pivot Table to another Worksheet

    Remove the quotes from around the variable name in the array.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    05-03-2017
    Location
    Brisbane
    MS-Off Ver
    2013
    Posts
    3

    Re: Copy Filtered data from Pivot Table to another Worksheet

    I still get the 1004 runtime error.

  4. #4
    Registered User
    Join Date
    05-03-2017
    Location
    Brisbane
    MS-Off Ver
    2013
    Posts
    3

    Re: Copy Filtered data from Pivot Table to another Worksheet

    Has anyone got any idea on why I am still getting a runtime error on this code?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Creating Pivot Table with Filtered Data
    By omagoodness in forum Excel General
    Replies: 2
    Last Post: 05-27-2016, 11:09 AM
  2. Pivot table counts filtered data
    By nagonar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-11-2015, 01:23 PM
  3. How to Automatically Update New Data within a Filtered Pivot Table?
    By templeoftyler in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-24-2013, 11:54 AM
  4. Adding and showing new data in a filtered Pivot Table
    By andreamazzai in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-10-2013, 07:20 PM
  5. Pivot tables: display filtered data in another worksheet
    By ridingbio in forum Excel General
    Replies: 5
    Last Post: 12-12-2011, 12:22 AM
  6. [SOLVED] Macro To Delete Filtered Pivot Table Data
    By Roger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2006, 12:00 PM
  7. [SOLVED] Pivot Table - display filtered data
    By Jeff M in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2005, 01: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