Pivot Chart Error: Unable To Set The _Default Property Of The Pivot Item Class
I 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.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Unable To Set The Xvalues Property Of The Series Class
For the last day I have been getting the following error: "Run-time error '1004': Unable to set the Xvalues property of the series class." The archetecture of the marco is sound being that I have used this methology in another macro and it works correctly every time. The XValuesand Values range are also correct and I know this because I had the macro select the data cells and it does this correctly as well. The data for simplicity at the moment has no empty cells or abnormal data. The data cells are all filled with some kind of data. For the XValue Range the data would look something like "2070087 4/27/2007" and the Values range is filled with a number between 0 and 100. What I can't figure out is why it crashes with a valid establish range. I highlighted the lines it crashes on in read, they are near the bottom. Sub Proto1() ' ' Proto1 Macro ' Macro recorded 8/30/2007 by aaron.verellen ' ' Dim CurrentSheet As String, ChartName As String Dim RowCount As Integer, ColumnCount As Integer, FirstColumn As Integer, LastColumn, _ FirstRow As Integer, LastRow As Integer, Row As Integer, Column As Integer, _ ProductColumn As Integer, ArrayIndex As Integer, ParameterCount As Integer, _ ItemCount As Integer, MyIndex As Integer Dim UserProductFlag As Boolean, NewProductFlag As Boolean Dim Possibilities() As String Dim ProductXRange As Range, ProductYRange As Range .................
View Replies!
View Related
Unable To Set The Visible Property Of The Worksheet Class
I've been using the following code successfully for years. Today I would get the error 1004:Unable to set the visible property of the worksheet class. All my searches came up with someone not realizing they had protected the workbook. As you can see, the first thing I do before trying to set the worksheet visible is to unprotect the workbook. In frustration and on a lark I tried .Sheets("items").Visible = True and it worked. These kind of intermittent errors in Excel VBA are very frustrating. Can anyone tell me what I am doing wrong?
View Replies!
View Related
Unable To Set The Formula Property Of The Series Class
I am struggling with one problem. I am trying to change formulae of series in a chart using VBA code given below. But I am getting 'Runtime Error 1004, Unable to set the formula property of the series class' ActiveChart.SeriesCollection(1).Formula = _ Replace(ActiveChart.SeriesCollection(1).Formula, "Sheet1", "test")
View Replies!
View Related
Unable To Set The Locked Property Of The Range Class
I am using the below code to unloock certain columns based upon a value in cell a1. The value is the previosu month end date. It was working, but now I am getting " run-time error 1004 Unable to set the locked property of the range class" Can anyone help me to fix the error. Sub UnlockMe() Dim cl As Range ActiveSheet. Unprotect "password" For Each cl In Range("$B$1:$M$1") If cl = [a1] Then cl.EntireColumn.Locked = False cl.EntireColumn.Interior.ColorIndex = 6 Else cl.EntireColumn.Locked = True cl.EntireColumn.Interior.ColorIndex = xlNone End If Next cl ActiveSheet.Protect "password" End Sub
View Replies!
View Related
Unable To Set ColorIndex Property Of Interior Class: Fill Cell Color Macro
I have an error message that says: Run time error '1004': Unable to set the colorIndex property of the interior class. I attached code for your reference. If (Range("B10").Value = "Gift" Or Range("B10").Value = "Entertainment") And Range("C10").Value = "" Then Range("C10").Interior.ColorIndex = 6 MsgBox "Please Fill in the Person's Name & Company." Range("C10").Select Range("C10").Interior.ColorIndex = 6 End If
View Replies!
View Related
Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"
Having problems with trying to get my vba code to access the SpecialCells property. Receiving the following error.... Unable to get the SpecialCells property of the Range class. The section of my code is below that is causing the error. Keeps stopping on the "Selection.SpecialCells(xlsCellTypeVisible).Select" line. Sheets(" Book Query").Range("A6:I6").Select Sheets("Book Query").Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlsCellTypeVisible).Select Selection.Copy Sheets("Inventories and Variances").Select Sheets("Inventories and Variances").Range("A7").Select
View Replies!
View Related
Receiving Error In Code "unable To Get The Find Property Of The Range Class"
I am receiving the error "unable to get the find property of the range class" in my code. I have attached my code and highlighted red where my error is occuring. On Error Goto ProductionRptEngineListExport_Error Dim objWrkTmp As Excel.Workbook Dim ObjWrkshtActive As Excel.Worksheet Dim objWrkshtTmp As Excel.Worksheet Dim RngStart As Excel.Range Dim CurrentRowNum As Long, RowCnt As Long Dim GroupRowNum As Long, ColCnt As Long Dim chtChart As Excel. chart Dim intwrktmp As Integer, intwrktmpTot As Integer, intLastRow As Long Dim sngwrk As Single ctlStatus = "Opening Production Report..." DoEvents........................................
View Replies!
View Related
Unable To Get The VLookup Property Of The WorksheetFunction Class...
I am trying to look at column at of two different workBOOKS and then when a match is found take the value from column D in workbook two and copy it to the corresponding row in the current column of workbook one. this is the code I'm using and I get that unable to get the vlookup function. I thought maybe it was because not all of them will have matches so it could return an error if it is trying to return empty, so I put int on error resume next, and it ran through but never brought over ANY data, and there ARE matches and data to be brought over. destRng is the range in the current column(that i just inserted) on the active worksheet that we will be putting the new info into. srcRng is the range in column A on the other workbook that the numbers are being compared to. src is just an integer
View Replies!
View Related
Unable To The Locked Property Of The Range Class
Sheets("PRODUCTIVITY").Select Range("A20").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select With Selection .Locked = False (***debug screen is highlighting this line***) .FormulaHidden = False .ClearContents End With Range("A20").Select ActiveCell.FormulaR1C1 = "PASTE NEW DATA HERE" Range("A20").Select This worksheet was built for use of many users so this sheet is PROTECTED. The user is instructed to copy/paste information from our internal web into the selected areas in the worksheet. When i UNPROTECT the worksheet the VBA works fine. The data in the worksheet spans from columns A to P, and the length (# of records) is variable.
View Replies!
View Related
Unable To Get The Correl Property Of The Worksheetfunction Class
I get the "Unable to get the Correl Property of the worksheetfunction class" Error when I try to run the following code CurrentRow = 2 CurrentColumn = 2 Finalrow = 5 WS2 = Sheet2 Finalrow2 = 5 Num_Records = 4 Correlation1 = Application.WorksheetFunction.Correl(Range(Cells(CurrentRow, CurrentColumn) & ":" & Cells(Finalrow, CurrentColumn)), WS2.Range("B" & Finalrow2 - Num_Records + 1 & ":B" & Finalrow2
View Replies!
View Related
Unable To Get The FindNext Property Of The Range Class
I keep getting that error when I'm running my macro. When I debug, it points me to the bolded line in the code below. The larger macro I'm running this function in runs this function some 101 times without error before this happens. The values of the parameters are as follows when it gives me the error: Find_Exact("hchen", ws1, "B:B"). The first parameter is the only one that changes in the previously mentioned running of this function.
View Replies!
View Related
Unable To Get The Find Property Of The Range Class?
When I try and run my macro I keep getting an error message saying Unable to get the Find Property of the Range Class and it refers me to: TeamRef = Worksheets("Info Reference").Range("A3:A120").Find(What:=strFind1, After:=Worksheets("Info Reference"), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Address
View Replies!
View Related
Unable To Get The Vlookup Property Of The WorksheetFunction Class ..
I have a UserForm with one ComboBox and one TextBox. The ComboBox is populated from another sheet with product codes in Col A and names in Col B. When the user selects a product code from the ComboBox the respective name appears in the adjacent TextBox. Once both boxes have been filled the user can then save the data to a third sheet. Now the problem arises when the user tries to manually enter a code into the ComboBox that does not appear in its rowsource - VBA states a run-time error and my new keyboard goes flying out the window again. Attached is an example sheet
View Replies!
View Related
Unable To Get The Find Property Of The Worksheet Function Class
I am using the "Find" in VBA and wanted to test if the value searched for could not be found. So I get the error = "Unable to get the find property of the worksheet function class" When the text cannot be found. Dim zz As Variant zz = Application.WorksheetFunction.Find("xx", "Hello", 1) I also tried Dim xx as boolean xx = Application.WorksheetFunction.IsError(Application.WorksheetFunction.Find("xx", "Hello", 10)) But this produced the same error.
View Replies!
View Related
Unable To Get The Sum Property Of The Worksheet Function Class
Here is the code that generates the error: Set rng = Range("I:I") LR = ActiveSheet.Cells(Rows.count, "I").End(xlUp).Row + 2 Range("I" & LR).Value = Application.WorksheetFunction.Sum(rng) Range("I" & LR).NumberFormat = "#,##0" and from what I've read on google the .SUM(rng) need to be set to a range, but isn't it already a range? I tried plugging in there Range("I:I") for rng and that didn't work either.
View Replies!
View Related
Why Do Pivots Display Pivot Item Tick Boxes That Are Not Visible In The Pivot?
I was wondering why a pivot table will allow you to tick and untick pivot items that are not visible? My pivot tables have a lot of data and someone may wish to only see the pivot items that are shown in the pivot table in its currrent view. Is there a way in VB to achieve this? In my attached example when you suntick the Media 'TV' then look in the 'Channels' drop down you can still see the tick boxes for TV channels 'ITV' and 'TV_BBC1' (I know why they are still ticked, I'd just like them not to be visible).
View Replies!
View Related
Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way. So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well. This solution also worked perfectly, but now I've been told to create the graph without macros. Does anyone have any suggestions? The requirements/details are as follows: 1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH" 2. The Pivot Table has two criteria - School Name and Year Level 3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
View Replies!
View Related
Unable To Set Formula Property Of Series
I went through a lot of threads in this forum, although there were a couple relating to the same problem am facing, they didnt solve my error .. so here's my question again .. When am trying to delink charts , i get an error stating "Unable to set the formula property of series class". The code is as follows : Sub DelinkChartData() Dim iCtr As Integer, iChars As Integer, SH As Shape, iPlotOrder As Integer Dim nPts As Long, iPts As Long 'Holds the total no of points in the chart Dim xArray, yArray, sChtName As String, sSrsName As String Dim xVals, yVals Dim ChtSeries As Series ' var used to loop thru the series collection Dim ws As Worksheet Dim sChartType As String, iCtr1 As Integer
View Replies!
View Related
Unable To Set The Orientation Property Of The PivotField
I want the data field to change to a different field when a command button (cmd_view) is clicked. So when the command button has a caption of "Toggle to Feeds", the code changes the field from Sqm to Feeds, and changes the caprion on the command button to "Toggle to Sqm". When this is next clicked, the fields are swithed over, and the command button caption changes relative to it. The code I have written is below: The problem I have is when the code changes the view from feeds to Sqm, it works fine, but when the code changes from Sqm to feeds, it crashes out in the true part of the If statement on the code line ".orientation = xlhidden" with the error "Unable to set the Orientation property of the PivotField class". As the code for the change of view is the same, apart from the field names, I am at a loss on what is the cause. I have tried to record a macro to see if the field names appear differently, but they do not. Sub Tog_pivot() Dim Toggle As String Application.ScreenUpdating = False Toggle = Sheet5.cmd_View.Caption If Toggle = "Toggle to Feeds" Then 'switch view from Sqm to Feeds Range("F12").Select ' hide existing field With ActiveSheet.PivotTables("Pivot_forecast_old").PivotFields("Sum of Sqm") .Orientation = xlHidden End With ' Create & display new field "Feeds"......................
View Replies!
View Related
Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open. 2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version? 3) am i going about this the wrong way with the objects? should i be after vba code?
View Replies!
View Related
Error 1004 Dget Property From Worksheet Function Class
I've noticed error 1004 is very broad and switches its name depending on what you're working with. But I have no idea what it means. This is the line w/ probs: Sheeti.Cells(y, 2).Value = WorksheetFunction.DGet(InventoryRange, 3, CriteriaRange) I looked at protection and Macro security and checked my definitions (that work in other contexts). Does DGET need to have criteria and database in the same worksheet? My project is working between two workbooks.
View Replies!
View Related
Set XValues For Series Class (Chart)
I'm getting an error when I try to set the XValues for a chart in my excel worksheet. The chart shows up fine and the new series adds fine though when I try to set the XValues the below error occurs 'Unable to set the Xvalues property of the Series Class' 1004. My Code is below. Can anyone see anything wrong with it? This could be a floor in my understanding of the chart object. I don't want to be selecting charts and working on an activechart because I'll have several in the one worksheet.
View Replies!
View Related
Add Calculated Item To Pivot Table
Attached is a worksheet example, of my beauty salon with 5 therapists. The input sheet - is the shhet into which I input data on a daily basis. Weekly inc - This sheet shows the total that each therapist made in apivot table based on the input. The week numbers are 1 to 52 (or 53) depending on each financial year (07/08, 08/09 etc). What I want to do is either have another pivot table or to add an calculated item to the weekly inc pivot table showing me how much commission each therapist took each week? This is to be done automatically by excel. This commission is calcluated by using the target comm figures - E.G. Leanne's target is £480 - so she has to earn £480 a week before getting a commission. So if in a week she earns £500 - she will get a commission on £20 (£500 - £480). The commission is 10%, so she will earn £2 that week.
View Replies!
View Related
Pivot Table Calculated Item Removal
I have cut down my problem to its simplest form - in the real world my spreadsheet is significantly more complex but the attached spreadsheet demonstrates it nicely! The data is straightforward. PT1 shows the data without a calculated item whilst PT2 is the same PT but with the addition of a calculated item for GP (i.e Sales - COS). Unfortunately adding this has resulting in the PT growing and adding "ilogical" combinations of data that didn't exist in the source data. In my current project I have "cheated" by using a helper column and filtering on this using the Worksheet_PivotTableUpdate event to hide the unwanted rows but surely there must be a way of removing them "properly". Another problem, and I guess this is just the way it is, is that my PT takes ages to calculate presumably becuase it is calculating this formula for every combination of values?
View Replies!
View Related
Change Pivot Item With Named Reference
I'm trying to create code where a user can input criteria into a cell then run a macro. The macro would then use that criteria to filter the pivot table. I have attached an example. The first portion, using date to filter the current page - pivot item is working. I need to creating the code to use the "supplier" to filter the pivot items on the row. As a note, both the Date and Supplier cells are named references.
View Replies!
View Related
Stale Pivot Item Values After Query Change
I have a spreadsheet with a pivot table that is based on a table within an SQL Server database. Every month a stored procedure is run creating a new table. Using the pivot table wizard I can modify the underlying query to access the new table. The pivot table functions properly with the new data. I also have a macro that I created that will cycle through all the combinations of page field pivot item values, setting the page field currentpage, do some calculations, and save the results. This is bombing because it seems that some old date values from the previous month's table are still in the pivot items and when my macro attempts to set the page field's currentpage to one of these values it generates Run-time error '1004' Unable to set the _Default property of the PivotItem class. I have tried putting a pivotcache.refresh in but that doesn't seem to help.
View Replies!
View Related
Pivot Table, Calculated Item.... No Data, Still Creates A Row
I have a Pivot table. Layout: Rows: City, Restaurant, Cashflow Column: Date (Month/Year) DataField: $Amount "City" might be 7 cities "Restaurant" may be 32 restaurants, some in some cities; but not other "CashFlow" can be either "Revenue" or "Expense" The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist. Now.... the trouble... I added a Calculated Item, "Profit" which = "Revenue" - "Expenses". Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries. How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination? Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?
View Replies!
View Related
Class With Property As Collection Of Another Class
I've created a class called CStock. It has 5 standard properties (with Get/Let methods) as explained in that article above. Nothing fancy here. I also want to create a CPortfolio class that has 3 properties: pName, pNumberofPositions, and pHoldings. the first 2 are stardard, but the last one is different. I want it to somehow keep a collection of stocks.
View Replies!
View Related
Creating Pivot Chart
I am trying to create a Pivot Chart. My data consist of staff whom had errors in their work done. The data has a time when the work was completed by the staff. I would like to generate a Pivot Chart that has a line chart that shows when the work was completed based on the time and can be sorted easily by team, date, staff ,shift and errors.I know the easiest way is by Pivot Chart. I am not sure how to accomplish this as I had tried with the Pivot Chart and it did not work.
View Replies!
View Related
Pivot Chart Layout
I'm trying to Create a pivot chart from Data. I've attached a sample of the data I'm using. What happens when i try to create a chart is test 1-9 get assigned to Open which is fine. I can then drag Open to the position in the chart i want using the layout feather. I could then uncheck test 7 if i didn't want it or whatever. I want that same process to run horizontally for the numbers from 1-10 to go under the header WW so that i can unclick any WW that i don't want in the chart if you get my meaning. I included an example of what i'm talking about. Sheet 1 contains the raw data and Sheet 5 contains a small explanation.
View Replies!
View Related
Pivot Table Chart
Is it possible to use the chart wizard for a Pivot Table yet maintain that chart in the same worksheet and not have it in another? Every time I click on the wizard a chart is created on a separate page.
View Replies!
View Related
Convert Chart Into Pivot Chart
Please look at the sample workbook below. The chart you see below is fine BUT I would like in a pivot table chart, so I could select any days on the pivot chart. I have tried it to do it myself but for some reason the time is not displaying on the pivot chart correctly. Any help please ? P.S. If not possible then can we add a scroll bar or something?
View Replies!
View Related
Pivot Chart Changes Chart Upon Refresh
I have created a pivot table and chart. The pivot chart consists of three sets of data from the pivot table. Two of the data sets are lines and the third data set is displayed as columns. I made the third data set into columns by right clicking on the data series line and selecting chart type. I change the chart type for that data set to a column and it worked great. The only problem is that when data is refreshed in the associated pivot table, the entire chart turns into columns with completely different formatting. Does anyone know how I can maintain the graph with the two lines and one column data set when I refresh the data?
View Replies!
View Related
Blank Data - Pivot Chart
Have an auto-calculator sheet on..mean formulas are input in rows (to calculate yield from no. inspected & no. found defective)....am running a pivot chart based on it to calculate no. of defectives by product and date... The issue is had selected the entire worksheet for the pivot (including the rows with no data input as of yet but will be inputed in the future)...the problem is the pivot data gets all messed up because of the blank inputs...as soon as i select only the area with data input it works fine.....as of now have put in a generic format so that it reads it...but is there a way where i can leave it blank and still the pivot will refresh and calculate the data when put in???? current format is date-week-no. inspected-no. defective-yield...have input 1 jan 2001 as default date...and 0 and 0 as default no. inspected and no. defective....do not want to keep on doing this...
View Replies!
View Related
Pivot Table Chart Differences In 03 And 07
I have attached a portion of my Pivot Chart in Excel 2003 and have been trying to recreate this in 2007 with some difficulty. I am particularly interested in displaying the field list headings (specifically 'Count of Hit or Miss') in the pivot chart in 2007 but cannot make it work.
View Replies!
View Related
Pivot Chart Type Changing
I have an issue with pivot charts that I can't figure out and I can't find anyone with the same problems either. I have several big reports that use data from 2005 onwards that need to be represented on graphs separated by the city the data belongs to and different ranges of the results. This data is being sourced from inside each workbook on another tab and turned into a series of pivot tables (for each city) which provides the source for the charts. I did it like this because if I use pivot graphs I don't have to go through and individually change the data source range in the charts every time - because there are so many. My problem is that when I refresh the pivot tables (to bring in the new data) the pivot charts 'chart type' resets. They should be represented in a Line - Column on 2 Axes chart and I have set this as the defult type but after refreshing it reverts to a basic column graph and I have to go through and change them all back manually. I made a macro that acts on a currently selected chart to change the format to make this process a bit faster:
View Replies!
View Related
Incorrect Percentage On Pivot Chart
If I simply publish the pivot chart, the correct percent values are displayed on the y-axis. However, when I publish it with the interactivty option, the percent values are wrong. For example, the actual value is supposed to be 13.7% but graph shows 46496000.00%. The other oddity is that the table published in interactive mode has the correct values.
View Replies!
View Related
Hide Zeros On Pivot Chart
I have a PivotTable linked to a Bar Chart. (see attached JPG for example) I do not want the 0% values to show in the chart at all but I do want to see all of the other percentages. I have been able to hide these values in the PivotTable itself by Conditional Formatting or custom number formats but they still show up in the chart!? how I can get the 0% values to be hidden or not show on the charts at all? I don't want to have to do this manually for every 0% that shows because the data changes daily dynamically. A VBA or Macro solution would be preferred.
View Replies!
View Related
|
TRACKER
