none
How to select pivottables with specific names RRS feed

  • Question

  • Hi.

    I have two groups of pivottables in my worksheet. One group shows actual figures the other shows accumulated figures.

    Each month I want to add new datafields to the pivottables. The fields are either actual or accumulated.

    For the pivottables called *OPS I want to add the accumulated datafields. For all others I want to add the actuale fileds.

    I have tried to use a if-then-else, but something is wrong.

    Please advice.

        

    Sub AddValuesField()

    Dim pvt As PivotTable, ws As Worksheet
    Dim pf As String
    Dim pf_Name As String

    For Each ws In ActiveWorkbook.Worksheets
    For Each pvt In ws.PivotTables
        If PivotTables.Name = "pivottabel2" Then    'I would prefer something lile "if ..name like "pivottabelOPS* then"
            pf = "  ops 201507"
            pf_Name = "  ops 201507"
            pvt.AddDataField pvt.PivotFields("ops 201507"), pf_Name, xlSum
    Else
            pf = "  ult 201507"
            pf_Name = "  ult 201507"
            pvt.AddDataField pvt.PivotFields("ult 201507"), pf_Name, xlSum

        End If
    Next pvt
        Next ws

    Best regards

    Peter

    Monday, July 6, 2015 9:04 AM

Answers

  • Hi,

    >> 'I would prefer something lile "if ..name like "pivottabelOPS* 

    You may finish this by the following code.

    If str(pvt.Name,"pivottabelOPS")>0 then 

    or

    If Left(pvt.Name, 13)=pivottabelOPS

    By the way, looking for each tables name in the pivottables, you could use table index to get the name, not PivotTables.Name

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Sunday, July 19, 2015 7:02 AM
    Tuesday, July 7, 2015 3:20 AM
    Moderator
  • There is “like operator” in Visual Basic, so you can try like below:

    For Each ws In ActiveWorkbook.Worksheets
     For Each pvt In ws.PivotTables
         If pvt.Name Like "pivottabelOPS*" Then    'I would prefer something lile "if ..name like "pivottabelOPS* then"
             pf = "  ops 201507"
             pf_Name = "  ops 201507"
             pvt.AddDataField pvt.PivotFields("ops 201507"), pf_Name, xlSum
     Else
             pf = "  ult 201507"
             pf_Name = "  ult 201507"
             pvt.AddDataField pvt.PivotFields("ult 201507"), pf_Name, xlSum
       End If
     Next pvt
    Next ws
    

    For more information, click here to refer about Like Operator

    • Marked as answer by L.HlModerator Sunday, July 19, 2015 7:02 AM
    Tuesday, July 7, 2015 9:34 AM

All replies

  • Hello,

    You could use instr: https://msdn.microsoft.com/en-us/library/8460tsh1%28v=vs.90%29.aspx?f=255&MSPPError=-2147217396

    if instr(1,If PivotTables.Name , "pivottabelOPS*",vbTextCompare)>0 then ...

    Or left: https://msdn.microsoft.com/en-us/library/y050k1wb(v=vs.90).aspx

    Hope it helps,

    Wouter

    Monday, July 6, 2015 12:32 PM
  • Hi,

    >> 'I would prefer something lile "if ..name like "pivottabelOPS* 

    You may finish this by the following code.

    If str(pvt.Name,"pivottabelOPS")>0 then 

    or

    If Left(pvt.Name, 13)=pivottabelOPS

    By the way, looking for each tables name in the pivottables, you could use table index to get the name, not PivotTables.Name

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Sunday, July 19, 2015 7:02 AM
    Tuesday, July 7, 2015 3:20 AM
    Moderator
  • There is “like operator” in Visual Basic, so you can try like below:

    For Each ws In ActiveWorkbook.Worksheets
     For Each pvt In ws.PivotTables
         If pvt.Name Like "pivottabelOPS*" Then    'I would prefer something lile "if ..name like "pivottabelOPS* then"
             pf = "  ops 201507"
             pf_Name = "  ops 201507"
             pvt.AddDataField pvt.PivotFields("ops 201507"), pf_Name, xlSum
     Else
             pf = "  ult 201507"
             pf_Name = "  ult 201507"
             pvt.AddDataField pvt.PivotFields("ult 201507"), pf_Name, xlSum
       End If
     Next pvt
    Next ws
    

    For more information, click here to refer about Like Operator

    • Marked as answer by L.HlModerator Sunday, July 19, 2015 7:02 AM
    Tuesday, July 7, 2015 9:34 AM