Hello all,

I'm fairly new to VBA and have been writing a macro for work. I'm stuck on a macro that will incorporate changing dates into the worksheet name. Below is my code for the pivot table.

Columns("D:K").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"GL_SEEQ_20130722!R1C4:R1048576C11", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion14

It all works except tha the SourceData "GL_SEEQ_20130722 is date sensitive. What I am trying to do is code this so that it will read that the worksheet will always be GL_SEEQ_(yestdays date).

I have successfully programed the macro to open this work book base on date using these codes but when I try to use these codes for the pivot it won't read them.

Dim udate As Date
Dim fdate As String
Dim fmonth As String
Dim fday As String

udate = Date - 1

If Len(Month(udate)) = 1 Then
fmonth = "0" & Month(udate)
Else
fmonth = Month(udate)
End If

If Len(Day(udate)) = 1 Then
fday = "0" & Day(udate)
Else
fday = Day(udate)
End If

fdate = Year(udate) & fmonth & fday
Dim filename As String
Dim filename2 As String
filename = "GL_SEEQ_" & fdate & ".csv"
filename2 = "GL_SBMB_" & fdate & ".csv"

' open workbooks

Workbooks.Open ("\\scntsamba\derivProdData\Gsf\reports\" & Year(udate) & fmonth & "\" & filename)
Workbooks.Open ("\\scntsamba\derivProdData\Gsf\reports\" & Year(udate) & fmonth & "\" & filename2)

Could someone please help me?