Select Today's date in correct format in PIvot Macro

[crypto]

I have a Pivot table whicih I wish to Refresh and then select "today" as the PAGE Date variable

I can get this to work with (extract )

Sub Test1
Dim Tempdate As Date
Tempdate = Date
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Tempdate

End Sub

But the format is incorrect ( selects 8/15/2007 ) , all my data is in format 15/08/2007 , even if I change the data format for the table it still doesn't select the correct items
Manually I can refresh data and just select the latest day , no worries . But I can not seem to write it into a macro
Help please you wonderful people

 

[golfbum71]

Date Format Change

crypto,

Try this:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Format(Tempdate, dd / mm / yyyy)

I just learned this the other day and may not have it totally correct...but I'm thinking it should work.

Good luck,
Geoff

 

[crypto]

Thanks Geoff

Tried a few different combo's and still could not get it to work , thanks anyways

Cheers

 

[golfbum71]

Try Three

Try This:

Sub GeoffTry()

If Application.ActivePrinter = "\\SLZDESIGN\HP LaserJet 4 on Ne04:" = True Then

ActiveSheet.Unprotect "dod"
Cells.Interior.ColorIndex = xlNone
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
True


ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\SLZDESIGN\HP LaserJet 4 on Ne04:", Collate:=True


'NEW CODE 8/14/07 TRYING TO MEET THE NEEDS OF DENVER WITH THE NEW PROCESS


End If

iResponse% = MsgBox("Do you want to save this document", vbQuestion + vbYesNo, "THARCO")

If iResponse% = vbYes Then

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
ThisWorkbook.SaveAs fileSaveName
End If
FileLoc = Application.ActiveWorkbook.FullName 'This is added in -- hopefully will stay into next steps

Else
MsgBox "This sample request will not be saved", vbExclamation, "THARCO"
End If


Dim LastRow As Range
Dim ws As Worksheet
Set ws = Sheets("Sheet1")


ChDir _
"\\FP_SLZ\SLZ\DESIGN"
Workbooks.Open Filename:= _
"\\FP_SLZ\SLZ\DESIGN\SAMPLE REQUEST LOG 2005.xls"
ActiveSheet.Unprotect "DOD"

Sheets("2007").Activate

Set LastRow = Sheets("2007").Cells(Rows.Count, 1).End(xlUp)

fname = ws.[AB3]
    Range("A65000").End(xlUp).Offset(1).Select
        ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:=FileLoc, _
        TextToDisplay:=fname

With LastRow

.Offset(1, 1) = ws.[O14]
.Offset(1, 2) = ws.[O12]
.Offset(1, 3) = ws.[F16]
.Offset(1, 4) = ws.[O16]
.Offset(1, 5) = ws.[AB12]
.Offset(1, 6) = ws.[AB14]
.Offset(1, 7) = ws.[F32]
.Offset(1, 8) = ws.[AB16]
.Offset(1, 9) = ws.[AB18]

End With

End Sub

Good Luck,
Geoff

 

[crypto]

Sorry Geoff , I don't understand how all that code relates to my problem , thank you but sorry I don't understand

 

[golfbum71]

This time with quotes

crypto...sorry, was from another post and I can't delete it now. Oh well.

Have you tried using quotes like this?

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Format(Tempdate,"dd/mm/yyyy")

I forgot about that part...

And I'm sorry, I don't know much about Pivot Charts.

Cheers,
Geoff