Select Fields In Slicer With Macro
Apr 10, 2014
I have two slicers, say slicer 'F' that slices on fields 'x', 'y' and 'z' and slicer 'E' that slices on fields 'a', 'b', 'c'.
I select these values by using a very simple macro. For instance to select z in slicer "F", I have the following code:
With ActiveWorkbook.SlicerCaches("F")
.SlicerItems("x").Selected = False
.SlicerItems("y").Selected = False
.SlicerItems("z").Selected = True
End With
However, I want the selected fields to have more flexibility than which is possibly by coding them in VBA. Ideally, I would want to write the selected fields in a cell in Excel (also if there are more of them!), then let the macro retrieve these fields and use them to apply to the slicer. So I write in a cell slice on X and Z, the macro picks this up and performs this operation.
Is this possible?
View 1 Replies
ADVERTISEMENT
Mar 28, 2014
I' have two slicers that each control two pivot tables. The slicers both are for the same field ("Department") but I guess the pivot table structure differences don't allow me to simply have one control all four tables.
Since both slicers contain the exact same options from the same field on the same origin table, I would like to problematically ensure that when department "A" is selected on slicer1, it is also selected on slicer2. When departments "A, B, & C" are selected on slicer1, the same are selected on slicer2. When filters are cleared... you get the picture.
View 5 Replies
View Related
Feb 20, 2014
I created a macro to create the beginning of a pivot table- just the rows and slicers, because when I tried making a longer macro to create the entire pivot table (formatting, etc) I couldn't get the macro to work.
So I do get all the vertical rows I want, and I do get all the slicers I want. But I get blank space. I think this is because the pivot table is created from another tab, and the length of data in that tab will vary from file to file (I'm doing another 30 or so of these, all with varying amounts of data, but the same data categories).
Anyway, I get slicers, but in addition to getting the categories I want, I also get "(blank)" below the names in the slicers. Is there a way to add some code to the macro to remove the blanks so I don't get this field?
I have copied and pasted the macro below, with identifying data changed.
View 4 Replies
View Related
Apr 11, 2008
I have a workbook with sheet 1 called Support which is basically an input form. The second sheet is called Database.
I want to enter data into the form and via a macro copy the data to the database. I have followed a text book to the letter on how this is to be completed to the letter.
My problem is that when the data is written to the database some of the fields are blank. Using F8 I can step through the VB script which appears to select fields at random.
View 10 Replies
View Related
Feb 20, 2014
I have a spreadsheet with a heap of raw data in it. I need to perform a search based on a customer name, then return certain columns from the raw data IF the customer name is matched in one column, AND a special flag name is matched in a second column. The customer name is always a single entry for the purpose of the query, however the special flag must be checked against a possible list of values in a table. I know this is hard to explain, and I can't add attachments to my posts, so I have created a sample document and placed in in dropbox:
[URL] ........
View 9 Replies
View Related
Oct 8, 2009
I need some assistance in creating a MsgBox when an excel spreadsheet is opened and more specifically a particular sheet is opened. I'd like the Msgbox to say "Do not forget to select frequency in the blue shaded fields." I have read a guide which says just add a module in the spreadsheet eg.
Sub Msgbox ()
Msgbox "Do not forget to select frequency in the blue shaded fields."
End Sub
View 4 Replies
View Related
Dec 3, 2013
Im using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?
View 1 Replies
View Related
Jan 31, 2014
I have a list box contains of customer name and I used that to set up formula based on your selection in the list box. However, I wonder if there is a way that can select multiple customers in the list box and it will add up all that customers from your multiple selection like in the pivot slicer. I don't know if we have any formula that can do that complicated thing.
View 2 Replies
View Related
Jun 12, 2014
In my pivot table I have "Due Date" with is a column label.
I grouped the dates into weeks by using the "days" grouping and using 7 days.
I created a slicer to allow the user to select with week they want, but the slicer is sorted as though it's a text field and not a date field.
Example:
6/1/2015 - 6/7/2015
6/13/2016 - 6/19/2016
6/15/2015 - 6/21/2015
6/16/2014 - 6/22/2014
I want the slicer to sort by actual date.
View 1 Replies
View Related
Dec 31, 2013
I am currently working with a pivot table someone else created. The data set has dates only, in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.
Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.
How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
View 3 Replies
View Related
Jun 12, 2014
The main objective of the code is to create different workbooks that are going to be send to different people(with only their data).
I am using the filter to delete the data that I don't need and then create a new workbook, save it and close it. This has been working fine.
The issue is that I have two slicers in the 'DistMenu' sheet. When I open again the newest workbook, they slicers are disconnected to the pivot tables. This can be fix manually by selecting the slicers --> PivotTables Connections etc
I use the macro recording to get a code and work from there, but is giving me error '1004' "Unable to get the PivotTables property of the Worksheet class".
View 1 Replies
View Related
Jan 8, 2014
I need to transfer selection(s) from slicer Country to Country1 (they cannot be connected due to workbook functionality).
User can make a single or a multiple selection in the country slicer.
So basically using CUBERANKEDMEMBER I got the output from slicer Country (in cell A1 for now) and used it as input in VBA for slicer Country1
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]")
I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)
Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.
So, if the user selected 2 countries I would need to run a following code:
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]", "[01_Feed].[Dosage].&[" & Range("A2"]")
Now, is there any way to loop this within the array, how many cells it should take?
For i = 1 to ..
code from above
Next i
won't work because then it only takes the last value, so I kind of have to loop it within the array.
View 2 Replies
View Related
Nov 6, 2012
I have an issue with trying to write a macro to apply a filter and then delete all lines.
Within the macro I copy and paste one sheet to a new one, and Paste Special Values over all formulas, leaving me with a column that has some figures and some "#N/A".
i.e.
13.75
12.00
#N/A
14.00
So I need to do something in the macro that then applies a filter on this column and deletes off all the #n/a results.
This table has a number of columns not just this one so it is the full rows that need removing.
I see on other posts that using a range.specialcells clause could work, but i'm not too sure exactly on how to apply this.
View 1 Replies
View Related
Mar 24, 2009
I will paste data into columns A-H, I have headers in rows, 1,2 and 3. I have formulas in a few cells in row 2. After inputting my data on the sheet etc I copy all the formulas from row 2 and paste in teh relevant columns, I then select my autofilter and my selection (in this case #N/A), i then choose all visable cells (Alt and ;)and delete them. The problem is I do not want to delete rows 1,2 or 3 but all other rows (i.e. just the selected N/A rows). Can anyone help on how to do this?
Once i have figured this out I will then continue the macro to select 'all' on the filter and do the same to a few other columns.
So far I have this;
Sub Macro6()
'
' Macro6 Macro
' Macro recorded 24/03/2009 by Richard Tydeman
'
'
Selection.Copy
Range("I4:I5000").Select
ActiveSheet.Paste
Range("J2").Select
Application.CutCopyMode = False
Selection.Copy
I think the extra bit of code needs to go in after selecting visible cells - something that says exclude row1,2 and 3 but im not sure.
View 9 Replies
View Related
Nov 25, 2013
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
View 4 Replies
View Related
May 2, 2013
I have multiple pivot tables with different data source. I wish to have one slicer which control all the pivot tables. I would have one common colum for all the pivot tables which is the one i wish to control for instance the person in charge. Note that all my pivot tables are from different data source. how to do it?
View 1 Replies
View Related
Sep 26, 2013
I have two pivot tables, both of which source the same sheet of data. Each record in the data has two fields, Region ID of person and Region ID of facility, that reference the same list of Region IDs via vlookup (it's just numbers 1-12). Either one or both can be blank. One pivot outputs counts by person Region ID, and the other, by facility Region ID.
Using a combination of nested IF statements and vlookups, I tried making a third Region ID field that could be used as a slicer to control both tables, but what I end up with is an undercount in one of the tables. The only thing that works so far to output the correct counts is having two separate slicers, the facility Region ID being the slicer for one and the person Region ID being the slicer for the other. If the end user wants to see counts for one Region ID, they have to manually set one slicer equal to the other. But what we want is just for the end user to be able to control both tables just by pushing a single number, Region IDs 1-12.
I can make one of the slicers hidden but then how do I get the hidden slicer to automatically select Region ID values equal to the nonhidden slicer? Alternatively, I could somehow program a combo box or list to control the two different slicers, then the slicers would be hidden and the user would see only the combo box/list. (I guess in either of these alternatives, I could just use a report filter instead of a slicer; either way, I still have to get items in one to automatically select based on the user's selected items in another).
FYI, some of the options I looked up involved PowerPivot, which I do not have access to. VBAs/macros are ok.
View 2 Replies
View Related
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
Jul 19, 2012
I am trying to create a "dashboard" style report using a pivot table and pivot chart with slicers. All of these objects (table, chart and slicers) are in the same worksheet. However, the users of the report (read executive management) are not very Excel savvy and I want to protect the format of the report. So, I want to protect the sheet and only allow access to the Pivot table options. However, in order to make the slicers usable, it appears that I have to allow them to edit objects. This unfortunately means that they can then move those objects (the Slicers and Chart).
I am looking for a way to lock the slicers and pivot chart in position. I have already accounted for column width changes created by modification of the pivot table parameters by selecting the "Don't move or size with cells" option under PivotChart Tools > Size > Format Chart Area > Properties > Object positioning. This is the default/locked-out setting for the slicers.
View 7 Replies
View Related
Mar 15, 2013
I have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB()
' Copies VBA modules, Calls
Dim wbNew As Workbook, wbT As Workbook
Set wbT = ActiveWorkbook
On Error Resume Next
Kill ("PATHmod1.bas")
Kill ("PATHmod2.bas")
[code].....
View 2 Replies
View Related
Apr 9, 2014
I need a simple macro to use for conditional autofill of cells below the active cell (to the end of the sheet) with the text that is being entered in the active cell.
DESCRIPTION: I have two columns of data, the first (call it Col1), has a word number which is unique to each specific word in a foreign language, every cell in this column has a number in it from 1 to 30000. Each number reoccurs many times in cells over the length of this column.
The other column (call it Col2) is blank. As I type words in english in each cell of this column, I need the macro to autofill the text that I have just entered, into every cell in Col2 where it's Col1 number is a match with the Col1 number where I am typing.
For example, in one row, Col1 has the number "21" and I type in Col2 the word "run". I need the macro to find every row where the number 21 is found in Col1 and automatically enter "run" into Col2 at that location.
The macro must only autofill cells which have no data in them. Preferably from that cell down, although if that is not possible, all cells would work seeing we are only replacing blank cells with data.
View 5 Replies
View Related
Aug 29, 2007
I want to print out, for a range of 'Page Fields', from a Pivot table automatically.
The Macro must be able to:
Automatically go through and select the 'Page Fields' in the pivot table
Select the print area range automatically, which will change each time the pivot is refreshed/reselected.
Set the print area to fit the page for each category selected in the pivot table
Finally print.
Can't find code for this anywhere, but i must admit got fed up with looking
View 3 Replies
View Related
Nov 16, 2006
I need to get a macro to select all the data in column "A", sort it in ascending order, omit the blanks if any, then select (highlight) all the data so that another macro can be run.
When I record it, it will only record up to the last row I highlight but the data always changes so there could be more or less.
View 9 Replies
View Related
Apr 24, 2008
I wrote a macro for a pivot table.
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Export!R1C1:R3000C53").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Reference"), "Count of Reference", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("ACN received date")
.Orientation = xlRowField
.Position = 1
End With
What i want to know is if there is a code line to test if "Non Processed" value exists in column "Status" and then hide the value.
Now, if the macro does not find one value just gives me error.
View 7 Replies
View Related
Dec 4, 2012
- The macro should then try to find this data on the worksheet "Sheet2" in the same workbook. This data on Sheet2 is essentially a table with four columns "Number" (Col A), "Country" (Col B), "Consol" (Col C) and "Bypass" (Col D) but can have around 70k rows
- It should look for Number first, and if its not found, the macro should insert the message "Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it matches the value input for Country on Sheet1, then it insert the message "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "Y" but the Bypass field input on Sheet1 has "N", then put "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "N" but the Bypass field input on Sheet1 has "Y", then put "Multiple Records - Bypass - Do Not Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows that have a Bypass value of "N" and it does match the value input for Country on Sheet1 then put "Multiple Records - No Need to Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is different for all the rows that have a Bypass value of "N", then put "Multiple Records - Refer" in cell A8 on Sheet1
[URL] ....
TestBook.xlsm
View 6 Replies
View Related
Jul 16, 2014
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.
View 1 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
Dec 18, 2007
I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.
ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")
View 4 Replies
View Related
Jul 17, 2014
I have this problem which I have simplified below:
Sheet 2 contains a Pivot table. One of the report filters is "Month-Year" (Eg, May-14 - in date format 'mmm-yy') which is selected by a Slicer on sheet 1.
Cell D1 on Sheet 2 contains a formula which calculated/displays the mmm-yy of the same period last year (eg May-13).
I want a code so that when I select the 'Month-Year' Slicer to filter the pivot table on Sheet 1 (eg to May-14), another Pivot on Sheet 3 is filtered with the previous year mmm-yy (May-13).
View 5 Replies
View Related
Apr 20, 2008
I have a workbook with a 2 macros "PopulateSheetlist" and "SaveEditedversion"
Normally they are each button operated, and prompt the user with vbYesNo style options. I wanted to write a Macro, say "Macroautomatewkbk that would automatically answer the prompts.
Sub Macroautomatewkbk ()
Call PopulateSheetlist
' Always answer YES or OK to any prompts this macro may offer
Call SaveEditedversion
' Answer No for the 1st prompt and YES for the 2nd prompt
End Sub
MsgBox "Both macros "PopulateSheetlist" and "SaveEditedversion"
' Some error handler here, don't know how this should work exactly.
Could any one please explain how to fill in code in the commented sections in the above sample code?
Edit: I tried recording a macro to do the above, but it only showed the zooming and scrolling that occurred, none of the button prompts being answered.
View 9 Replies
View Related
