Is it better to create a pivot table manually or via VBA? (Excel 2007)

I have a few pivot tables that I created manually and have a VBA code to refresh their data.  However, I was wondering which one is better?

            Create all the pivot tables manually and use their corresponding worksheets when needed

OR

           Create a pivot cache and when one table is needed, only then create that table using VBA

Which one will have a smaller file?

Also, I have data residing within the workbook, but whenever I open the workbook, I have a VBA code that checks to see if there is a data file (maybe updated) in a shared drive.  If so, it copies that worksheet into the current workbook and deletes the old data worksheet.  This allows anybody to do work offline, but I am not so sure if it is going to create any problems as this will probably sever the pivot table data links.

BTW, I am using Excel 2010, but others have 2007

Any suggestions?

August 27th, 2013 11:39pm

Hi

For your first question, a pivot Cache is nothing more than a special memory subsystem in which your data source is duplicated for quick access.

That is to say, Excel literally makes a copy of your data, and then stores it in a cache that is attached to your workbook.

So if you want your file a smaller size, using VBA may be a better choice.

For the second doubt, if structure of the data source doesnt change, it may not server pivot table data links.

However I cannot reproduce your scene absolutely, if there is any more problems after updating your workbook via VBA, contact us and we will give further help.

Regards

Tylor Wang
TechNet Community Support

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2013 3:18am

Hi,

I have a few more questions now.

* I am doing advanced filtering on the data, but would you consider this a change in the structure?

* When I do a "refresh data", I clear all the filters, change the data file back to the original, and refresh the pivot cache.  However, this refresh is done through an Active X control on the worksheet so there is a private sub associated with it.  I was also using this FinalRow1 assignment to know the number of rows in my data:

FinalRow1 = Sheets("1").Cells(Rows.Count, "E").End(xlUp).Row

I made FinalRow1 Public so it was ok, until I made the call via the Active X Command button, maybe because it was coming from a private sub on a worksheet, FinalRow1 was becoming zero and messing up my advanced filter range clear process.  So, I had to copy the above line before making the clearing. 

While I was thinking everything was fine, the pivot cache started to have problems too

  1. Does refresh cause this problem?
  2. OR, is the culprit this "ActiveSheet.PivotTables(1).SaveData = False"  which helped with the size of my file?

* I was defining the pivot cache in the "This Workbook" and declaring it Public in one of the modules.  Now, when somehow the code loses its connection to the pivot cache, I have to do the following and it seems somewhat useless, but I don't know what else I can do

Set PVTCache_1 = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=Sheets("1").Range("A1:X" & FinalRow1))
       
Set PT = ActiveSheet.PivotTables.Add( _
    PivotCache:=PVTCache_1, _
    TableDestination:=Range("A1&q

August 30th, 2013 7:02pm

I was looking at some solutions in the VBA books I have at home as well as some examples over the internet, and I started to think if the following makes sense

I will define DataRange as a Named Range , i.e. DataRange will be "A1:X43000"  and I will change FinalRow1 to a Named Range too, although it will only have one element which will be 43000. This way I won't be losing its information nor the DataRange, but I don't know how it will affect the size of my file.

Any sugges

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2013 7:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics