 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2005Create Pivot Table Using Code
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 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. > 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 [quoted text clipped - 5 lines] > SelectionChange event of the worksheet that will generate a pivot table at a > certain location on the same worksheet?  SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html 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 > You could create a dummy record on each worksheet, e.g. > [quoted text clipped - 22 lines] > Excel FAQ, Tips & Book List > http://www.contextures.com/tiptech.html What code are you pasting in? The example for a dynamic range doesn't have any code. > 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 [quoted text clipped - 48 lines] >>Excel FAQ, Tips & Book List >>http://www.contextures.com/tiptech.html SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html 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 > What code are you pasting in? The example for a dynamic range doesn't > have any code. [quoted text clipped - 56 lines] > Excel FAQ, Tips & Book List > http://www.contextures.com/tiptech.html 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. > Oops! I'm sorry I didn't explain myself. I want the pivot tables to update > automatically: [quoted text clipped - 82 lines] >>Excel FAQ, Tips & Book List >>http://www.contextures.com/tiptech.html SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Where on earth is the enable macros button???? Shelly Jackson > 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 [quoted text clipped - 95 lines] > Excel FAQ, Tips & Book List > http://www.contextures.com/tiptech.html 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 > Where on earth is the enable macros button???? > [quoted text clipped - 107 lines] > > Excel FAQ, Tips & Book List > > http://www.contextures.com/tiptech.html 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 SignatureRegards Roger Govier > Where on earth is the enable macros button???? > [quoted text clipped - 107 lines] >> Excel FAQ, Tips & Book List >> http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.