Macro To Create Pivot Tables And Sort Out Data In Other Worksheet

May 19, 2009

The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros.

I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet.

How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think.

An example of what I want the pivot table to do is:
in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5
then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way)
then do the same all over again for value in L2
etc etc etc

this should be done for the 6 values in column L, each having values up to 7 in the J column.

Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.

View 14 Replies


Macro Create Pivot Tables?

Jun 27, 2007

I created a macro by using the macro recorder. It creates some columns and formulas and does some formatting, then it is to take the data and create some pivot tables.

for some reason it gets stuck on the first pivot table. It creates it, but it wont add the first field to the table.

View 4 Replies View Related

Macro To Create Pivot Tables

Oct 22, 2006

I have recorded a Macro to create Pivot Tables, I would like this to run in any workbook. The problem is that every time a Pivot Table is generated Excel alters the number of the table(Pivot Table options). This then does not match that in the Macro as below("PivotTable1").

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R37C6").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GAME")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LANGUAGE")
.Orientation = xlRowField
.Position = 2

View 2 Replies View Related

Sort Multiple Pivot Tables

Jul 15, 2014

with this macro?

I have a lot of pivot tables on multiple sheets in one workbook.

I want to deselect all "(blank)" from rows, and sort both rows and columns in ascending order.

I recorded a macro but it only refers to specific pivot tables, I would like it to do the same sorting for all pivot tables in the workbook.

Sub PivotBlankSort()
' PivotBlankSort Macro
Windows("Alt Assessment Y10 13-14.xlsx").Activate
Sheets("L&C Prog Sum2").Select
With ActiveSheet.PivotTables("PivotTable10").PivotFields("English")
.PivotItems("(blank)").Visible = False


etc as I move to each separate pivot table.

View 2 Replies View Related

Macro To Create Pivot Table On Active Worksheet?

Jun 20, 2012

I have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from

Sub SalPiv()
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick


View 1 Replies View Related

Pivot Tables - Create New Fields On The Fly?

Feb 1, 2010

it's possible to create new fields (variables/columns) on the fly (without adding them to the original data table).

For this example, assume I have a column for:
job code estimated hours
job code actual hours
total estimated hours
total actual hours

Excel has made it easy to run averages or sums for any of those columns, but what if I want to see what the average proportion for job code estimated hours/ total estimated hours? Is there an easy way to do it with pivot tables without adding a column to my database? I'm working in Excel 2007, my data table size is approximately 100 columns by 200 rows.

View 2 Replies View Related

Create Addin That When Clicked Refreshes All Pivot Tables?

Mar 12, 2012

I am trying to create an addin that when clicked refreshes all pivot tables and queries insteading of using the right click refresh or refreshall option button. This is part of a larger project which requires the addin -vs- the built in functionality. Anyways...the following script if I put it in a button and run works perfect....if saved and run as an addin it blows past the refresh code and does not refresh anything, but will cycle through each worksheet.

I also tried thisworkbook.refreshall and activeworkbook.refreshall and neither worked.

Dim wks As Worksheet
Dim p As PivotTable
Dim qt As QueryTable


View 1 Replies View Related

Create Macro To Sort And Move Data

Jun 5, 2009

I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.

I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.

The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.

View 9 Replies View Related

Copy Pivot Tables To A New Worksheet

Nov 5, 2009

I have a workbook with a lot of commercialy sensitive data. I have created various pivot tables from that data which I want to copy to a new workbook without retaining the link to the original data, so I can send it to a number of suppliers.

View 8 Replies View Related

Pivot Tables In A Shared Worksheet

May 31, 2006

The shared worksheet resides on a secure network drive, so I can access it from anywhere. For years, I've had my secretary cut and paste out separate reports on individual salesman booking/commission performance. I thought taking advantage of Excel's native Pivot Table features, would save an awful lot of work, and probably lessen the chance of errors. Then I discovered that Pivot tables don't work with shared workbooks.

So I tried un-sharing the file. I discovered that the Pivot tables worked fine, but that I had to
re-create them ( seven sales guys, 4 independent geographical territories) each reporting period. The Pivot tables weren't dynamically updated each time additional orders were added to the main list (entry worksheet). I was very careful in laying out a new version of the "entry worksheet", and eliminating any unecessary column and rows. My immediate thought is that for the Pivot tables to work dynamically, I have to have dynamic ranges in the entry worksheet.

View 9 Replies View Related

Change Pivot Source Data In Multiple Pivot Tables?

Jan 21, 2013

I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.

Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.

Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.

View 4 Replies View Related

Refresh All Pivot Tables On Worksheet When Cell Changes

Dec 23, 2008

I have a dashboard that I created that is driven off one source of data. I have several lists and pivots created from that single source. I have two cells that are driven off of lists that then drive the pivot table. When a user selects an item from the drop down list I would like the pivot table to update. I have two tables like this on the main dashboard. Once the user selects the first data point, the table refreshs as does the second drop down list. After they select the second data point, the second privot table will refesh. Manually everything works prefectly, but when I attempt to automate it with VBA, it will only update the initial pivot and the secondary list, but not the second pivot.

I have posted the current code below.

"SelDept1" and "Wave" are the two cells that contain the drop down lists.

Private Sub Worksheet_Change(ByVal Target As Range)
Application. ScreenUpdating = False

Sheet4. Unprotect "lcssdi"
Sheet2.Unprotect "lcssdi"

If Target.Address = Range("SelDept1").Address Then
End If

View 3 Replies View Related

How To Use Pivot Cache To Create Another Pivot Table Instance On A Different Worksheet

Jul 7, 2014

I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.

View 1 Replies View Related

Macro Code Create Pivot Table Based On Dynamic Source Data

Nov 28, 2006

The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....

View 9 Replies View Related

Pivot Table - New Worksheet Auto Create To Right Of Existing Worksheet

Sep 2, 2013

The new worksheet is created to the left of the existing source worksheet.

View 2 Replies View Related

Running Multiple Pivot Tables With Same Macro

Aug 31, 2006

I am trying to find a way to set up a macro that will allow me to pull in data - create the Pivot table - delete the table - then pull in fresh data (of a different row length - same number of columns) and create another Pivot table. I have tried to manipulate recorded code to no avail. Here is what I am starting with:

Const lngLastPossRow As Long = 65536
Range(Cells(1, 1), Cells(Cells(lngLastPossRow, 1).End(xlUp).Row, 24)).Name = "Data"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Data"). _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array(" ", _
"Channel", "Sales/Exchange", "Mode of transp.", "Sold-to Party", "Material", "Data")

While it creates the first pivot table fine, after deleting it and starting again it wants to create the next sequence (PivotTable3) which crashes the macro. I must close the file and reopen to run it again.

View 4 Replies View Related

Doing CountIFs Off Of Pivot Tables Data?

Apr 16, 2013

I have a pivot table i created to pull in data in a tabular format so i can have all of my data fileds listed in a columns. On another page I am doing a dashboard that is using Countifs to pull in data to my report. What I am running into is the countifs are only working when i filter my pivot table to a certain value that matches, otherwise it will not pull the counts in..

View 5 Replies View Related

Pivot Tables - External Data

Mar 10, 2014

I have a VBA code that goes to a specific Folder, grabs data from an Excel file, and then puts (data) in another Excel file (template with VBA) file as "Raw Data" tab.

Then my VBA refreshes 30+ Pivots from that "Raw Data" tab.

Now "Raw Data" is stored together with 20+ Pivots in one Excel file.

Challenge: Is there a way to keep my "Raw Data" in the external Excel file and simply refresh my Pivots by only "pointing"/linking that source?

View 3 Replies View Related

Macro/Code Required For Calculations Base On Pivot Tables Sum

Jan 29, 2009

What I required is either a Macro or Code for formulas in column 'F' in the attached spreadsheet that correspond to the SUM of each description and divided by 37.5 e.g. in F10 the formula should be =D10/37.5 the formula should be F12 D10/37.5 and so on all the way down the Pivot table

My problem is as the amount data increases on the Data Tab the formulas in column 'F' will become out of line with the corresponding Sum of each description so I guess I need some code or formula that check every time the Pivot table is refreshed.

View 3 Replies View Related

Filter Data Into Groups That Contain A Common Data Point Using Pivot Tables?

Apr 30, 2013

I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?

As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.

Before filtering:

Study ID
Study Short Title
Study Patient ID



View 8 Replies View Related

Pivot Tables 2007 - Selection Data

Dec 29, 2009

I have the following code and I can't work out how to fix it. What i want is for the macro to create the pivot table from the "used range" in a worksheet but I want it to be formatted as a 2007 pivot table. The reason I say this I because I have some 2003 code which works very well which is where the idea of this come from but it doesn't seem to work here. I have highlighted the problem in orange, I know the rest of the code works because when I replace the orange bit with

"Raw Data!R28868:C33"
it works.

Sub CreatPivot()
Dim wksdata As Worksheet
Dim rngdata As Range
Dim wksdest As Worksheet
Dim pvtTable As PivotTable
Set wksdata = ThisWorkbook.Worksheets("Raw Data")
Set rngdata = wksdata.UsedRange.........................

View 5 Replies View Related

How To Access ISeries Data Through Pivot Tables

Oct 28, 2011

I'm trying to access ISeries data through Excel pivot tables.

Two weird issues, one is preventing what I'm trying to do.

1. After I set library list and saw my list of files, I didn't see ALL the files in the library, just some of them. Where are my missing files?

2. I found the file I needed and selected it. The file has 15 fields. When I got my field list in my pivot table, there were only 13 fields, and one field I need is missing. I noticed the missing fields are my packed numeric fields.

Is that a limitation of Excel, that it can't pull in packed numeric fields, or is there some way to get those fields without changing the file field definitions on the iSeries?

View 1 Replies View Related

Pivot Tables - Quartile Data By Row Label?

Dec 12, 2011

I have a bunch of raw sales data, by individual, which I would like to quartile to determine who is performing at which level.

Not all of this data should factor into the Quartile - a T/F "Exclude?" column flags the individuals who should not count in the quartile for any given month.

I then built a pivot like this with the Row Labels nest like such:


What I would like to do is define a formula that will quartile all of the agents where Exlude is False, Year is 2011, and the Month is January. And I would like this work going forward, so manually defining the range each month would not be a fix.

What do you all think? Is there a way to do this? To tell it to only quartile the individuals for each month?

.........Mr. Excel......6

View 3 Replies View Related

Removing Old Data Values From Pivot Tables?

Jul 17, 2012

If I update my data, then refresh the pivot table, old values still show as an option. How can I delete these items? If it matters, my pivot table's name is PivotTable2.

View 1 Replies View Related

Macro To Create And Rename Multiple Worksheet And Then Placing Relevant Data

Feb 24, 2014

I want to find a way to create multiple worksheets and matching data to be placed on appropriate sheets.

Here are more details (Please check the attached sheet screenshot as well):


In a workbook, there is a "Master-Sheet". This master sheet contains 8 columns.

I want to create as many new worksheets after master sheet as the values are there in column B (Column 2 i.e. Ad Group). In above attached screenshot, there are 8 values (B2:B9 or A-H). So, I would like to create 8 new worksheets after the master sheet. Also, I want to rename them based on their value from Ad Group column.

Each newly created worksheet should have same columns as the master sheet . Same 8 columns with their name intact.

Finally, I want all matching data of the Ad Group values to be placed on their respective newly created worksheet. For example, worksheet A should have A2:H2 data. Worksheet B should have A3:H3 data, and so on.

Please note that same ad group may have more than one row data. But I don't want to create multiple worksheet of same name ad group. The worksheet should be just one, but all matching data should be placed in that one sheet.

I know it is a bit complex task, but I am sure there would be a way to perform this automatically - probably a macro.

View 1 Replies View Related

Automatically Refresh Pivot Tables After Data Entry

Mar 20, 2009

This is the first time I’ve tried to use VBA. I’m using Excel 2000. In my excel workbook have 1 sheet called “Cards” in which I Change data in one cell $B$2 (enter a part number) and formulas in this sheet return many values from another sheet “Card Data”. In the “Card Data” sheet, formulas that look through a sheet “Sales Book” and return the data that pertains to the Part Number I entered in the sheet “Cards”. In the sheet “Card Data” there are 3 pivot tables (“PH CALC”, “PH QTY”, and “Pivot Table 3”) that use the Part Number data to show 3 different sets of information. The pivot tables are the source for 3 charts in the “Cards” Sheet.

Problem: Pivot tables don’t automatically refresh. I would like to use VBA code to automatically refresh the pivot tables in the “Card Data” sheet when I change the Part Number in cell $B$2 of the “Cards” sheet, which, in turn, would then update my charts in the “Cards” sheet.

View 3 Replies View Related

One Slicer For Multiple Pivot Tables With Different Data Source

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

Update Source Data For Multiple Pivot Tables With VBA

Dec 16, 2013

The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:

Sheet13.PivotTables ("Top10LW").PivotTableWizard SourceType:=xlDatabase, SourceData:= _

This works great however I was repeating it for every pivot table I had in the spreadsheet, causing messy code and file corruptions.

Is there any code which I can use for updating all pivot tables, using as minimal code as possible, all in one hit?

View 3 Replies View Related

Combine Data From Multiple Worksheets - Pivot Tables

Sep 20, 2009

I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.

My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.

I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.

View 9 Replies View Related

Pivot Tables Do Not Refresh Contents When Data Is Deleted An

Apr 11, 2003

When we create a database in an Excel Spreadsheet and then some pivot tables, we use to copy the spreadsheet with another name, in order to clean the data and update it with another date (for instance, one excel spreadsheet for one BU or country, another one for other bU or country), to avoid creating the pivot tables again and again.

However, when we clean up all the data and enter the new one, on the Pivot Tables fields, you still see the "old data that was deleted"... Do you know if there is a way to delete that data without recretaing the pivot table?

View 9 Replies View Related

Copyrights 2005-15, All rights reserved