pivot table

J

JIM.H.

Hello,
I have an excel file created daily which keeps 3 columns
data. What I need is: each time the users open it they
should see data in a pivot table, not as three-columns.
Can I do that? The file is ready only and user does not
have to write it back.
Thanks,
Jim.
 
D

Dave Peterson

Couldn't you create the pivottable the same time that the workbook is created.

If no, then maybe you can record a macro that creates the pivottable once.

Then either run this macro after the creation--or give the macro to the
users...In fact, maybe you could give the users a macro that would open the
newly created file and run your pivottable code.

And you could drop out of the loop!
 
G

Guest

Hello,
Thank for the reply. It helped a lot.
In Excel 97, I used record macro feature to create a
pivot table and it defined SourceData:= "20040608!
R1C1:R4C3") in the pivot table definition. Here 20040608
is the first sheet name and R1C1:R4C3 is the range for
the data. Each time when I run the macro these data would
be different, how can I figure out them and use as
SourceData in the pivot table.
Thanks,
Jim.
 
D

Dave Peterson

I would think that the pivottable's source data is always 3 columns, but the
number of rows vary. If you can pick out a column that defines the last row (I
used column A), maybe this would work:


If that worksheet's name is picked up from the date (it kind of looks that way):

Dim mySourceDataRng As Range

With Worksheets(Format(Date, "yyyymmdd"))
Set mySourceDataRng = .Range("a1:C" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

'later

...., sourcedata:=mysourcedatarng.Address(external:=true), ...


=====
If it's always the leftmost worksheet, just change the With statement to:

With worksheets(1)
 
Top