Create Pivot Table Using Code

S

S Jackson

I am creating a workbook that tracks different types of employee hours. The
workbook will be used by the supervisor to make daily entries that are
summarized on a monthy basis.

I want to create a pivot table on each of the worksheets that automatically
updates. However, the worksheets currently contain no data, so therefore, I
cannot create a pivot table. Is there code that can be initiated by the
SelectionChange event of the worksheet that will generate a pivot table at a
certain location on the same worksheet?

S. Jackson
 
D

Debra Dalgleish

You could create a dummy record on each worksheet, e.g.

Date Name Qty
9/1/2005 Enter first name here 0


Then define a dynamic range, and base the pivot table on that. There are
instructions for using a dynamic range here:

http://www.contextures.com/xlPivot01.html

Users can overwrite the dummy record when they start using the worksheet.
 
S

S Jackson

That's an excellent idea. Now, I have a new problem. When I paste in the
code, close the workbook and reopen it and then try to make an entry I get
the message about macors being disabled.

I went to the website: http://www.contextures.com/xlfaqMac.html#NoMacros

I tried deleting all of the code as instructed, saving and closing the
workbook and then reopening it and adding the code back in. I saved and
closed the workbook and I still get the error message.

Grrrr - this is so frustrating!

S. Jackson
 
D

Debra Dalgleish

What code are you pasting in? The example for a dynamic range doesn't
have any code.
 
S

S Jackson

Oops! I'm sorry I didn't explain myself. I want the pivot tables to update
automatically:
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub
But, I cannot put the code into any sheet without ending up with the error
coming up. I have had to delete all code in the workbook in order to avoid
the error.

FYI, I'm using Excel 2000.

S. Jackson
 
D

Debra Dalgleish

The message you described will appear any time a workbook contains code,
and your security level is set at Medium. It gives you the opportunity
to enable the macros, or disable them.

Since you created this workbook yourself, and know that the macros
aren't harmful, just click the Enable Macros button, and then your code
can run.
 
S

S Jackson

I gave up trying to fix that workbook and started over. I completely
redesigned things and it is much simplier to use. However, now I have a new
problem. Now when I insert the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

on the worksheet that contains the pivot table, NOTHING happens. It does
not automatically update, nor do I get any messages about macros not being
enabled when I open the workbook or make entries.

This is the craziest thing I've ever seen. Any thoughts?

S. Jackson
 
R

Roger Govier

When you get the warning come up about Macros, you should have the
opportunity to choose "Enable".
If not, go to Tools>Macros>Security and set the level to Low

--
Regards

Roger Govier


S Jackson said:
Where on earth is the enable macros button????

Shelly Jackson
Click to expand...
 
Top