I'm trying to tweak a piece of my code to allow me to use a date as a variable in selecting information in my pivot table. Here's my code:

Sub RS_IBOB_Open_Select()
'
' RS_IBOB_Open_Select Macro
'

'
SelectDate = Format(Date - 1, "YYYYMMDD")
WShtDate = Format(Date - 1, "mm.dd.yy")
BOCM = Format(DateSerial(Year(Date), Month(Date), 1), "mm.yyyy") 'Begin of Curr Month


Application.DisplayAlerts = False


Workbooks.Open Filename:= _
"http://mminet/sites/fcmgt/Michael%20Cagan/RS%20Day-Week-Month/Updated%20Daily/Right%20Start%20Inbound-Outbound/RS%20Inbound-Outbound%20Tracking%20" & BOCM & ".xlsx"


Sheets(WShtDate).Select

Workbooks.Open Filename:="H:\BI Reports\RS IB - OB.xlsx"

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

ActiveSheet.PivotTables("PivotTable1").PivotFields ("[Date].[Day].[Day]"). _
VisibleItemsList = Array("[Date].[Day].&[20110105]")


End Sub


The very last row is the one I am interested in making a variable:

VisibleItemsList = Array("[Date].[Day].&[20110105]")

Can I make the 20110105 a variable? I've tried to play around with the code to do something like

SelectData = format(Date - 1, "yyyymmdd")

VisibleItemsList = Array("[Date].[Day].&[SelectData]")

That didn't work so I tried

VisibleItemsList = Array("[Date].[Day].&[" & SelectData &"]")


Any thoughts?

I would have suggested your last line of code, but if that doesn't work, I don't know off the top of my head. I do very little work with Pivot Tables. (shame)

If you can provide a sample workbook with your macro installed, as well as that external reference file, I can take a look and see if I can suss it out.

Quote:

---

Originally Posted by JBeaucaire

I would have suggested your last line of code, but if that doesn't work, I don't know off the top of my head. I do very little work with Pivot Tables. (shame)

If you can provide a sample workbook with your macro installed, as well as that external reference file, I can take a look and see if I can suss it out.

---

Jerry - thanks for offering to take a stab at it... I would send you the file but it's sensitive company info... gosh, it's hard to find an answer to this question on other forums, too; I guess pivot tables are not popular :(

Appreciate you, man.

It usually takes less than 3 minutes to desensitize a copy of your workbook. FOr names, put NAME1 in the first name cell, then double-click the lower right corner to copy it down, causing all the names to change to:

NAME1
NAME2
NAME3
etc...

Now repeat that with ADDR1, and CITY1, COMPANY1.

You get the idea. It shouldn't be THAT hard to desensitize a wb to the point it can be worked on.

Quote:

---

Originally Posted by JBeaucaire

It usually takes less than 3 minutes to desensitize a copy of your workbook. FOr names, put NAME1 in the first name cell, then double-click the lower right corner to copy it down, causing all the names to change to:

NAME1
NAME2
NAME3
etc...

Now repeat that with ADDR1, and CITY1, COMPANY1.

You get the idea. It shouldn't be THAT hard to desensitize a wb to the point it can be worked on.

---

Jerry - I figured it out... with a little help, though. Here's what I did (in an abbreviated form of my earlier code:

Sub Macro1()
'
' Macro1 Macro
'

Dim dateselect As String

mydate = Format(Date - 2, "yyyymmdd")

dateselect = "[Date].[Day].&[" & mydate & "]" 'pivotfield string



ActiveSheet.PivotTables("PivotTable1").PivotFields ("[Date].[Day].[Day]"). _
VisibleItemsList = Array(dateselect)

End Sub

Anyway, just thought I'd share the solution with you in case you came across something like it again.