Update PivotChart On Another Sheet Via Code
Sep 7, 2006
I have created a pivotchart and placed it on (say) sheet " GRAPH". Excel has created the associated pivottable and stored it on (say) sheet "PIVOT".
Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.
However, on closer investigation, i noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another problem for another day?) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the chart changed before my eyes. I thought I would then record a macro to see what i had to do.
The code the macro recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.
However, it crashes everytime with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant "Sub Worksheet_PivotTableUpdate" to display the target. name, its blank!
I've tried all sorts of work arounds like trying to update the pivottable in the graph activate event:-
dim pt as pivottable : set pt = ......... : pt.refreshtable etc
select the sheet before hand etc, etc but no joy.
Following the code through debug it always crashes with the same message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which then triggers my graph reformat code and everything is fine.
I'm sure i'm doing something wrong but it doesn't appear to like the chart and table being on different sheets?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
msgbox Target.name <---------ERROR (Blank!)
If Target.PivotFields("BusinessArea").CurrentPage = "XXXXXX" And _
(Target.PivotFields("BM").CurrentPage = "AAA" Or _
Target.PivotFields("BM").CurrentPage = "BBB" Or _
Target.PivotFields("BM").CurrentPage = "CCC" Or _
Target.PivotFields("BM").CurrentPage = "DDD") Then
MsgBox "Error - " & Target.PivotFields("BM").CurrentPage & " is not a valid Business Manager of " & UCase(Target.PivotFields("BusinessArea").CurrentPage) & vbCr & vbCr & "Please try again!!", vbCritical, "Error"
Exit Sub
End If
..graph reformat code (irelevant because even If i delete this it still bombs out!"
End Sub
View 5 Replies
ADVERTISEMENT
Jan 28, 2014
I've got a big file with a large dataset. One of the sheets works as a screening for the data, so everytime I populate an ID number in say cell C3, the rest of the sheet updates as per the formulas I have in place. However, I need to write a code that takes ID numbers one by one from a sheet, populates them into my screening sheet, updates the sheet and saves it.
View 4 Replies
View Related
Aug 30, 2007
When attempting to use the following
charts.add
activechart.setsourcedata thisworkbook.worksheets("PTPage1"). range("A1")
I receive the following error: Run-time Error '1004':
The source data of a pivotchart report cannot be changed. You can change the view of data in a pivotchart report by reorganizing its fields or items, or by changing its associated PivotTable report.
View 2 Replies
View Related
Jan 23, 2007
I have the following extract of code which is meant to add a chart. However, it adds a pivotchart and then errors on the last line because it can't change the source data on a pivotchart like it can on a non-pivotchart. All of the other charts in the workbook are pivotcharts. It was working fine last week.
Sheets "LOI Sheet" and "Data for Charts" exist.
"e" is a defined integer with value 53 at last running.
Charts.Add After:=Worksheets("LOI Sheet")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Data for Charts").Range("D2:D" & e), PlotBy:=xlColumns
View 9 Replies
View Related
May 14, 2014
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
View 2 Replies
View Related
Nov 21, 2009
I have copied a sheet, moved it to the end and renamed it with a date that is in cell "A1"
Now after that process is finished I need it to update the date in cell "A1" of the newly created sheet with the next day's date.
I am stuck however referring to the previous sheet to update the date value in "A1"
View 7 Replies
View Related
May 5, 2007
I need to write code to automatically update a user's add-in if a newer version is available. Here are the specifics. The add-in is installed on multiple users c:drives. In the add-in code, there is a constant, Version_Nbr. On the network drive, I have a text file containing the most up-to- date version number. When the user opens the add-in file, the code compares the Version_Nbr to the text file on the network drive. If the version on the network is higher, I prompt the user with "A new version is available. Would you like to download it now?"
I have a seperate download program which will automatically download the new add-in to the user's local drive. Here's where I run into problems. I cannot replace the add-in file with a newer version when the add-in is open. The add-in is always open because it's the add-in that determines if a newer version is available. So how do I do this? It seems to me I need to launch the download program from the add-in and then have the add-in close itself before the download program executes.
View 2 Replies
View Related
May 5, 2014
I have 2 excel sheets A and B
Sheet A has full list of (3000) of user names - First Name , Last Name and Email address
Sheet B has few UPDATED users (200) of user names from above list with UPDATED email addresses ( with First name , Last name and email address)
How should I update Sheet A with updates from Sheet B?
View 3 Replies
View Related
Feb 2, 2014
i have two sheets, sheet 1 is a list of some items i sell on my website and the stock quantity.sheet 2 is a list of what my supplier has in stock.i want a quick way of my stock values on sheet 1 in column G to match the stock values on sheet 2 column E.
the product codes are the same (sheet 1 is column E for product codes, on sheet 2 the codes are in column B).
on my website (sheet 1) the products size options (column J) are more detailed than that of my suppliers (sheet 2 column F)
for example, my sheet (sheet 1) will say on some items for example MEDIUM: DRESS SIZE 10-12 but my supplier sheet will just say medium also my sheet (sheet 1) will say ONE SIZE or PLUS SIZE where as my suppliers (sheet 2) will be blank however this means that there is only one stock value to update anyway as there is only one size option
is it possible to magically make sheet 1 match the relating stock values from sheet two?
so on sheet 1, item 10035 Child Gangster Suit in size small would change from 7 to 13 and item 10001 Jumbo Syringe would change from 11 to 20
i would also need the updated values on sheet 1 to be highlighted so i know if any have been missed, for example my supplier may have stopped supplying an item in medium but obviously i would still have a stock value for it on my sheet so therefore that value wouldnt be updated so i would need to be made aware of this so i can then remove the option (which has to be done manually) on my website...
View 2 Replies
View Related
Apr 29, 2009
On a weekly basis I record payment terms updates and do this process manually. I compare 2008 terms against 2009 terms for any status update. I would like to provide a button to do the work for me. Can anyone provide code please?
I currently use this "IF Statement" =IF(C2=D2,"No Change",IF(C2D2,"Reduced")))
View 9 Replies
View Related
Jul 28, 2008
My find and replace code is going to all the tabs in my workbook, but I want it to stop in my current spreadsheet I am on. Here is my
View 14 Replies
View Related
Dec 7, 2009
I have a time sheet which is used by around 15 people. Part of the timesheet is a userform which adds a new sheet and names it with the seleted month and year. I have made a few changes to my timesheet which I wish to update on other peoples sheets. I am going to send out a speadsheet with a macro that people can run and it will automatically make the changes to their timesheet. I have done all the work for updating the various formats and formuals but I have hit a bit of a brick wall when trying to change the code on a user form via a macro.
It would be great if I could either overwrite all of it or add a some lines of code from a specific line number.
View 5 Replies
View Related
Feb 13, 2009
I'm trying to write a VBA code to automatically update using a vlookup but I seem to be running into trouble, partically with the lookup value part of my vlookup. I basically have a range of dates in column B and want the values to appear in column C. Yes, I know I just type the forumla in column C and drag it down, but I really need to do it in VBA, as I want this to update when additional dates are added.
This is what I have thus far. The code keeps looping at i = i +1 and doesn't stop unless interuppted.
Sub update()
Dim i As Integer, L() As Range, s As Integer, V As Double
Sheets(1).Activate
s = Range("D:D").Select
On Error Resume Next
While s ""
i = i + 1
ReDim Preserve L(i To 1)
L(i) = s
Wend
If i = 0 Then
MsgBox "No dates found"
End If
Exit Sub
For i = 1 To UBound(L)
V = WorksheetFunction.VLookup(s, Range("Inputs"), 2, 0)
Next i
End Sub
View 9 Replies
View Related
Jan 23, 2010
I need to have multiple (up to 6) pivot tables on the spreadsheet with shared external data source. As a data source i have a csv file. Also, i need to be able to load data file on demand. I have created a button on the sheet that would load File dialog and let user select whatever csv data file they need. Then the idea was to update pivot cache connection and command properties using VBA with new file name. Here is the code :
Dim pc As PivotCache
Set pc = ActiveWorkbook.PivotCaches(1)
Dim ODBC_CONNECT_STRING As String
' FullFilename is a new absolute path to selected csv file
ODBC_CONNECT_STRING = "ODBC;DBQ=" & FullFilename & ";" & _
"DefaultDir=" & FullFilename & ";" & _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;" & _
"SafeTransactions=0;Threads=3;UserCommitSync=Yes;".................
View 6 Replies
View Related
Mar 15, 2007
I have multi- sheet workbook that opens to a specific ws and to a specific cell.
Application.Goto Reference:=Worksheets("Menu").Range("A1"), _
One of the several ws is a form; when the user saves it, all other ws are deleted and the wb is saved as a new file name. (Need to retain other macros for future functionality, seems easiest to just delete the unneeded sheets).
Is their a way to modify the sheet reference from "menu" to "sheet-name" (will be a constant). Because it is likely that additional sheets will be added in the future to this new wb, I'd like for it to open to a specific ws.
As my VBA skills are "in development" (basement level, just now), please provide a complete answer (assuming this can be done).
View 9 Replies
View Related
Apr 18, 2007
I need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
My code is below ...
View 6 Replies
View Related
Mar 20, 2008
I have a 15 excel files which are part of the forecast system. There is another file MASTER which is the host file for the macro. Every months multiple users are asked to update their forecast in the 15 excel files .As these files also display actuals, so they are linked to bunch of other files. The links to the files should not be updated unless I run my macro from MASTER to do the series of tasks.
Everytime these users open the files , they are asked to update links.
Is there a way for me to stop this warning and still able to update the links only when i run my macro from the Master.
View 4 Replies
View Related
Jul 4, 2012
I can easily use VBA to update a column's next cell with a value from a static cell. As easy as this may be for most, I seem to be struggling!
For example, I would like cell B3 (then B4, B5, B6 etc) to update, according to a value (time frame in seconds) in cell C3, with a value from the cell D3.
View 1 Replies
View Related
Jun 14, 2014
The below is a data sheet which is going to be designed to keep the records of "sending and receiving" details of wash-garments from a garment factory.
Descriptions;
01. Starting from B12, the dates of sending and receiving are entered in the sequence they occur. Both sending and receiving can occur on the same date.
02. Starting from C12, there are reference codes related to the activity, if Sending the letter is entered as "S", and if receiving it is entered as "R". I included this for the calculation or sorting purpose.
03. Columns D to J includes the break down of sizes of the quantities sent or received.
04. column K simply calculates the totals of the columns from D to J.
Requirement : I need to have the totals of each size wise quantities sent and received in the "summary table" as follows;
>> Size wise totals of "sent qtys" to be shown in D5 to J5(referring to the code "S")
>> Size wise totals of "received qtys" to be shown in D6 to J6(referring to the code "R")
>> The dates to be updated and displayed"automatically" in the rows of the column B, when the code letters("S" or "R") is entered in the column "C"
Special Remarks : The last date of sending or receiving cannot be predefined, the rows(dates)will be kept adding according to the way sending and receiving may occur.
View 14 Replies
View Related
Apr 10, 2008
I have a ton of cells that contain formulas with arrays. How can I update these without:
clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER
View 13 Replies
View Related
Aug 29, 2008
I have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.
follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet
View 9 Replies
View Related
Jul 16, 2009
I have a function that is called upon saving a UserForm. One of the Text Labels contains the ID field that I need to use to do a lookup into the first column of a Named Range and then I need to update the cell in the 4th column of that row.
From my research, I'm assuming it will go something like this:
Application.WorksheetFunction.VLookup(valve, Worksheets("TestWorksheet").Range("TestRange".Cells), 10, False).Value = "TestValue"
View 9 Replies
View Related
Jun 22, 2005
I am trying to link what is in the header and footer with information contained in certain Cells. (I eventually want to have a user form to help input the information, but one thing at a time.)
How do I go about getting the following information into code so that I can update the enclosed code?
Left Header: Client Name contained in Sheet: "Table of Contents" B:1
View 5 Replies
View Related
Aug 20, 2006
I have a sheet with names and subdividers (Ent, Ver) on the columns, and dates on the rows. I am trying to make a form update the sheet when the name, ent or ver, and date match. I have attached a copy of my sheet to help explain that. I don't know much vba, but I can make a userform just fine. Trouble with most places is that the userforms only update based on empty cells, not based on more complex criteria.
View 7 Replies
View Related
Mar 12, 2008
I used VBA to write a number of formulas into various cells in a workbook. After the VBA is done running, I change the values in the cells that are referenced by the cells I wrote formulas in. The formulas do not update after I enter this new information. My calculation is set to automatic, and F9 doesn't help.
View 3 Replies
View Related
Jun 2, 2014
The only change I made was to change the "Sheet1" to "Journal" to match the worksheet name in my workbook.
As you close and reopen the workbook, the timer should start with =NOW() in A1 (formatted as "HH:MM:SS") and count up with the current time until you close the workbook.
I use Excel 2010, could that be the problem, that I copied a VBA code for an older version of Excel???
Question: What exactly is a regular module, do I use Module 1 for the first portion of the code or place it in ThisWorkbook?
AUTO TIME UPDATE VBA
PLACE IN REGULAR MODULE
[Code] ....
View 7 Replies
View Related
Jul 3, 2012
I have 2 pivot tables in a worksheet.
The names are "PivotTable1" and "Pivot Table2"
Cell H2 is the worksheet has a currency type, ex) EUR, CAD, GBP
Column 1 in each pivot table is "Currency Pair Sell/Buy"
Based on Cell H2, I want the pivot table filters in column 1 to show 'USD.currency' and 'currency.USD'
For example, if H2 = EUR, the pivot tables filters in column 1 should only show USD.EUR and EUR.USD.
Any template for this type of code, I can fill in the blanks with the relevant sheet names and other information that might be needed.
View 9 Replies
View Related
Nov 15, 2013
I'm trying to update a pivot table filter with a list on another worksheet. When I do a record and select two WBS for filter criteria I get the following:
Sub Macro5()
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Receiver WBS Reference].[Rec WBS Element].[LM Master]").VisibleItemsList = _
Array("")
[Code]...
If I entered values 700UY0S1MGL1 & 700UY0S1MGL2 in cells A1 and A2 of Sheet2 in the same workbook, how can I get this code to read that? If I extend the list to include additional items, how can I get the code to read it?
My next question is, in Excel I could enter a partial search (like 700UY0S1MG) in the filter drop down and I could filter on everything with this string. Using the macro it seems as though the full 12 char string needs to be qualified. How would I write a macro to handle this?
View 1 Replies
View Related
Jun 6, 2014
I have some existing code which I did not write, nor do I have the expertise to update. The macro code takes multi rows of data for one person and "flips" the output to be one record per person with columns of data.
The source data looks like this sorted by teacher, Term and Period (columns H and I are the new ones added; the code currently looks through column G only)A
Faculty Name
B
Periodc
C
[Code]......
I just want to add column H and I data to the period course listings by term. So instead of "Term: TM#", just have the display indicate Termcode: Tally/SectSize.... such as (example): "TM1: 20/30"
My Code is as follows:
Option Explicit
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
[Code].....
View 1 Replies
View Related
Aug 24, 2009
I am tracking my sales interactions and am trying to link cells so when I update on one sheet it updates my last "customer touch" on a master sheet. Current formula-
On "sheet1" I selected a cell and used this formula... =sheet2!$b$7
The problem is that I want to keep a history of what was in that sheet 2 B7 cell so i insert a row (making B7 move down and become B8) and enter new info into the new b7. if I go back to sheet1 the formula in the selected cell becomes =sheet2!$b$8 I want it to stay to =sheet2!$b$7 no matter what I do... insert rows, delete rows etc...
in short what i am trying to do is have a cell in sheet1 show my last interaction with the customer and keep a history of in sheet2-infiniti.... with the caveat that my last interaction on sheet 2 etc... is listed first (thus the insert row)
View 2 Replies
View Related
