Macro with PivotTable

[antonio]

I recieve upate data from Access everyday. In the first colunm I have "todate" with different dates, but I only need "yesterday" date. So, my goal is filter the table with a yesterday formula that I created :=TEXT(WORKDAY(TODAY(),-1),"yyymmdd")

My problem is like "yesterday" change everyday I don't know how I can do dynamic. This is my macro:


Sub Macrob()

'

' Macrob Macro

'


Sheets("IMSRD ACCESS").Select

Range("Table_Swaps_reconciliation.accdb[#Headers]").Select

Range("Table_Swaps_reconciliation.accdb[[#Headers],[REMNOMINAL]]").Activate

Selection.AutoFilter

Range("O1").Select

Selection.Copy

ActiveSheet.ListObjects("Table_Swaps_reconciliation.accdb").Range.AutoFilter _

Field:=1, Criteria1:="20110830", Operator:=xlAnd

Range("A1:L24843").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("IMSRD Paste").Select

Range("A1").Select

ActiveSheet.Paste


End Sub


My guess is that i should change something in Criteria1:="20110830"

any comments?

Thanks in advance.

 

[Luke M]

Something like this?

[pre]

Sub Macrob()
'
' Macrob Macro
'

Sheets("IMSRD ACCESS").Select
Range("Table_Swaps_reconciliation.accdb[#Headers]").Select
Range("Table_Swaps_reconciliation.accdb[[#Headers],[REMNOMINAL]]").Activate
Selection.AutoFilter
Range("O1").Copy

'Change begins here
Dim MyCriteria as Date
MyCriteria = Date - 1
ActiveSheet.ListObjects("Table_Swaps_reconciliation.accdb").Range.AutoFilter _
Field:=1, Criteria1:=MyCriteria
Range("A1:L24843").Copy Sheets("IMSRD Paste").Range("A1")

End Sub

[/pre]