Pivot Table Specify Specific Ranges ?

[robertguy]

Hi every week I create a pivot table based on the on the number of weeks that have passed in the year to date so far e.g. this week is week 24, however, when I select the range for the pivot table I select all of the columns from the data table (A:AZ) and consequently with the new data being added every week my file is now over 10MB and growing every week.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
My question is. can I only selective the columns of data I want to use in my pivot table e.g. column A, D. S, X , Z rather than highlighting the data A:AZ.
<o></o>
As a work around to make the file smaller I have turned off the facility “Save Data with Table Layout” which does reduce the file size somewhat
<o></o>
Any help would be greatly appreciated.
<o></o>
<o></o>
Many Thanks in advance
<o></o>
<o></o>
Bob
<o></o>
Excel version 2003

 

[RoryA]

No, I don't believe so, though you could just use A:Z for the example you quoted rather than A:AZ.

 

[mae0429]

You can make a pivot cache that is specific to that data range:

Dim pivCache As PivotCache
Set pivCache = ActiveWorkbook.PivotCaches.Add SourceType:=xlDatabase, SourceData:="Specify your data range (sheet and range)"<YOUR here data source here<SPECIFY goes range><IDENTIFY Here Range>)

Then you would use the pivot cache to create a pivot table:

pivCache.CreatePivotTable TableDestination:=Worksheets("Sheet where you want the table").Range("Range where you want the table"), Tablename:="Table Name"
Worksheets("Sheet where you put the table").Activate
With ActiveSheet.PivotTables("Table Name")
    .PivotFields("Field1").Orientation = xlDataField
    .PivotFields("Field2").Orientation = xlRowField
    .PivotFields("Field3").Orientation = xlPageField
    .PivotFields("Field4").Orientation = xlColumnField
End With

Fill in the parentheses with your variables, then just create as many fields as you want, then you can do some clean up.

You can also use the pivot cache for more than one pivot table, just add another and give it a different name.

That should give you the bare essentials you need. Any questions, just ask.

 

[robertguy]

mae0429<SCRIPT type=text/javascript> vbmenu_register("postmenu_1595069", true); </SCRIPT>
where would I put this code in a Macro ?

Rob

 

[mae0429]

Untested! You could also just put parts of this in wherever you find the specified range. I hope the comments help you follow the basic pattern here.

Option Explicit
 
Sub PivotCacheAndTable()
    'Define variables
    Dim myWorkbook As Workbook
    Dim pivCache As PivotCache
    Dim myRange As Range
 
    'Locate data set
    Set myWorkbook = ActiveWorkbook
    Worksheets("whatever sheet your data is on").Activate
    Set myRange = "wherever your range is"
 
    'Set the pivot cache
    Set pivCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=myRange)
 
    'Add a new sheet if you want
    With ActiveWorkbook
        Worksheets.Add
    End With
    ActiveSheet.Name = "PTSheet"
 
    'Add a blank pivot table to that sheet at cell A1 called PT1
    pivCache.CreatePivotTable TableDestination:=Worksheets("PTSheet").Range("A1"), Tablename:="PT1"
 
    'Add in fields as desired
    With ActiveSheet.PivotTables("PT1")
        .PivotFields("Field1").Orientation = xlDataField
        .PivotFields("Field2").Orientation = xlRowField
        .PivotFields("Field3").Orientation = xlPageField
        .PivotFields("Field4").Orientation = xlColumnField
    End With
End Sub

-Matt