pivot chart automation. I have a tab that contains thousands of rows and it grows larger on a weekly basis. On this tab is 169 peices of hardware I manage. Each week 1 row of data is added to all 169 peices of hardware. The data from the main tab contains metrics I need to chart. However, I am in need of a way of selecting a specific peice of hardware and having the pivot charts update automatically. There will be 26 Charts and going through each chart and selecting the proper hardware is going to take far too long.
Is there any way i can select just 1 peices of hardware from the data tab and have it populate all of these charts?
I'm having a lot of trouble automating charts from my VB.NET program. I'm simply graphing one series of data as a time series that must maintain its order as the user filters the data, deletes some data, etc. Because of the changes the user can make, I am using a named variable with the offset command to maintain a dynamic range. I am aware of how to manually build the graph by supplying the series range =workbookname.xls!namedvariable, but I can't figure out how to supply this to my chart from my VB program. The following is some of my VB.NET code.
xlapp.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)") chartrange= ? chartPage.ChartWizard(Source:=chartRange, HasLegend:=False, Title:=ParameterNames(i) & " Time Series")
I can supply more code if needed, but figured this would give a general idea of what I am trying to do. This has to work across multiple Office platforms so I am using late binding.
Right now the code takes charts & tables from all sheets in a workbook. I would like to limit this to only charts on one tab - CHARTS.
I am also looking to tweak this to open an existing presentation, as well as apply a template instead of simply pasting to blank slides.
Sub Chart2PPT() Dim objPPT As Object Dim objPrs As Object Dim objSld As Object Dim shtTemp As Object Dim chtTemp As ChartObject Dim objShape As Shape Dim objGShape As Shape Dim intSlide As Integer Dim blnCopy As Boolean
For Each shtTemp In ThisWorkbook. Sheets blnCopy = False If shtTemp.Type = xlWorksheet Then For Each objShape In shtTemp.Shapes 'chtTemp In shtTemp.ChartObjects blnCopy = False If objShape.Type = msoGroup Then ' if ANY item in group is a chart................
I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart() Dim oCell As Range Dim oChart As Chart Set ws_data = ActiveSheet 'Chart 1 Sheets(wsPT).Select Range("B21").Select
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
I have some monthly figures that's I need to put into a charts, and I want separate charts for each months data. People add to the list of figures almost every day.
Do I need to put the data in different sheets (Aug, Sep etc), with different tables and charts, or is the a way you can keep it all in one list, and filter it so each months figures get populated in their own chart?
I did try putting the data in separate sheets and making new charts for each one, but it made my file really big. Also as some months are yet to come, the blank sheets were making the charts mess up.
I am trying to find a way to write a code in VBA to conditional format Bars on a pivot chart to change colors based on if the field beats the forecast field.
I am attaching the sheet which explains it better.
I would like the bar series in the chart to show green if it beat forecast and red if it didn't.
I want the macro to do this for all the pivot charts.
I have two separate data tables (with separate named ranges) and two separate pivot tables (the pivots each refer to the separate tables) all on the same sheet. For each of the pivot tables, I would like to have a separate pivot chart. In the pivot charts, when I click "Select Data", I find that the "Chart Data Range" for the pivot chart is grey (uneditable). Is there some setting that I need to toggle to enable the data source range so that I can change it? Also, when I try adding a new pivot chart to the sheet, this option is also grey/unavailable.
My column chart is produced by a pivot table & pivot chart and then I manually select each regions code, and paste the associated graph into word 120 times over. This is extremely tedious.
Although there is facility for SHOW PAGES on a pivot table, I don't seem to be able to find an equivalent built in function for pivot charts.
I've tried locating my pivot chart on the same page as my pivot table and then hitting show pages, but it only reproduces the pivot table.
If I could even get a graph on each tab for each region - I could at least PDF the excel file as a complete report by selecting all the tabs and printing them into a single PDF file.
I have a spreadsheet with multiple pivot tables and graphs. Is there a way to add a scrollbar on one sheet so that when you scroll through it, it will display(one by one) each graph that I created with the pivot table(s) so that they're all on one page(still dynamic) as opposed to them being on multiple worksheets
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")
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
I'm trying to do some automation of a website that has one of those annoying javascript popup dialog boxes you have to click OK on before you can progress. How can I kill that so that I don't have to manually click on "Ok" so that my macro can keep running?
You take an input, click a button. This will them create a small HTML table with a text input box and another button.
Sub VisitWebsite() Dim ie As Object Dim tcode As String Dim sURL As String Set ie = CreateObject("INTERNETEXPLORER.APPLICATION") sURL = http://sample.jsp ie.navigate sURL ie.Visible = True tcode = "apples"
While ie.busy DoEvents Wend Application.Wait Now() + TimeValue("00:00:03")
For Each ieForm In ie.document.forms ieForm(1).Value = tcode ieForm.submit Set ieForm = Nothing Exit For Next The code ieForm(1).Value = tcode works. The code ieForm.submit also hits submit on the button.
The problem is I cannnot figure out how to access the 2nd button which is generated when you click the 1st button. I have tried every variation of ieForm(1).Value. There appears to be only 3 forms (0, 1, 2). When I try 3 or greater, I get an error.
When I use ieForm(2).Value = "something", the Retrieve button text actually changes name to "something".
I've attached a screen shot of the webpage. I can insert text into the top Track Code box, and click Retrieve.
I can't change the text in the Routing Position field, and I cannot click the "Set" button. Any help would be greatly appreciated. I am still a beginner with VBA and it took me a while just to get it to work with the 1st input box.
The above is a screenshot of the data analysis (regression) I want to automate with vba code. Like all macros, I tried to record first and only got the following
I am trying to create a document that automatically sends an email whenever a field reaches a certain value. I am pulling the data from different worksheets onto a summary worksheet and using the data on the summary sheet as the point at which my code checks to see whether the value is below the minimum required. The summary sheet updates automatically whenever I change the data in one of the other sheets but the email will not send automatically when the summary sheet is updated. When I run the macro individually the email sends, however I need some way of triggering it so that it sends automatically upon the data in the field changing. Can anyone help me with this?
Here is an example of some of the code I am using:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D3"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value < 1 Then ANDES1 End If End If If Not Application.Intersect(Range("E3"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value < 1 Then ANDES2 End If End If End Sub
Creating a spreadsheet to inventory cups each day. When a macro was invoked do the following:
-unprotect the worksheet
-cut/paste the completed sheet below the current
-bring in the values form the previous day's inventory
-set protection on new spreadsheet to lock the cells
-blank out old values
-reprotect the worksheet
Pretty straightforward and seems to work fine for a few iterations, then crashes with "Run-time error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients.".
As FYI, this code was mostly generated using a record macro session, so no variables involved.
Here is a screenshot of the spreadsheet as it sits now:
spreadsheet.png
Here is the code: Sub NewDay() ' ' NewDay Macro
' Create new day and copy cup count from previous day[code]....
We are trying to automate the creation of PDF files for each sheet in an Excel file,a nd keep getting error messages. Can someone help? It produces the post script file, but it has no data nd then doesn't create a PDf file at all. We have Excel 2003, and have the below code setup.
I have a file that I update daily. It involves opening the file, going to a drop down menu and selecting 1 of the listed items and pressing a button to run the macro. Once the macro is complete, I then go back to the dropdown menu and select the next line item and press the same button to run the macro on this. I repeat this task 7times.
All the macro does is load a txt file to a third party system.
I am looking to see if it is possible to reduce the time it takes to do this task.
should the line be set : ObjXL.displayAlerts = True - befor the objxl.Quit ? - before the Objxl.ActiveWorkbook.SaveAs FileName:= aname how to close the ObjXL with out orphaning a process.
OR??? ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion Will this line of code create a 2nd instance of Excel?
I am trying to calculate the sumsq throught VBA for some automation.
The formula to be entered is - =SUMSQ(K1025:K1358)/(COUNT(K1025:K1358)-COUNTIF(B1025:B1358,"0"))
Another thing is that the row numbers are variables, and not fixed in automation. Let's call them R1 and R2. So I need the range to be R1 to R2 in all the instances in the above formula.
Step 1 : click on button in Internet explorer1(IE1) which inturn opens Internet explorer2(IE2). Step 2 : in IE2 i want to enter some value in textbox and click in "go" button.
Managed to get step 1 some how, need to proceed futher with step 2 given above
Private Sub IE_Autiomation() Dim i As Long Dim IE As Object Dim objElement As Object Dim objCollection As Object Set IE = CreateObject("InternetExplorer.Application")
I am trying to automate a website which I use for daily office related work. I am navigating the website, selecting some options and clicking on download button using vba code. When I click on download it opens a new internet page. Now I have to click one more hyperlink on that newly opened internet page. How to do that?
I created a UserForm (userform1) and added a label to it which essentially just says: "Pick a Colour"...
The other UF controls are added on the fly using the below code...however when I try to add the event to each button I get the Automation Error but I don't really understand how / why... the events are actually added to the VB project ok...
Private Sub UserForm_Initialize() Dim NewButton As MSForms.CommandButton Dim ws_i, line As Integer Dim clr_str As String For Each ws In ActiveWorkbook.Worksheets
I added a dummy routine for test purposes which is to be invoked on click of UF button control (I have tried placing this code in a standard module, within UF module etc to no avail):
Sub HIDE_WS(clr_id As Long) MsgBox clr_id End Sub
So to reiterate -- when I invoke the initialisation of the UF I get the following error:
Automation Error The object invoked has disonnected from its clients
All events are added for each control.
If I remove the adding of the events I know the UF populates with the correct controls etc...
I've done quite a bit of research in the past week or so on the topic of solver automation using loops; while there are a few good posts scattered around, i haven't quite found the solution to my problem.
i'm trying to run the solver on a few like-sized datasets contained in one worksheet. i've set up a loop to properly reference the 4 datasets. the solver constraints are in one column on the same worksheet.
the macro runs with no errors, but it doesn't "solve." the parameter cells retain their original values.
i should mention that my macro ran perfectly before i consolidated the 4 separate solver calls into one loop. I got tired of having to type 4 different constraint changes in the code as i was optimizing the solver.
on a similar topic, does anyone know how to call a variable in the FormulaText declaration? If i wanted to step through a defined array of constraints, say [1,2,3], how do i get FormulaText:= variable to work?
Sub Macrosolver()
Dim iteration As Long Dim i As Integer i = 8 'start in row 8 at first dataset
how to automate AddIns sequence when opening Excel? I have 5 different AddIn (xla) and I have to launch them in a specific order (after the menu Essbase is installed - with xll) cause I'm adding rows in that menu. Excel is launching my xla's in alphabetical order but before Essbase is installed.
I receive sales data from my wholesalers every month and I continually have to format them to fit the structure of our in-house database. I wanted to design a macro that would automate this process. However, in some months, the files are recieved in a format that is a bit different from the wholesaler's usual format.
Is there such thing as an initial "litmus" test where I could try running the macro and if it doesn't fit the usual structure, there's an error code and I could do it by hand?
I am new to excel macros and writing automation code....I work for a company that sells special permits and so far i have written a macro to bring up a pop up box when a permit has expired but i also want to be alerted by email, how can I do this, also is there anyway to stop the macro from running once it finds all of the expired permits? I usually press ctrl + break....
I have attached the file to this post : Book1.xlsm
I'm trying to automate advanced filter but can't get past having just 1 critreria. I have attached an example spreadsheet where the download dump is on "Data" sheet and the result of the advanced filter is on the "Result" page. I would like, if possible, for the result to come up every time the criteria is put in (they are all data validation lists).
so for example, if i select account code, all those codes come up then select period and the list dwindles down and again for cost centre. Is it possible to do this without using macros. I have found the following code butnot sure what to do with it (sorry).
