+ Reply to Thread
Results 1 to 3 of 3

Add Columns to Pivot Table with a name like "ABC"

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2010
    Posts
    10

    Add Columns to Pivot Table with a name like "ABC"

    I have a pivot table that I create weekly to include the new week's data, as well as the rest of the weeks in the year. I'd like for the macro to pull all of the columns like "WE" into the pivot table so that it includes the current week. Below is what the code looks like now. Is there a way to tell the macro to pull columns based on "field name like" language? Thanks in advance for any help you can give me! I've been able to get this far, but it doesn't loop the headers to find all of the columns that have WE headers.

    Sub AddPTFields()

    Dim PT As PivotTable
    Dim PF As PivotField
    Dim FldName As String
    Dim LastCol As Integer
    Dim TxtStr As String
    Dim wsName As String

    'The sheet the Pivot Table is on is on Sheet1

    wsName = "Total Daily Volume Data"
    FldName = Sheets(wsName).Cells(1, x + 1).Value '''Not cycling through FldNames....Stays on 1st Field???
    Set PT = ActiveSheet.PivotTables("PivotTable2")
    Set PF = PT.PivotFields(FldName)
    LastCol = Sheets(wsName).Range("A1").SpecialCells(xlCellTypeLastCell).Column
    TxtStr = LCase(CStr(Cells(1, x + 1)))

    For x = 7 To LastCol 'Date fields that could contain *we begin at this column(7)
    If PF Like "we*" Then

    PT.AddDataField PF, "Sum of " & TxtStr, xlSum
    End If

    Next x

    End Sub
    Last edited by FaithH; 07-15-2014 at 12:05 PM.

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    41

    Re: Add Columns to Pivot Table with a name like "ABC"

    Faith,

    Try something like this:

    Please Login or Register  to view this content.
    Last edited by mrmmickle1; 07-16-2014 at 02:51 PM.
    Using Excel 2010, 2013 & 2016 | Windows 7 | 64 Bit

  3. #3
    Registered User
    Join Date
    12-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Add Columns to Pivot Table with a name like "ABC"

    Worked like a charm!!! Thank you mrmmickle1!

+ 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. Replies: 0
    Last Post: 10-21-2013, 05:06 PM
  2. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  3. Pivot table showed "All" instead of "Multiple items" when using datasource from CSV
    By dikchan@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2009, 07:26 AM
  4. Replies: 0
    Last Post: 02-01-2006, 03:35 AM
  5. Replies: 1
    Last Post: 03-10-2005, 01:07 AM

Tags for this Thread

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