Execute string as vba code using vba

Gideon1995

New Member
Joined
May 8, 2018
Messages
3
I am using code to generate code. The generated code is stored within a string, but i can't execute the string. I have been searching for hours but still find nothing. The goal is to update the filters within a pivot table to only show certain months between a start and finishing date, without having to go and select them.

Code:
Dim field As String

field = "ActiveSheet.PivotTables(" & """FinanceTable""" & ").PivotFields( _" & Chr(13) & """[Transaction Date].[Date YQMD].[Month]""" & ").VisibleItemsList = Array( _" & Chr(13)
Range("F1").Value = field
'Add necessary month fields
For i = Starting_Month To Current_Month
    If i < 10 Then
        field = field + """[Transaction Date].[Date YQMD].[Month].&[20180" & i & "]""" & ", _" & Chr(13)
        Range("F1").Value = field
        
        
    Else
        field = field + "[Transaction Date].[Date YQMD].[Month].&[2018" & i & "]"""
    End If
Next i

field = field & "ActiveSheet.PivotTables(" & "FinanceTable" & ").PivotFields( _" & Chr(13) & """[Transaction Date].[Date YQMD].[Day]""" & ").VisibleItemsList = Array("""")"
Range("F1").Value = field

*Execute String as Function Code*
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Why can't you run the code in the usual way?
 

Gideon1995

New Member
Joined
May 8, 2018
Messages
3
Why can't you run the code in the usual way?

I tried using the for loop to create each filter field individually but it doesn't save the previous ones.

Code:
For i = Starting_Month To Current_Month
    ActiveSheet.PivotTables("FinanceTable").PivotFields( _
        "[Transaction Date].[Date YQMD].[Month]").VisibleItemsList = Array( _
        "[Transaction Date].[Date YQMD].[Month].&[20180" & "i" & "]")
next i

I need to keep the previous filtered months *** well. I want to see various columns.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
I think what you need to do is create the array of items/months within the loop and then, outside the loop, set the VisibleItemsList.
Code:
Dim arrItems()

    ReDim arrItems(Starting_Month To Current_Month

    For i = Starting_Month To Current_Month
        arrItems(i) =  "[Transaction Date].[Date YQMD].[Month].&[20180" & "i" & "]"
    Next I
        
    ActiveSheet.PivotTables("FinanceTable").PivotFields("[Transaction Date].[Date YQMD].[Month]").VisibleItemsList = arrItems
 

Gideon1995

New Member
Joined
May 8, 2018
Messages
3
I think what you need to do is create the array of items/months within the loop and then, outside the loop, set the VisibleItemsList.
Code:
Dim arrItems()

    ReDim arrItems(Starting_Month To Current_Month

    For i = Starting_Month To Current_Month
        arrItems(i) =  "[Transaction Date].[Date YQMD].[Month].&[20180" & "i" & "]"
    Next I
        
    ActiveSheet.PivotTables("FinanceTable").PivotFields("[Transaction Date].[Date YQMD].[Month]").VisibleItemsList = arrItems

This works perfectly Thank You so much! Been sitting from this morning at 9.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,657
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top