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
ADVERTISEMENT
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
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
Me.PivotTables(1).PivotCache.Refresh
End If
View 3 Replies
View Related
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
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
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
Jul 25, 2006
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
End Sub
View 6 Replies
View Related
Sep 5, 2006
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
View 3 Replies
View Related
Mar 5, 2014
I have 12 months worth of data in twelve separate workbooks each with a pivot table, I have copy/moved the pivot table tab from each into one workbook so now I have a workbook that consists of 12 tabs each with a pivot table. What I would like to be able to do is create a summary table with the full years data; where I am running into problems is that each months table has slightly different row and column counts and labels making any formula like =sum([sheet 1 cell a1]+[sheet 2 cell a1]) problematic.
View 3 Replies
View Related
Oct 14, 2003
if there is a way to display a table as column percentages but have the totals as raw numbers.
View 9 Replies
View Related
Jun 19, 2008
I've got 4 pivot tables (all derived from the same base data) on 4 separate worksheets. I've been able to (with this help of this site) to use VBA to hide pivot items on all of these sheets using a list on a user form. Hide/Show Pivot Table Field Items. Hide Pivot Table Fields Pivot Items by Criteria
I now need to be able to show all the pivot items on only 3 of the 4 pivot tables, with the 4th pivot table being left untouched. For ease assume that my sheets are sheet1, sheet2, sheet3, and sheet4. The tables I wish to update are on sheet2, sheet3 and sheet4. The pivot table on each sheet is called "PivotTable4" and the pivot item is called "Business". The pivot item contains 12 business names (Business1, Business2 etc etc)
Is there an easy way of doing this? I've spent the day looking through the internet and various "Dummies" books but with little success, I fear that I'm obviously below even Dummy level
View 5 Replies
View Related
Mar 16, 2009
I have a shared worksheet my self and a few others key pricing into weekly, more or less a price check for stores we service. I have created another workbook that has the same basic template with links to the "shared" worksheet. In this workbook Im trying to run a macro to copy the pricing "template" into a new tab in the workbook. I keep getting this error as it pertains to "sheet". I would like the tab that is created to be named whatever the current day is if possible.
Sub StoreWeeklyData()
'
' StoreWeeklyData Macro
' Macro recorded 3/16/2009 by : Creates a tab for current week pricing data.
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Template-Link").Select
Range("A1:AM61").Select
Selection.Copy
Sheets("Template-Link").Select
Sheets.Add
Sheets("sheet").Select
View 9 Replies
View Related
Jan 12, 2009
have had to try to automate a function which will transfer all rows of data that an operator "checks" in a check box, to a seperate sheet before printing. Written under pressure with boss at my elbow so I will tidy later.
My problem is that there is a command within the macro (after the filtered data is copied over to Priority sheet) which will not allow the macro to fully execute when the file is shared.
Copy file attached.
View 10 Replies
View Related
Aug 1, 2014
I've added some code the adds a button to a worksheet
Public Sub AddSheetPrintButton()
Dim btn As Button
Dim t As Range
Set t = ActiveSheet.Range(Cells(1, 6), Cells(1, 7)) ' button position
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "sheetPrint"
.Caption = "Print Sheet"
.Name = "Print"
.Font.ColorIndex = 10
End With
End Sub
This works fine until the workbook is shared!
View 1 Replies
View Related
Mar 13, 2014
I have a 8 pivottables with a graph for each. is there a way that i can have a dropdown menu to select which graph to display instead of having all these graphs everywhere....
View 4 Replies
View Related
Mar 21, 2014
I have an issue with printing a worksheet with a pivot table. When refreshing the table, depending on the filter choices, the table length with expand and contract. When the pivot table contracts, it leaves a light blue shaded area. If you try to print the worksheet it includes the blue section. Is there a print macro that can be written that will only print the sections with values or perhaps a print setting that would exclude the shaded area?
View 1 Replies
View Related
Oct 27, 2011
When in a worksheet and clicking in the pivot table results, the pivot tables no longer display.
I did have a reinstall of Office last week but can't see how that would have impacted this. Otherwise, I only use the pivot table command to 'refresh all'.
I know very little about them and didn't create this workbook.
I do add entries to the source data and have tried to change source data but I get Reference is not valid.
View 1 Replies
View Related
Feb 14, 2012
When I run pivot tables, the row label descriptions are only displayed at the highest level for each category. As displayed below the highest level on the row label is MOB and is only detailed in the first row. I need for each of the different row label levels to display the data so there are no blank cells.
MOBMan. Boxset 2Large1328Manual StickeringN/A500Shrwp 13+Large16680Small596Shrwrp 7-12Large35704Small2411SW 1-6Large87912Small14635SW w/ PremLarge26966OUTDisplay Lvl 1Small100Klutz Standard Display 6-packN/A2499Man. Boxset 1Large40000Man.
[Code] ........
View 2 Replies
View Related
May 15, 2014
All I want to do is write some vba script that inserts a pivot table based on all of the data in my current workbook.
Therefore I want something incredibly simple if it exists like:
ActiveWorkbook.PivotCaches.Create(SourceData:= ActiveWorksheet.Select.AllData)
ie the vba code that just takes the data you've got in your current worksheet and creates a pivot table.
View 1 Replies
View Related
May 27, 2014
I have four sheets with a lot of pivot tables on each and my problem is that whenever I hit Refresh the formatting of the pivot tables is always changing. It seems that this is something of an issue in Excel 2010 and that they only way to truly get around it is to re-format the pivot tables upon refresh with VBA code.
Any resource or thread I can use to learn the commands to format the pivot tables?
I am getting better with VBA code but seems to always get stuck on trying new things because I do not know the commands.
View 1 Replies
View Related
Mar 23, 2007
I'm trying to sync a common field in two different pivot tables.
I have 2 pivot tables on a single worksheet.
Each table has its own data source - 2 different databases (had to set it up that way to present all the data requested).
The 2 data sources have one column of data in common. This column is called Projects and all project names are the same in both databases.
BUT...
Pivot Table 1: Projects is in the ROW area (multi-select dropdown)
Pivot Table 2: Projects is in the PAGE area (single select drop-down)
I would like to link the Projects data items so that when I select a project name in the Combo box, the same project name would seamlessly be selected in both tables.
Each table would populate with its own data based on the project selected.
Basically, I'd like to use the method illustrated in this Flash file:
[url]
This method would have worked beautifully if it weren't for this reason:
Table 1: Project data is in the PAGE field
(single selection)
Table 2: Project data is in the ROW field (multi-selection)
If the Projects data was in the PAGE field in both tables, my code would look like this:
ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value
ActiveSheet.PivotTables("Table 2").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value
But no, because the ROW field is a multi-select one, I get this kind of
View 9 Replies
View Related
Dec 14, 2007
I have several pivot tables that need to be re-linked to an Access database (really change the link to a different month's data). Currently, I do this manually, but I was hoping to write a macro to do this. Data is saved by month so I can't just create a "current" file as to not change the links each time. I can only get code to work for the refresh portion.
View 9 Replies
View Related
Jun 5, 2009
I have used the macro recorder to create a macro that creates a pivot table from data located on another workbook.
with this said i have two questions.
How can i make it so that the workbook containing the data for the pivot tabel does not need to be open?
the second question is why am i gettign stuck with a macro that sticks right here:
View 9 Replies
View Related
Oct 16, 2003
Is there a code I can use to update pivot tables e.g every 10 mins?
View 9 Replies
View Related
May 2, 2006
I am working with VBA to create a pivot table, and have done just fine so far. However, I need to create a two buttons that will run the following macros: 1. A macro that will remove the selected header (either row or column) from the pivot table.
2. A macro that will put the removed header back into the pivot table.
I need to be able to click on the header (whether its the row or column header) and then press the button to remove it from the table. The second button should then add that header back into the table. My code for creating the table is fine, I just need to work out the buttons. Below is what I have so far. I was think that if I had variable for the header name it would be able to tell which header to remove (so I used Set iField = ActiveCell.Value), I also tried ActiveCell.Text. All I need to do is put the text in the selected field into the PivotFields range to make it hidden. However, I keep getting an error (Compile Error: Object Required) on the line Set iField = ActiveCell.Value. Below is my current code.
Sub PivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product", _
ColumnFields:="Location"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub..................................
View 4 Replies
View Related
Aug 3, 2006
I wonder if it is possible to make calculations between two pivot tables which have the same number of columns and rows and put the outcomes in a third pivot. For example Pivot 1 contains revenue data, Pivot 2 contains cost data and Pivot 3 provides the calculated outcome of revenues minus costs.
View 7 Replies
View Related
Aug 9, 2007
Is it possible for me to compare two pivot tables and get a report. in the attached example, is it possible to compare sheet1 and sheet 2, if the values haven't changed, then it should be green, or it should be red.
View 3 Replies
View Related
Feb 14, 2008
I read in your "best Practice..." sticked above that it's better to use Pivot tables then multiplied criteria SUMPRODUCTs (or array formulas). i agreed with it because i found it took long time to recalculate the sheet. do you suggest it refering the new SUMIFS formula?. i found that a SUMIFS that refers to Tables can be replacement for GETPIVOTDATA. In my sheet there are several dozens of GETPIVOTDATA.
View 4 Replies
View Related
Nov 15, 2012
I'd like to grant access to selective columns on a shared worksheet.
For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else
The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...
After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.
However in order to enter the password I need to unprotect the sheet. And in order to unprotect the sheet, I need to unshare it!
Is there any way around this or perhaps a completely different way of approaching it?
View 3 Replies
View Related
May 11, 2009
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
View 5 Replies
View Related