Change SourceData For All Pivots In Workbook While Disconnect / Connect Slicers?

Jan 28, 2014

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) ...

PHP Code: 

    Max = Sheets("eLink_Raw").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Quantrix").PivotTables("Quantrix 1").ChangePivotCache ActiveWorkbook. _
      PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Sheets("eLink_Raw").Range("A1:AW" & Max).CurrentRegion _
      , Version:=xlPivotTableVersion14) 

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?

View 14 Replies


ADVERTISEMENT

Inexplicable Subscript: Find That LBound(sourceData) = 1 And UBound(sourceData) = 11, As Expected

Apr 20, 2007

I have set up a worksheet to demonstrate how arrays work--and find that I need to learn more about the topic myself! The code below reads data from Column A into an array, sorts it, and then writes the sorted data to Column B. In the example I have created there are 11 data values in Column A. When I break at the indicated line of code, I find that LBound(sourceData) = 1 and UBound(sourceData) = 11, as expected. However, at the indicated line of of code, when i = 1, the reference to sourceData(i) results in a subscript error.

Public Sub AddSortedColumn()
Dim sourceData() As Variant
sourceData = Range("A1:A" & Range("A65536").End(xlUp).Row)
Dim swap As Boolean
Dim i As Integer
Dim save As Variant
swap = True
Do While swap
swap = False
For i = LBound(sourceData) To UBound(sourceData) - 1..................

View 5 Replies View Related

Multiple Pivots With The Same Row - How Do I Make Them Change At The Same Time

Oct 22, 2009

I'm using Excel 2003 for this project.

For example: -
I have 3 pivot tables with the same row 'Media' (TV, Neswpaper, Magazine) and each has count as the data field (number of people who read/watch) then each pivot has a different column, Region, age, ***.

If I change the row of the first pivot to only show TV, how do i code it so pivot 2 and 3 only show TV also? I may have upto 20 pivots inthe end.

I found the below code to do this for page fields but can't adapt to rows -

HTML Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, ptItem As PivotItem, vFields As Variant, lngField As Long
On Error GoTo ExitPoint
vFields = Array("filed1", "field2", "field3")

Application.EnableEvents = False
For lngField = LBound(vFields) To UBound(vFields) Step 1
For Each ptTable In ActiveSheet.PivotTables
If ptTable <> Target Then
With ptTable.PivotFields(vFields(lngField))
.CurrentPage = Target.PivotFields(vFields(lngField)).CurrentPage.Value
End With
End If
Next ptTable
Next lngField
ExitPoint:
Application.EnableEvents = True
End Sub

View 14 Replies View Related

Updating All Pivots When Opening Workbook

Dec 19, 2008

I have a spreadsheet with a number of sheets in it for our sales team to record orders received and pending; the first sheet contains pivots which relate to the other sheets (a sheet for each financial quarter) (i.e. one pivot for sheet 2, one pivot for sheet 3 etc)

Is there a macro which will allow me to update all of the pivots when the workbook is opened? Or, even better, one where they will be updated when the first sheet is accessed?

The only one I have found when searching is:

View 14 Replies View Related

Chart SourceData Code

Jun 14, 2007

Creating a vba program that automatically generates charts. Looking for the easiest way to get the range of the data that the chart is using (later in program). Noticed the SetSourceData method but dont see a GetSourceData (that returns a range).

View 3 Replies View Related

Excel 2010 :: Using Slicers On Tables?

Apr 11, 2013

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.

View 5 Replies View Related

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.

Code:
newaddress = "$B:$" & Mid(Cells(1, YTDcolumn).Address(), 2, 2)
.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TOP250Excel!" & newaddress, _
Version:=xlPivotTableVersion14)

Here is my code for reconnecting the slicer connections.

Code:
ActiveWorkbook.SlicerCaches("Slicer_Top250Excel_cypheren_Query_Name"). _
PivotTables.AddPivotTable (.PivotTables("PivotTable1"))

View 1 Replies View Related

Excel 2010 :: How To Cascade Slicer Selection To Other Slicers

May 16, 2014

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

ActiveWorkbook.SlicerCaches("Slicer_DimDateBill.DateHierarchyFinancial1"). _
VisibleSlicerItemsList = Array( _
"[DimDateBill].[DateHierarchyFinancial].[Fin Month].&[201403 FM09]")
ActiveWorkbook.SlicerCaches("Slicer_DimDateProcess.DateHierarchyFinancial"). _
VisibleSlicerItemsList = Array( _
"[DimDateProcess].[DateHierarchyFinancial].[Fin Month].&[201403 FM09]")

Also it should disallow multiple selects, is there code to monitor that as well?

View 3 Replies View Related

Connect 2 Listboxes

Feb 28, 2014

I have a userform with a listbox that has 6 columns (D2:I50) which works great. In column J (J2:J50), I have data which needs wrapping so I plan on putting it in its own listbox. Is it possible to have another list box but one that operates as the first one is scrolled?

View 2 Replies View Related

Connect Nested Ifs

Jul 1, 2008

I have more than seven if statements but don't know how to connect them.

works
=IF(C2=$A$2,13,IF(C2=$A$3,7,IF(C2=$A$4,5,0)))works
IF(C2=$A$4,,IF(C2=$A$5,3,IF(C2=$A$6,3,IF(C2=$A$7,3,IF(C2=$A$8,3,IF(C2=$A$9,3,IF(C2=$A$10,3,IF(C2=$A$11,3,0))))))))can these be connected? they work for me separately but not all in one.

I've tried Named but that doesn't work because there are way too many things I need to do w/ it.

View 9 Replies View Related

Connect Two Circles

Feb 28, 2008

Im writing a program in VBA excel that displays two circles in excel spreadsheet, these two circles have been set X and Y Co - ordinates. Using the X and Y Co - ordinates i want to connect a line from shape 1 to shape 2

ActiveSheet.Shapes.AddLine(495.75, 234#, 682.5, 234#).Select

View 3 Replies View Related

Connect To Access Database

Nov 6, 2007

I was trying to move data from excel to access database in VBA. not sure if this has been done before.

What i have got at the moment is that there are some data in excel spreadsheet that i can dump into the table in the access database. My problem here is i need to be able to open the database first, set up connection, and then perform SQL insert query command.

So in the worksheet, i have a button with the following codes in it
so far i could just manage to open the database as follows

View 13 Replies View Related

Connect Direct To The Newsgroup

Nov 25, 2005

Is there a way to connect direct to the newsgroup below via OE6:
microsoft.public.excel.worksheetfunctions
It doesn't appear in the newsgroups' list ..

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

View 13 Replies View Related

Connect To Access Using ODBC

Apr 17, 2009

how to do this with Java but not VBA, I have the capability of understanding it I just need to know where to look because I can't seem to find any solid information on the net from my searches. Does anyone have any websites, other threads or anything they could direct me to for this kind of thing.

I was contemplating putting this in the Access thread but I want to connect to Access through Excel so I think it fits both.

My plan is to use a macro to send specific cells to five seperate tables in an Access database using VBA and SQL.

View 9 Replies View Related

Checkbox Won't Connect To Linkedcell

Apr 4, 2007

I've created a button on a sheet that triggers the creation of a checkbox on the ActiveCell. I also want the checkbox to link to the activecell in order to use the TRUE or FALSE values in an AutoFilter. I used the following code, but the checkbox doesn't link to the ActiveCell. I thought it might be because the LinkedCell = "TEXT" whereas a Range isn't text?

Sub AddHoekCheckBox()
Dim MyRange As Range
Dim CBLeft As String, CBTop As String, ctlName As String
Dim cbObj As OLEObject
Dim MyDoc As Worksheet
Set MyRange = ActiveCell
CBLeft = CallByName(MyRange, "left", VbGet) + 0.25 * CallByName(MyRange, "Width", VbGet)
CBTop = CallByName(MyRange, "Top", VbGet)...........................

View 3 Replies View Related

How To Connect To Unix OS On Solaris Server

May 28, 2012

I am trying to get some data on a excel. This data is available on a unix OS solaris server.

The data is available in tabular format on the txt file in the server and i need to populate this data on my excel sheet.

How to connect to unix from excel and how to get the data.

View 1 Replies View Related

Macro To Connect To Infomaker And Run Query

Apr 17, 2014

I'm trying to get my macro to connect to Infomaker and run a query. I have successfully done this in the past by using the record macro function. But, when I try to record the macro and paste the query syntax from Infomaker into the commandtext box of the connection, I get the "too many line continuations" error.

I've been looking at ways to write the code rather than record, but the syntax for the Infomaker queries doesn't seem to mesh well.

The syntax of the query in Infomaker is (copy/pasted, all "'s are necessary):

Code:

SELECT "COMPANY"."CO_NAME",
"COMPANY"."FLAG",
"CO_REF"."CO_VALUE",
"ITEM"."ISSUE ",
"REFERENCE"." ID_VALUE",
MIN(CODE.PROD_CODE)
FROM "COMPANY",

[Code] ........

View 2 Replies View Related

Use SQL To Connect An Excel Document To Database

Feb 26, 2007

Some time ago, a friend of mine told me he didn't use any Pivot Tables at all, due to the imense space they require.

Instead, he made connections between Forms in Excel and the Databases using SQL.

Do you know of any Internet site where I can start to learn something about this?

View 9 Replies View Related

Connect Live Data In Sheet

Dec 30, 2009

How To Connect Live Data in an Excel Sheet?

I want to track changes in some Stocks, I have made a sample file in Excel and would like to make it Live by Connecting it to a website..

However, I do not know how to go about the same?Can some please provide some examples as well as a File if possible explaining how it was done..

View 9 Replies View Related

VBA Code To Connect To BMC Remedy And Fetch Report?

May 9, 2013

I have a requirement to pull report from BMC remedy using VBA. I am sure that many people would have achieved this. .

View 1 Replies View Related

Excel 2010 :: How To Connect To MS SQL 2008R2 Using VBA Coding

Feb 12, 2013

I have 2010 Excel and MS SQL 2008R2.

I'd like to import data from my stored procedure from MS SQL into a cell in Excel....

No pivot.

I looked for a standard coding all over the place - no success.

View 1 Replies View Related

Formula In Destination Workbook Will Not Update When Change Tab Name In Source Workbook

Jul 17, 2014

I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.

this is a portion of the forumula I am working with:

=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4

View 9 Replies View Related

Excel 2010 :: VBA Code To Connect To SQL And Pull Records To New Worksheet

Oct 18, 2012

i found this code...

Code:
Sub Button1_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim SNfound As String
'Your sqlserver 2008 connection string
Const stADO As String = "Provider=SQLOLEDB.1;" & _
"" & _

[code].....

but i dont see where to put in the Database object...

The Database it needs to connect to in SQl is called

The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal

using Excel 2010 connecting to SQL 2008

View 4 Replies View Related

Controlling More Than 2 Pivots

Sep 10, 2009

Following up on a tread from Sailor64, I tried to use a code DonkeyOte created,

View 2 Replies View Related

Multiple Pivots

Jun 6, 2008

Can someone direct me on how to make multiple pivot tables from values that are in a column?

View 9 Replies View Related

One Filter For Multiple Pivots?

Aug 14, 2014

I have 2 separate pivots running off 2 separate data sources. The filters used for each or fairly similar (bar one entry).

Is there a way to have one filter that filters the information in both pivots even though they are not based on the same data source?

Also, with the filter that is slightly different; in one table, I have an option that shows (this is just an example) 'retail and end user' yet, in the other table these are two separate options.

View 4 Replies View Related

Controlling Multiple Pivots

Aug 14, 2009

I am working on an excel sheet (2007) with about 8 pivots from the same data (huge db). The 8 pivots take a different cuts of data. One filter element is same across all pivots. (For instance let us say that filter is City names: with 10 different city values in it- NY, London, Chicago etc)

What I would like to do is have a system where I just chose NY in one place and all the 8 pivots should use NY as one of the filters and refreash the data accordingly.

What I am doing now is I got to each pivot, choose NY first and then refreash all for the data.

View 14 Replies View Related

Multiple Pivots One Filter Etc

Oct 16, 2009

I am looking for a way to combine three pivot tables in to one. Basically, one sales rep will have data for the current month, the year to date, and the total year. I have to compile this data to one sheet, for 30 sales managers! The problem with running the pivot three times (once for monthly, year to date, and yearly) is that brands that aren't sold in the month are then left off the table, which leads to a mess that it takes far too long to clean up. I hope I've made this clear, but please check the attachments for a better idea of what I'm looking to do...

View 9 Replies View Related

Summary Analysis Without Using Pivots?

Feb 10, 2012

I need to summarise a batch of data in this format....

YearPeriodAccountCustomerPart NumberSalespersonChannelValue2011Jan1SteveA1KylieHome1502011Feb2BillA2KylieExport1802011
Mar3FredA3KylieHome2002011Apr4JohnA4KylieExport1002011May5AndyA5KylieHome85

And need to put it in the following format...

Salesperson - KylieJanFebMarAprMaySteve150Bill180Fred200John100Andy85

I know I can use a series of pivot tables but would like to keep the size of the file to a minimum.

Is a DSum the way to go or can I use a Sum if and use an and function with the sum if?

formula if so to saveme a bucketload of trial and error attempts...

View 2 Replies View Related

Pivots - No Drop Down Arrows

Jun 8, 2007

I have been sent a spreadsheet with data in it that is from a Pivot table.

The headings look like the ones in the Pivot table the only difference is
there are no drop down arrows to show/hide items

I cant click and drag fields to swap them.

So it looks like a Pivot but I cant manipulate it.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved