I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.
View 1 RepliesMy macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.
New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.
Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.
My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.
Fix / Solution:
Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.
Code:
Dim VBAPPPC As PivotCache
Dim VBAAPPC As PivotCache
Dim VBAPRPC As PivotCache
Dim VBAEXPC As PivotCache
Dim VBAMJPC As PivotCache
Dim VBAIVPC As PivotCache
Dim VBAPT As PivotTable
[code]...
We are trying to create pivot cache from an external .csv file.
This works fine when the csv file is small (~500 mb).
Attaching sample code for reference -
Code:
Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim SQL As String
''Creating ADODB connection object
[Code] ...........
Let's say I have a pivot table made from data in another workbook. The other workbook is then deleted. Is there anyway to get the original data back? My particular file is about 10 MB, but if I "save as" it's only 1 MB. Plus, the pivot table "works," so I'm pretty sure Excel still has my data cached somewhere. How can I access it? I'm decent with VBA, so I'm open to any solution at all.
View 5 Replies View RelatedI am trying to create multiple pivot tables from the same pivotcache using VBA. The data range is approximately 270,000 records with 100 columns. When I run the macro, I get "Run-time error '-2147352567 (80020009)' Method 'Add' of object 'PivotCaches' failed". Is there a size limit on the data? If there is less than 65,000 records, it works great; if I use a wizard, it works with no problems with the full dataset. If I bring the data in as an Access table, it can work with some tweaking of the code, but the size of the file is huge because each pivot is taking its own snapshot of the data. I also can only create 3 tables before it runs out of memory, so I have to stop, save the file, and open it back up to create 3 more pivots.
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
[Code] .........
The last line is where I am getting the error. I am running XP, Excel 2007.
As I said, if I import an Access table, I can use the code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_Pivot_test.accdb[#All]", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:=txtPivotTableName, DefaultVersion:=xlPivotTableVersion12
And loop, but the final file size with 14 pivots can be 500MB! I have tried both "Create" and "Add", but they both give me errors.
I am currently taking over a workbook from a colleague that contains 10 pivot tables. Spaced out over a few sheets.
Each pivot table contains a lot of information and my current workaround to refreshing them all is..
Step1:Refresh 2 Pivots,
Step2:Save and Close document,
Step3: Re-Open document and repeat Steps 1 & 2 until all refreshed.
I was wondering if there was any kind of code to clear the memory space being used by the pivots thus leaving the hard coded information behind.
That way I can just write some vb to refresh all the pivots and not keep saving and re-opening the document which takes sometime as its on a network drive.
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies View RelatedI'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies View RelatedThe new worksheet is created to the left of the existing source worksheet.
View 2 Replies View RelatedI have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from
Code:
Sub SalPiv()
'
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick
[Code].....
What is causing this to error. Basically I'm just trying to highlight cells, and create a pivot table out of that. Here's my Code:
Sub Macro1()
'
' Macro1 Macro
Dim myRange As String
myRange = "Sheet1" & Selection.Address
Sheets.Add
[Code] ..........
When I debug, the value of myRange is something like Sheet1$A$19:$K$37 with the actual cell values being whatever I highlighted. I get a Runtime Error 5 Invalid procedure call or argument. I'm assuming it's something with my sourcedata variable, but I'm not sure what.
Also, I did try commenting out the Sheet.Add and Sheets("Sheet2").Select Cells(3, 1).Select lines, but it still produced the same results.
I am trying to fix a report for a remote coworker in excel, they tell me they get
Run-time error '5': Invalid procedure call or argument
When they run the Macro, here is where it stops executing
Code:
Range("C6").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!$A:$Y" _
, Version:=xlPivotTableVersion14)
Now, the problem is that I am unable to recreate the error, and it always works fine for me. I am the 'excel guy' around the office, but I'm still learning VBA, so I am not entierly sure what is going on in this section of code, but I generally follow it.
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
1) I want to create a pivot table in a new tab that has both the prefix “ENG” and the date of creation as the new name of the tab every time a new table is required, using the example headers highlighted in the attached file.
2) Then if possible format the table as in the example, i.e. bordered cells for all the data and the dates only aligned to the right. Example in tab “Eng 01 05 09”
No 1 is the most import question I would like help on. Formatting is not crucial. This is only to make the table easier on the “eye of the beholder”, I am writing this spreadsheet for people who have less knowledge than myself and would like to keep it as simple as possible, (run a macro and get the results).
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies View RelatedI regularly build a pivot table using VBA. I now want to group countries into regions within the pivot table, and I want to automate that using vba.
For simplicity, assume I have the countries Germany, Netherlands, Belgium, Luxembourg, Norway and Sweden; and I want to create the Groups BeNeLux and Scandinavia. (In reality, I have 150 countries, one group of 10 countries, 2 countries I want to show individually, and the rest of the world I want to group together)
I know I could select the cells using something like cells(d2:f2).group, but that seems wrong....
I have attached a file showing how far I can get with vba and where i want to get.
I have also tried things like ".DataRange.cells(2).group by:=3" but that never led to anything....
grouping reqs.xls
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
View 7 Replies View RelatedCan record the all those actions which we need to do on a regular basis. In my work i have to create the pivot table to a data which gets changed every day but the data sequence remains the same.
I have tried to record the macro for the pivot table but when i tried to run the macro again then the below error message comes.
"run time error 5" "Invalid procedure call or argument" when i click on debug then the below macro program is highlighted in yellow color.
For example...
I have data for monday.. Tueday ..Wednesday as individual excel
All the excel have same column names and dynamic values.
Is there a way i can create one pivot with different excel as source
I got this code from another old old thread, is there a way that the pivot is already setup like column A is already in Row Labels, B is for Column Labels and C to Values.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _[a1].CurrentRegion.Address).CreatePivotTable
TableDestination:= _"", TableName:="PivotTable1"ActiveSheet.PivotTableWizard
[Code] ......
Another question about pivot tables. In Excel it is possible to show different kinds of information in a pivot table like sum, total, min, max, average and so on. I want to create my own formulas in my pivots. Is this possible and if so how?
View 7 Replies View RelatedI have two questions about dealing with formulas in pivot tables and how to make them dynamic. First let me give you some further background.
My database countains the following variables: Country, Company, Period, Product, Category and Sales. Out of this database i created a pivottable which sums the total sales amount for each company in every period. Further selection on country and category is possible in this pivot table. See my attached file!
I want to express the sales of each company as a percentage of the total sales in that period. For example for company A in period 1 their total sales was (929/3172) 29,3% of the total market sales. Which formula do i have to use in my pivot table to express company's market shares?
Further my pivot table can be specified more detailed by country and category. I want to be able to select on country and category in such a way that the company sales are still expressed as a percentage of the market sales. In other words how can i make my pivot table dynamic?
I have a pivot table without report filter and I set insert page break after one of my selected row labels.
I also would like when I print, these row Labels become the title of these pages at the same time.
Actual File : Untitled.png
However I would like when I click on print or save as pdf will print all pages as one file and each Center that I have set page break for, in separate page and add that specific center as its title:
Attachments for more details:
test_Page_1.jpg
test_Page_2.jpg
test_Page_3.jpg
test_Page_4.jpg
I am currently trying to create a multiple pivot tables in one sheet. The current formula to make a single pivot table that i am using is
Formula:
[Code]....
The current problem i have is... i need to Dim PT01 as pivottable, Dim PT02 as pivottable, Dim PT03 as pivottable and so on.... Repetition is not good!
and secondly,the total pivot table is not constant each week. During busy week, i need to create 8 pivot table and in a slow week, it can go down to 5.
See below of my current formula...
[Code] ....
How to create pivot table in a better way?
Fixing a macro that i recorded in order to generate a pivot table on the same sheet and the same exact location every time. it seems that the macro fails every time i try to run and i'm thinking it has to do with the table reference. Again i want the table in the same sheet and the same location every time i run the macro. this is the recorded code so far.
View 1 Replies View RelatedI'm getting a 1004 run-time error when creating a pivot table.
I believe this happens because I'm creating it in a hidden sheet. When I unhide it no errors occur.
I have the following code that will open each file in a folder and run code on it. I'm trying to write code that will create a pivot table on each of the opned files...but cannot get past opening the file...
So I had built the vba code into a specific workbook so I could just drop that workbook into any folder I wanted to run code on files to and depending on the name of the file it would run different type of code. The problem has something to do with using "activesheet" for the pivot table wizard. It just doesn't like it and gives me a application 1004 error.
any way I can modify the activesheet so it will make a pivot of the newly opened file? I think what it is trying to do is make a pivot of the blank workbook that the code resides in
Sub filelooper()
MyDir = ActiveWorkbook.Path ' current path
Dim MyFile As String
[Code].....
I'm trying to create a pivot in vba. I can record what I have done but if I run that recorded code, it returns an error "Invalid call procedure or argument".
Code:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Sheets("Raw Data").Range("A1:AG" & Z), _
TableDestination:=Range("A3"), _
[Code].....