Highlight Used Cells For Pivot Report Selection
May 6, 2006
Below is my code to create a pivot table. The data is located on a 2nd sheet named 'Data2' in columns B & C. As you can see the range is already defined. How can i have this as a variable which holds the current address of the automatically selected - occupied cells in columns b-c? So in order a macro that can.
1) select all occupied cells in colums b-c
2)paste this range as an address in d1
3) assign a variable which has the range for the pivot table macro to complete its work.
Sub Macro8()
Sheets("PivotReport").Select
Range("B18").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data2!R1C2:R27C3").CreatePivotTable TableDestination:= _
"'PivotReport'!R18C2", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Products")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("QTY"), "Sum of QTY", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Application. CommandBars("PivotTable").Visible = False
End Sub
View 6 Replies
ADVERTISEMENT
Feb 12, 2014
I have a workbook with timesheet records for a list of resources from multiple locations. These resources have logged time against the projects which is captured on a monthly basis. These time records are against holidays declared by the company. I would like to see if there are any of them who have logged time incorrectly on a holiday.?
View 4 Replies
View Related
Aug 16, 2013
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.
View 3 Replies
View Related
Feb 26, 2009
I have a VBA module that performs several things when a command button is clicked. One of the last things it does is paste values, so when the module is comeplete the pasted area is still copied to the clipboard; in other words, it is outlined in pulsating dashes. What can i add to the end of the code to un-copy this selection?
View 2 Replies
View Related
Dec 13, 2007
I inserted the code below in my worksheet which allows me to highlight the entire row of any cell I click into. I use it when I am entering data into different cells along the same row. Helps me keep things lined up. Is there a way to turn this on and off? Maybe a button or check box that can be clicked to activate and deactivate it.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Rows(Target.Row).Select ' highlite entire row
Target.Activate ' select the cell
End Sub
View 6 Replies
View Related
Nov 10, 2008
i have a pivot table report filter that i cannot sort a-z. i've tried sorting the source data and also right clicking on the first value then choosing display a-z - neither work.
View 5 Replies
View Related
Apr 16, 2013
I am trying to arrange data (pay by week) for regions and sites within a region. In the first Report filter, I select the site, and the second report filter gives me all of the sites - not just those in that region.... How I can either arrange my data or change my pivot, so only the appropriate sites show under the region?
View 1 Replies
View Related
Jun 16, 2014
I have a pivot table based on sales data and I need to know the 3 salespeople that had the highest sales during the current fiscal year versus the last fiscal year.
I have tried manipulating the pivot table but I keep getting the error message: "the following system error occurred: the requested name is valid but no data of the requested type is found".
View 1 Replies
View Related
Sep 28, 2007
I have a simple table Attached. (A simplified version of a much larger table) The data continues on for thousands of entries and there are quite a few more columns. Names are dynamic in that names will be added and deleted over time. (There is a list of names on a different worksheet that updates as users are added.)
I wish to Consolidate the Data Under Different Titles. The data in the NAME column will Consolidate to list each name in Alphabetical order. The data under the RAP COUNTER column should count the number of YES entries per NAME under the new title ATTEMPTS. The data under the RESULT column should Count the number of KILL entries under the new title KILLS. And the Data in the TG SCORE column should average all entries per NAME under the new title TOTAL SCORE. I tried to do this with a pivot table to no avail. I could write some programming to accomplish this, but I am am amateur, and I also worry about time required to run a macro for thousands of lines.
View 5 Replies
View Related
Dec 16, 2013
I am trying to create a pivot table report to find out how much profit each client has made and I need the total profits to be added up for each client so I can see who has made the most/least.
I have attached a sample ss.
View 3 Replies
View Related
Apr 5, 2013
Below is a simplified/truncated version of a data set that I am using in a pivot table:
Client Asset Return
1 Port1 10%
1 Port2 12%
1 Port3 11%
1 Port4 13%
1 Port5 10%
[Code] .....
I have created a pivot table and I'm using report filters for both Client and Asset (obviously there are a lot more data points). When I filter on Client 1 I would expect to only see Port 1 - Port 7 available in the Asset filter however, I see every asset in the data set. I need to pick and choose using the Select Multiple Items check box without having to scroll through every single asset. Is there a way to easily do this?
View 1 Replies
View Related
Aug 19, 2013
I have a report (roughly 4000 lines) which I need to rearrange for pivot/power-pivot use.
The structure for each row/record is:
col1 geography
col2 area
col3 customer
col4 product
col5 price
col6-65 monthly sales units - 5 years (columns labelled Jan 2009, Feb 2009 .... Dec 2013)
I want to rearrange the data as:
col 1-5 unchanged
col 6 month (data Jan 2009, Feb 2009, etc.)
col 7 sales units
Currently i have 4000 rows/records (each containing 65 fields). Iwant to end up with 4000 x 60 or 240,000 rows/records of 7 fields each. Is this possible through a data import wizard or VBA routine?
View 5 Replies
View Related
Aug 29, 2013
So I was wondering if it were possible to completely lock a pivot table, almost like protecting the sheet, but to allow the report filter field at the top of the table to be used. So what I'm trying to show is our net rev accounts in the rows & quantity and amounts as the value all fixed and unable to move about or drill down; but then have our product titles at the top in the report filter which you can still use to be able to select specific products.
I understand that I could make a big old page of drop down lists and vlookups and sumifs etc etc, but surely the above would be way quicker, plus I wouldnt have to bother checking it all works regularly!
I'm currently running this macro, which nearly does the job, but the data and row labels are still able to be manipulated. So... changing the macro, or doing something completely different?
Sub KillPivot()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
[Code]....
View 1 Replies
View Related
Apr 21, 2014
I have to track how well location managers are keeping track of skill competencies.
We rate skills as...
0 – know nothing ,
1 – read training manual,
2 – check off by manager.
I need the managers to provide these ratings then I have to report what locations need more training. When an new employee is entered into the system, the manager should give them a target of 2 for each skill the employee needs to gain. If the target skill is 2 rating higher than the current skill level, I have a column named Delta that records a 2.
I have learned how to use the pivot table feature. I can make separate pivot tables using filters that show…
a) how many 2s each employee has for a target,
b) how many 2s each employee has for deltas,
a) how many 2s each employee has for a current level and I can show the date the 2s where achieved
What I need is to learn how to combine all of this information on one pivot table (or report), but the filters really seem to make a mess of it all when it is all together. adding multiple sheets to one Pivot table.
Screenshots...
Count of Targets
Count of Deltas=2
Count of Current Skills
!What I need to produce!
[Code].....
View 1 Replies
View Related
May 28, 2009
I have a pivot table created from a data table with three columns: Date, Sales, and Customers. I have the Date column in the Report Filter and I want to change the date based upon a value in a cell range named Date, of all things. The pivot table is located in another worksheet and the range Date is changed by a spin button in the active worksheet.
My code is:
Sub ptDate()
Dim pt As PivotTable
Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")
pt.PivotFields("Date").ClearAllFilters
pt.PivotFields("Date").CurrentPage = Range("Date")
End Sub
View 9 Replies
View Related
Jul 27, 2004
When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.
Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?
For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.
I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.
If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.
View 9 Replies
View Related
Dec 27, 2007
I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.
Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.
Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.
View 2 Replies
View Related
Dec 11, 2013
I have a big list of names of people that have completed a bunch of training they have completed. I want to be able to run a pivot table/chart and only count a user once vice each time they have completed a training. So in otherwords I want to get a count of how many people are on the list vice how many certifications they have.
View 1 Replies
View Related
Jul 15, 2014
I'm trying to find a macro that will report how many values are returned in a pivot table. For example, if there is 5 values returned, I want it to return the value "5" in a cell If there are 10 values returned from the pivot table, I want to return the value "10" in a cell
View 1 Replies
View Related
Dec 17, 2011
I have a pivot chart, filtering data from a pivot table, the problem I've got stuck is that every time I filter the chart the colors and weights of the chart change to default values, not what I want, I've managed to every time I click on the pvt cht tab it updates with the colors and weights I want, but when filtering I don't know how to do.
View 1 Replies
View Related
Aug 8, 2012
I was wondering if it is possible to create a custom pivot table report filter? I would like to take an existing pivot table report filter and manually add values into it. I would like to do this because I have multiple pivot tables, some with the same values and some with different values and I have a VBA code from Contextures that applies a mass filter to all fields with the same name. So if i could manually add values into one report filter, I could filter from one location and have all my pivot tables update at the same time if they contain the value that i would like to filter by.
View 1 Replies
View Related
Apr 5, 2013
I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)
The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.
When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line
Error Message: Unable to set the Visable Property of the Pivot Item Class
Code:
Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#
[Code]....
View 9 Replies
View Related
Aug 19, 2013
I have a file with lots of pivot tables which works fine. But if I save and reopen the file then I get the following message when I try to change a filter on any of the pivot tables:
"The pivottable report was saved without the underlying data"
(ideally without refreshing all pivot tables using a workbook open event as this will cause a 10-20 second time-lag)
p.s. the file used to work fine before I added an extra pivot table (there are roughly 10 pivot tables in the file)
View 1 Replies
View Related
Mar 3, 2014
By using this function I have managed to do a 1000 sheet report, 1 for every customer.
However, my problem is that I don't know a way of globally setting the print area for every single sheet.
Also, the reports are not exactly the same size, they will vary from 5 to 12 columns.
View 1 Replies
View Related
Feb 3, 2014
So I have a pivot table of about 2000 rows with data on employees.
In the table, there is a field associating each employee with a location (midwest, southeast,where ever) and I have a report filter on it that lets me view only people from a given region.
It was working fine, but I'm doing a lot of VBA macro related to the pivot, and I think I somehow changed a setting somewhere in the process and don't know how to reverse it.
When I apply the filter now, it only shows data for people in the region i choose, except it still shows all the other lines that should be hidden, just with no values. Here is a crude visual
Name region sales
Jim_____MW__10
Henry___MW__34
Jerry____SW__20
Aaron___SW__44
Scot____MW__22
AFTER FILTER for "MW"
Jim_____MW__10
Henry___MW__34
Jerry________0
Aaron_______0
Scot____MW__22
(jerry and aaron should not be visible at all)
View 2 Replies
View Related
Feb 20, 2012
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies
View Related
Nov 11, 2013
I have a pivot table with the following items;
Report Filter = Project Names & Dates (filtered on 2 fields)
then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days
When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.
Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?
View 2 Replies
View Related
Jun 19, 2013
I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
View 2 Replies
View Related
Aug 17, 2013
I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using combobox on userform.
Sample Data
ID
NAME, INIT
GENDER
DEPT
SALARY
DOH
LOCATION
RAISE
1
Smith, J.
F
Sales
$41,250.00
2/2/1982
Boston
$45,375.00
[Code] .....
VB:
Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Employees Data").Select
Range("A1").Select
[Code] .....
Error:
Private Sub ComboBox1_Change()
ComboBox1.Value = objTable.PivotFields("DOH")
End Sub
View 1 Replies
View Related
Apr 24, 2014
I want to create a report using power pivot while creating the relationship between the linked tables, power pivot is throwing error "The relationship cannot be created because each cilumn contains duplicate values. Select at least one column that contains only values"
View 1 Replies
View Related
