Can't Reconnect Slicers After Changing Data Source
Feb 11, 2014
I have a workbook with some pivot tables connected to a single slicer. My macro needs to disconnect the pivots from the slicer, change the data source, and reconnect the slicer. When my code gets to reconnecting the slicer it bugs out as if the data source is not the same for all the pivot tables (also doesn't let me reconnect manually). But when I look at the source, it is all the same and correct. If I manually set the data source the slicer reconnects just fine. Why is my code causing problems with the slicer?
Here is my code for setting the data source. Pretty sure this is the code that is causing problems for me.
I am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.
I've a set of excel sheets reading from a single excel data source, the only problem is that the data source is constantly changing, for example:
=DCOUNT('T:GreenbeltProductivity ToolWeek 42[Dept Overall and Data Entry.xls]Insert Data - Current Position'!A:F,5,AC139:AD140)
The week number is the only bit of the code that needs to change week on week, to update the data source on all the sheets and I'd like to password protect the data source.
I'm trying to change the source data of an existing PivotTable so that it is linked to a raw data source in another workbook. The workbook it pulls the data from changes every month so I made this flexible code that is meant to pull the raw data every month:
Sub ImportNewSource() Dim Filt As String Dim FilterIndex As Integer Dim Title As String Dim FilePath As Variant Dim ThisPivot As PivotTable Dim FileName As String Dim ShtNum As Integer Dim LastRow As Long
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
When I do it manually, it works just fine. There aren't any changes to the PivotTable field or anything like that so I'm stumped. I've tested the range that has the source data and its selecting the proper range and when I do it manually it works fine.
Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.
I have the following code to update a pivot table:
Code: Dim pt As PivotTable Application.EnableCancelKey = xlDisabled For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _ , Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
I'm trying to create a chart with different source ranges e.g if i used
With ch.Chart .SeriesCollection.Add _ Source:=Worksheets(1).Source:=Worksheets(1).Range("C1:C8,J1:J8") End With
That works fine but I want to modify the columns it looks at. I was going to use the cells(x,y) method but I can't get it to work. icol = 3. Range(Cells(1, iCol), Cells(8, iCol)). This works for a single range(C1:C8), but how do i reference Range("C1:C8,J1:J8") in the same way.
I am trying to monitor the status of a cell on another sheet and autopopulate a cell depending on that information. The formula works well until I give the spreadsheet to a 3rd party and the formula ranges change after they paste new data to the source sheet. I have tried locking and password the formula cells but they change range regardless!
Here is the formula from the first cell.
[Code] ......
It scans for a number in an adjacent cell. If the number is present on the sheet 'Test', it autopopulates the cell with a string from the source sheet. The cell remains blank until there is data present.
If I cut data from row 6 and paste it to row 17, the formula cahnges itself to:
[Code] ........
How I can lock down this formula so that the ranges remain the same i.e. $AT$6:$AT$26, despite changes on the source sheet? I have tried F4 to toggle relative and absolute references and this has made no difference.
I want the label in a chart to march the font color of the source cell
So in a chart I'd have 4 labels: "apple" "banana" "peach" "pear"
I can't see how to do it. When i select the albels it's "all or none". I can change ll the labels to a color. But, I want each individual label to be a diferent color.
Is there any way to use slicers on a table in 2010? I've seen posts that it is available in 2013, but can't find find a definitive answer on if there is a way to get it to work in 2010.
I've inherited a workbook that has about two dozen or so pivots spread over a few worksheets that source data from an analysis services cube.
There are multiple slicers attached to the various pivot tables and charts
Some of the reports (worksheets) have a business rule that certain filters need to be selected in specific combinations for the data that's returned to make sense.
eg the fact table holding the measures has a billmonth and processmonth that's linked to role playing date dimensions. For the report to reconcile the data correctly both the billmonth and processmonth need to be set to the same value. So if I select 2014-03 on the billmonth slicer, I need to set the processmonth slicer to 2014-03 as well. And there's another pivot on the same worksheet that's linked to a different fact table that's at the year grain, and for that bit to make sense it should be set to 2014.
My task is to simplify this by propagating the billmonth value to the processmonth and the billyear, but I haven't played with vba in about 7 or so years, so I'm very out of practise.
How do I use vba to monitor a slicer for changes? and if it does change how to set another slicer to a dynamic .Value? It can be assumed that the value will always exist in the downstream slicers, if it doesn't the user has bigger problems than an excel error.
I've recorded the macro of me selecting the same date on both slicers but it doesn't give me much to go on
I have 6 Slicers in called "Quantrix" The slicers are called REG, DIRECTOR, AREA MANAGER, AOI, DMA, AG and CLOCK. I have 15 pivots (6 in Quantrix tab and 9 in a tab called "Pivots").
I have vba code to update all pivot cache (showing 1 pivot update below) ...
Here is the code to disconnect SLICER, update source data for ALL pivots then reconnects slicer... This works great but its ONLY 1 SLICER
PHP Code:
Dim vPivots As Variant Dim i As Long Max = Sheets("eLink_Raw").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.SlicerCaches("Slicer_REG").PivotTables
[Code] .....
How can i disconnects ALL Slicers, change source data for ALL Pivots then reconnect ALL slicers?
I have been working on a workbook that uses as a source information on Sheet 1.
Sheet 1
Each item reports a number of different details from column A to column BY.
Each row is for a different item.
25 rows of information.
Sheet 2
This is my master formula sheet, the one I've been using to create the page that reports and formulates information on a single item. The source for this sheet is taken from the cells in Sheet1!Row2.
Now that I have the sheet pretty much finished, I want to paste the same report on each succeeding page for a total of 25 sheets. I can copy Sheet2 and paste into each successive sheet, but then I need to change every single formula so that where it said AT2 it changes to AT3, where it was H2, H3, etc., from A to BY.
Problem: Can I use something that will automatically take the information for succeeding sheets and take as its source the succeeding row from the source page?
I don't know that I'm describing that sufficiently, so:
Sheet 3 would take as its source Sheet1, row 3. Sheet 4 would take as its source Sheet1, row 4. Sheet 5 would take as its source Sheet1, row 5. Sheet 6 would take as its source Sheet1, row 6.
Etc., etc.
In the past I used the find/replace function, but this sheet seems way too complicated for that. As an example, one cell alone on my master sheet is
so I would need to change S2 to S3 everywhere it appears, but there are probably more than a hundred places on the sheet that I'd have to manually change 2s to 3s, etc., for 25 sheets, which is why I'm wondering if there isn't a better way than going cell by cell, sheet by sheet.
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
I can still do a pivot report against the 'saved' data. So it must be out there in a file...? I read the function GETPIVOTDATA. Based on the description, It sounds like it would work to rebuild the source data. However the examples given don't see to match up with that. I have tried playing with the command, but I really don't know what I am doing and have had no luck
Does anyone know of a way to retrieve that stored data that the pivot reports are reporting against?
I used to get data from external excel, access files and today tried to put together query to get data from CSV file. While using same source file, I have creted several queries to get data out based on criteria. For some strange reason, same structure queries that did run with excel or access as data source, does not want to work the same way with CSV files.
Here is example of non working query SQL: SELECT Vendors.Vendor, Vendors.Name, Vendors.`Name 2` FROM `Q:FinanceRapporterPSAPRaw`Vendors.csv Vendors WHERE (Vendors.Name Like '%' & ? & '%')
If I enter "London" as a criteria input, it will not work. However, if I enter "ondon" it will find me all values records whre name contains London... Seems like I need to skip first symbol when entering criteria. This is, of course, not something I would like to do.
I have a combobox in which I need data loaded from a column of an excel sheet.
As of now, I am using the below Last = Sheet9.Cells(Rows.Count, 1).End(xlUp).Row With ComboBox1 For Row = 2 To Last .AddItem Sheet9.Cells(Row, 1) Next Row End With Is there any other way to load data?
I have tried putting in the Row Source in the Properties of ListBox1, (A10:A13) but the range is in another worksheet called Codes. So when I run the Form the list box shows the range A10:A13 in the current sheet. I have named the data list SP but when I punch that in it will not accept it in the Row Source. How can I lock in the source data in the list box so it only shows the named range SP.
I want to design a spreadsheet report, but instead of importing the data from an external source, i would like the data to come from another sheet within the same workbook. The goal is that the data sheet will contain only raw data with no formatting, and the report sheet will contain all the formatting.
