Pivot Table Specify Specific Ranges ?

robertguy

Board Regular
Joined
May 1, 2008
Messages
121
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
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:p></o:p>
Any help would be greatly appreciated.
<o:p></o:p>
<o:p></o:p>
Many Thanks in advance
<o:p></o:p>
<o:p></o:p>
Bob
<o:p></o:p>
Excel version 2003
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, I don't believe so, though you could just use A:Z for the example you quoted rather than A:AZ.
 

mae0429

Board Regular
Joined
Jun 12, 2008
Messages
114
You can make a pivot cache that is specific to that data range:

Code:
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:

Code:
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

Board Regular
Joined
May 1, 2008
Messages
121
mae0429<SCRIPT type=text/javascript> vbmenu_register("postmenu_1595069", true); </SCRIPT>
where would I put this code in a Macro ?

Rob
 

mae0429

Board Regular
Joined
Jun 12, 2008
Messages
114
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.

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top