Why does the following code give me a runtime error on the first statement?
Code:
Sub pt()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Data-Consolidation (2)'!Source_Data", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Data-Consolidation (2)!R1C6", TableName _
:="PivotTable9", DefaultVersion:=xlPivotTableVersion12
[Code] ............
I am trying to create a pivot table on a worksheet that already exists using VBA, but I am getting an error (Invalid procedure call or argument).
Code:
' Create the pivot table on a new tab
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
source_data).CreatePivotTable _
TableDestination:="DJG Clients with Multiple Fee E", TableName:="PT_ClientsMultipleFeeEarners", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
But if I just send the pivot table to a new sheet it works fine -
Code:
TableDestination:=""
I have pieced together a VBA script to create a pivot table. Which is all great and works fine BUT for some reason it is now forcing the table onto a new worksheet. see code below, why it might be creating a new sheet. Alsothe sheet Worksheets("TICKETS BY OWNER & DAY") does exist.
Code:
' HERE WE CREATE THE PIVOT TABLES AND CHARTS FOR REPORTS
Dim WSD1 As Worksheet
Dim WSD2 As Worksheet
Dim WSD3 As Worksheet
[Code].....
I am trying to run the macro below but keep getting Error 5, any ideas why this is at all.
Sub Quarterly07()
Dim PTCache As PivotCache
Dim PT As PivotTable
'Create the cache
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDateBase, SourceData:=Range("A1").CurrentRegion)
' Add a new sheet for the pivot table
Worksheets.Add
'Create the pivot table
Set PT = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=Range("A4"))
Upon opening an existing file, I receive the following error: Unable to Read File….I then click OK, Enable the macros and upon doing so, the following message appears: Excel Table Report on [Sales] Sheet1 was discarded due to integrity problems.
I have two Pivot tables on the report – Pivot8 and Pivot1 – two separate worksheets but using the same source data. I have placed a button on each worksheet to enable the user to refresh the Pivot Table. Pivot1 is the table that is being discarded and when I click the button on Sheet1, the following Run time error 1004 appears: Unable to get the Pivot Tables property of the Worksheet class….when I select Debug, here’s the code with the bolded section being the point of error:
ActiveSheet.Unprotect Password:="test"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("K9").Select
Selection.Copy
Range("K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I am trying to get some averages using pivot tables. However, whenever I set up my pivot table and then go to field settings and pick average, all I get are # DIV/0 errors in all the boxes where there were numbers.
View 4 Replies View RelatedIn creating a macro and creating a pivot table as one of the first steps, I get this runtime error in visual basic:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"original working!R1C1:R1000C13", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet4!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion14
I am trying to to a vlookup on a pivot table and keep getting an error message (The error states that The formula you have entered contains a error) and excel will highlight the 0 in my formula. The formula is
=IF(ISERR(VLOOKUP(G17,'Curr Month'!$G:$H,2,FALSE)=TRUE,0,VLOOKUP(G17,'Curr Month'!$G:$H,2,FALSE)
I am creating 10 pivot tables for data in one worksheet and putting them in a separate work sheet. i used the macro recorder but the cod eit worte stop at this line ...
View 9 Replies View RelatedI created a custom formula for a pivot table.the existing columns are weekly averages.I made a formula to calculate the average of 5 individual weeks.But when one of the value is blank.Blank cell is considered as zero.and hence the final average is wrong.
View 2 Replies View RelatedI am trying to group some dates in a pivot table.
Unfortunately I receive an error saying it can't group the selection.
I have done "text to columns" and made the cells - (date values) and I have had no luck.
As I am at work I can't use the HTML genie because it won't install but I have uploaded a dummy file to my dropbox which available from here [URL] .......
I pull this data from an Oracle POWER PIVOT and always have trouble grouping the dates when they come from Power Pivot. Currently working as analyst so figuring this out would make life so much easier as I could group by months etc.
Below is some code I am trying to run. What I would like to do is select a certain "data row" in the pivot, and apply some conditional formatting to that. It works just fine until I reach the .colorindex = 3 line. It says I'm getting an application/object defined. how to fix this? I'm on Excel 2007. This was fine on 2000!
pvtCurrent.PivotSelect "'% Dist'", xlDataAndLabel
With Selection
With .Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With .FormatConditions.Add(xlCellValue, xlBetween, "0", "0.97")
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
End With
I've converted to a dynamic validation list which allows for a selection of data to be made available based on the selection of the two previous columns.
Unfortunately since creating the new validation a circulating error occurs when tabbing through the table (B) to create a new line or entry? Additionally I've found the following to occur also;
1. The payments and PO# columns converts a red shade colour?
2. The validation on Table B description column do not work while in excel online edit mode?
I am trying to create a pivot table on a new sheet names as 4x4
I am getting following error:
Error : 1004
Reference Not Valid
I am not sure where is the problem. I have generated this code by using macro recorder and just changed the source and destination of the pivot generation code.
I have to generate a report using a pivot table, that tells me what work was done on time and what was late. Entering a formula outside the pivot table sees the scheduled start date drop downs as "blank" and says they are late when they are not. I cannot seem to use the value fields when I try to create a calculation field within the table. I use 2010 at work, 2007 at home.
sep 11-17
FGHIJK10Count of WO No.
=IF(G12>=H12, I12," ")=IF(G12
Excel 2007
No empty rows
No empty cells
No calculations in cells
Field names look fine - no punctuation no merged cells
If I select one column - any single column I can generate a pivot table but not with multiple columns
I am trying to adapt a macro to run on data that is consistently in the same format but the data does change, the macro needs to run over and over on new excel workbooks, dynamic range ? instead of a set source ?
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"weekly71012!R1C1:R9379C30", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
I am sure this is the problem as i initially created the macro using weekly71012 excel file however i just want it to run on every file i pull it off?
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.
I'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 RelatedI 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 am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I keep running into this error when I try to create a pivot table.
The debugger highlights the following line:
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View 2 Replies View RelatedI have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.
However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class
The code where the debugger stops is the last line below here.
lngKpi = Sheets("Helpfile"). Cells(lngRow, 2)
Sheets("Charts").Select
ActiveSheet.ChartObjects("DK").Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi
I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
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 Replies View RelatedI'm not grasping the Pivot Table correctly. I've written code to create a sum of values based on a worksheet. Specifically:LocationIDDeptSum of Hours Worked. Location, ID and Dept are rows. This effectively provides the aggregate values that I need based on the row groupings.Here's where this is falling apart. I need to create a new worksheet based on these values. I assumed the three row values - Location, ID and dept - would be in a hierarchy. It's possible they are, I just can't figure out the object model.
When I loop through the PivotItems collection of the PivotFields("Location"), I get what I need. However, I'm unable to determine how to loop through the child values (just for that location). PivotFields("ID") returns all IDs. I can't figure out how to return only the child entries for each pivot item. GetPivotData hasn't been very useful for this. As far as I can tell, GetPivotData, while its return type is listed as Range, throws an error when more than one cell is returned. Worst case, I suppose I can just parse the data in the DataBodyRange of the pivot table - maybe not, I haven't tried that. I'm hoping there's a way to iterate through these collections, but based on what I've seen from Google searches, there may not be. Does my pivot table need to be rearranged? I suppose I could also just dump this data into a data table