I'm making a template that will title my charts, and I want to cycle through all the charts and apply names to them (the names for each chart will be stored in a particular cell...). I recorded a macro to see how to access a particular chart, so I clicked record, selected a chart, then selected the next chart. Excel tells me that what I did was
I want to give the charts meaningful, sequenced names so that I can cycle through them in VBA, so my question is: how do I access/change "chart 1027" and "chart1033?"
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
I am trying to write a macro that will look through an entire workbook and list all the charts in that workbook in a UserForm listbox. The user would then be able to select what charts from the file they would like to export to powerpoint. I have been able to complete this for lookup of sheet names, but methodology does not seem to work for the charts. Here is the code I am trying to manupilate to complete the listbox task.
Private Sub UserForm_Initialize()
' Populate the list with the Chart Names
Dim Chartz As ChartObject Dim Shet As Worksheet
For Each Shet In Worksheets For Each Chartz In ChartObjects ListBox1.AddItem Chartz.Name Next Chartz Next Shet
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I have a spreadsheet that has multiple sheets and each sheet has a graph, at oresent if i have to add a new sheet and graph i copy the last sheet, rename the tab then off we go, but i alway have to remember to also change the sheets graph title. is a mechanism so that if I change the Sheet Tab name then the chart will use the sheet tab as a chart title.
I'm trying to use some defined names to create charts. Though it works at the very begin, some extremely weird things happen when I try to develop further .....
Attached please find the test file, which simplifies my real case but shows the same problem.
Test.xlsx
In the test file, three names are defined as below:
the chart is very sample: "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line1,1)"
So far, it works very well. However, I just simply can't add the second line into the chart, for example, "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line2,2)"
I canNOT even change "Line1" to "Line2" in the "SERIES" formula!
I have created a chart but for some reason rather than the names appearing there are numbers. I did the chart successfully and then removed the data so that I could input another team's results and this happened.
In Column B I have a list of names separated by a comma in the following format: Doe, John. I need to have the format: John, Doe or John Doe. I could also move the last name to Column C if that would be easier. (ie. B1=John C1=Doe). I have tried a couple of examples from other places but they weren't what I needed.
I have got a workbook with about 200 sheets... Sheet1 > Sheet200
i need to delete about 100 sheets... sheet100 > sheet200
i then need to add the sheets back in but when i do the sheet numbers start from Sheet201... how do i get it to start from Sheet101 again or can i use some vba to change them later?
I have a folder structure like ex this c:my folders. Under this folder i have several folders and subfolders. I would like to use a macro to run through all this folders an sub folders and change the name of the first letters of all folders.
Ex LOP-100-APPL LOP-200-ORG
I would like to change LOP to PLP instead. I'm lazy and looking for a method for not doing this job manually. This should be done from the excel workbook.
Can I change Coulmn Header Names... What I mean to say that , We Have Generally Column Names A, B, C, D, ... I would like to Change these names what ever I Want..
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers() Dim managers1 As Range Dim names1 As Range Dim n1 As Variant Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
VB: Sub RenameTabs() For i = 1 To Sheets.Count If Worksheets(i).Range("C1").Value <> "" Then Sheets(i).Name = Worksheets(i).Range("C1").Value End If Next End Sub
I change my tab names with the above but than my hyperlinks breaks.
I have created 70 "templates" in my workbook. I would like to change the name of each template to the name contained on a list in the "Data" sheet. The list starts at a4, and may have 70 or more names. I want to cycle through each name, place it in cell c8 of the template, rename the template with the same name, then move on to the next name, rename the next template, place name in cell c8, and so forth. So far I have this, which creates the "template" and renames it according to the list, but it doesn't insert the name into cell C8 of each new sheet.
Sub NewSheets() Dim I As Integer Dim ws As Worksheet Dim sh As Worksheet Set ws = Sheets("Template")
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with:
Sub importdata() ' importdata Macro Range("A2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=Excel Files;DBQ=C:Documents and SettingsMy DocumentsTender Document(1).xls;DefaultDir=C:Documents a" _
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with: .....
So I have a graph with years present on the x-axis. I'm trying to format the axis from the years 1913-2012 (right now it's like 1900-2020). In the minimum and maximum range in the 'format axis' box these dates are listed as values instead of years (0-45,000). Is there a way to make these boxes just represent the years so i don't have to convert 1913 to the excel number equivalent?
I have a chart that needs to be positioned at the bottom of a table of data. (The table of data is created by some VBA which extracts data from another sheet based on user input). The chart is a pivot chart built on a pivot table from another worksheet. I've read the suggestion that the chart should be positioned above the table of data which I would do, however as there is already a series of tables and charts above, it would be too confusing for the user to see the chart out of sequence.
As I already have a fair bit of VBA to generate my existing sheet, I’d like to have something very simple that I can insert in various places depending when the user makes a change, which requires the chart to be repositioned. Something like:
Change chart position To Range ("B65536").End(xlUp).Row
I would like to create a bar chart which is linked to a drop down box (from data validation) that allows me to choose of the 3 sets of data to show in the x axis.
So if i choose "HALF YEAR", the bar chart will have two bars (one is "jan-jul" the other is "jul-dec") with the green section data if i choose "QUARTER YEAR", the bar chart will have four bars ("jan-mar", "apr-jun", "jul-sep", "oct-dec") with the blue section data if i choose "INDIVIDUAL MONTHS" i have twelve bars....with the red section data.
The attached file has a scatter chart in which all of the data is properly displayed except the X axis. How can I get my X axis to reflect the data I have selected?
I'm attempting to write a code that will change the colour of a series in a chart based on the name of each series. i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.
I am having a hard time figuring how to change the color of all the different text groups in a chart, header, axis, labels etc, and even the macro recorder has failed me this time - I have recorded the following, where I selected the graph, changed textcolor to white and then ended the recording - but without changing anything in the code, it returns an error on the second line (starting with "with"):
Run-time error '-2147024809 (80070057)': The value is outside of the boundaries (translated from danish)
ActiveSheet.ChartObjects("Chart1").Activate With ActiveSheet.Shapes("Chart1").TextFrame2.TextRange.Font.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorBackground1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0 .Transparency = 0 .Solid End With
From what I have read, it might be something with the textframe2 property.
I maintain Production data in an Excel Sheet and have the code which will create Chart for each Employee. Sample Sheet Attached. However, I want the Chart Range to be updated in a Single Chart and exported to a .Gif File.
I have 3 charts on 3 seperate worksheets. The data for each is sourced from a list of up to 100 but only the top 20, or all if less than 20 in full list, are displayed on the chart. A seperate worksheet ("Calcs") is used to sort all the data so that just the top 20 are sourced for the charts and they are displayed in descending order from max to min and alphabetically in the case of 2 or more names having the same value.
I have attached one ot the charts.
I need the y axis scale to auto-adjust to suit the max and min series 1 values. I need the y axis maximum to be 5 more than the max series 1 value (unless the value is 5 or less when axis should be 10) and the min y axis to be 3 less than the min series 1 value (unless the value is 3 or less when the axis should be 0)
I have done the formulae to sort out these conditions and put the required y axis values in a cell which the copies across to the chart sheet with the required max in cell M1 and the min in cell L1. These values will change when another macro runs.
I searched this site for the code to auto-adjust the scales and found one which I have tried to edit to suit my needs. This is the code I have ended up with, placed within the worksheet containing the chart....
My spreadsheet has multiple charts, each referencing a dynamic named range as its source data (e.g. "California1", "California2", etc). Each named range contains multiple series. I would like to make it easy for a single chart to quickly adjust to display different named ranges (e.g. view the chart with "California1" data, then update the same chart to view "California2" data).
I'd like to do this without having to right-click on the chart, select Source Data, and then type in "California2" as the Data Range. My question: is there a way to make the chart's Source Data Range dynamic so that I can quickly select the named range that I want to display? For example, would it be possible to tie the Data Range to a separate cell so that if I typed "California2" into that cell the chart would know to use that named range as its new source data?
I have an excel addin that contains chart templates. Without going into too much detail a user will select an option which will update these templates then move the charts along with the source data to a new workbook. My question is this; How do I change the chart references programatically so that the chart data now points to the source data in the new workbook.
I have a periodic task which involves moving some sheets from an existing workbook and presenting it in a new workbook. There are some diagram that I move as well from the worksheet. However, I also move the source data for the diagrams into the new workbook as well. Everytime I do this I need to change the source data to the new worksheet which is quite tiresome. Is there a smart hack to actually point the diagram source data to the new workbook?
