The line of code from Barnabel should be added to the end of the code that
refreshes the pivot table. If the code is in the Workbook_Open event,
specify the worksheet the cells are on:

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
Worksheets("Sheet1").Cells(1, 1) = TimeValue(Now)
'for Date & Time =Date & " " & TimeValue(Now)
End Sub

Be sure to format the cell for Time.

Mike F
"barnabel" wrote in message
...
2 comments:
1) "Now" is a real VBA function so it is a bad idea to create a function
with the same name
2) Wouldn't this be a much easier way to do it?
Sub timeStamp()
Cells(1, 1) = TimeValue(Now)
End Sub

Peter Richardson

"Hal" wrote:

There's probably more than one way to do this but here is the solution I
came
up with.

Sub NOW()
Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Selection.HorizontalAlignment = xlLeft
End Sub


"Just Learning" wrote:

I have a pivot table that will refresh source data (upon opening),
from an AS400 database through an ODBC connection.

I would like to have a time stamp on the pivot table that shows the
exact time of the source data was pulled from the AS400 database.

Right now I just have a formula =now() which can change if moving
from tab to tab and doesn't mean the source data was refreshed at
that time.

I'm just starting to learn Macros/VBA so if there is some code
snipit that would do the trick please be as detailed as possible as
to how I would include it.

Thanks in advance for your help.


--
--------------------------------- --- -- -
Posted with NewsLeecher v3.8 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -