Chart Macro: Update Using A For-loop

Feb 11, 2009

I'm trying to make a scatterplot so what I did was recorded a macro and now I want to be able to update it using a for-loop. Here's my code.

View 4 Replies


ADVERTISEMENT

Macro - Loop Through Worksheets And Update Linked Cells

Jul 19, 2012

I have a Database and every entry in the database has its own corresponding sheet with the data in a more viewer-friendly format. I want to assign a macro to a button to loop through all the sheets, copy the cells and paste to the database (to update the database).

Also, nothing prevents the user from deleting the sheets or mixing them. So is there a way to take the reference number from the database in column B, search for the corresponding sheet which has the reference number in cell B3 and then update it accordingly.

Code:
Sub Button19_Click()
Dim WS_Count As Integer
Dim I As Integer

[Code]...

View 1 Replies View Related

Long Calculation Loop Upon Update

Sep 2, 2009

My current task is to create a spreadsheet that will auto-hide/show rows based upon values in other cells. I am currently about halfway 40% complete with this aspect of the file. I've read over many forums, and the best what that I've seen individuals do it on other sample spreadsheets posted were a =Rand() in a random cell somewhere in the sheet, and a Private Sub worksheet_calculate(). I'm far from a VBA expert. I'm trying to learn as I go along. Here is my current VBA:

View 4 Replies View Related

Update Chart X Scale With VBA

Feb 16, 2012

I am using a VBA macro which performs a Yahoo webquery to obtain stock data. After the data are downloaded, a chart is updated with the new data.

The current macro updates the scaling on the price axis just fine, however, if I change the time frame the x axis, i.e, time/date axis is either spread out to far or jammed up.

The code for re-scaling the the y axis is shown below:

Code:
Sub UpdateScale()
Dim ChartVar As Chart
Dim lMax As Long, lMin As Long
On Error GoTo ScalingProblem
'Assigns the values in the Min and Max ranges to variables.

[Code]....

Any similar sub that would also rescale the x axis as needed when the data intervals are changed?

View 6 Replies View Related

Unable To Update Line Chart?

Feb 21, 2014

I had a couple of days ago..

I can get the "Daily %" to update just fine but for the life of me cannot get the "Total Number of Patients" to update.

View 3 Replies View Related

Using VBA To Update Chart Data - 2 Ranges

Dec 14, 2012

I am making a macro to update the data on a bunch of charts. This is what I got from the macro recorder when I selected the data manually.

Code:
Sub testthree()
ActiveWorkbook.Charts("Julian's Words").Select
ActiveChart.SetSourceData Source:=Sheets("Super Sight Chart Data").Range( _
"C1:H1,C5:H5"), PlotBy:=xlRows
End Sub

I have created the part of the macro that will go to the data table and define the two ranges I need to use.

Code:
Sub Adjust_Graphs()
Dim lastdate As String
Dim daterange As Range

[Code] .....

However, this code is selecting the entire range C1:H5. How can I get the code to take JUST the two lines I need?

Exmaple: The X axis needs to be C1:H1, and the Y axis needs to be C5:H5.

I also tried this, without success:

Code:

Dim combrange As Range
Set combrange = Union(daterange, wordrange)
ActiveWorkbook.Charts("Julian's Words").SetSourceData Source:=Sheets("Super Sight Chart Data").Range( _
combrange), PlotBy:=xlRows

View 1 Replies View Related

Automate Chart Source Update

Aug 30, 2007

I'm trying to change the source data for a worksheet for a series of charts. The source data appears in a series of tables. There are about 30 such tables and associated charts on the worksheet. (I need to duplicate this many times over, which is why I need to automate.). The chart names seem to increment by 2 (i.e. Chart 1, Chart 3, Chart 5) And the source data for the charts increment by 9: BM95:BP102, BM104:BP111, etc. How can I automate this? Also, I'm trying to automate changing the reference in the title of the chart. This reference increments rows by 9. When I use the macro recorder to do this, I get the following

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BM95:BP102")

I'm unable to generate any code that allows me to change the reference of the chart title.

View 4 Replies View Related

Automatically Update Graph/Chart

Mar 17, 2008

I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that? The code for extracting from the workbook is the following:

Sub Extract()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
ActiveWorkbook.Sheets("CURRENCIES").Select
ActiveWindow.SelectedSheets.Delete
myFileName = Application. GetOpenFilename("Excel files,*.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If................

View 4 Replies View Related

Automatic Chart Update With New Data Entry

Apr 12, 2006

I've been updating all the charts manually each time I update the entries in the worksheet which charts are created from in the same workbook. Is there any way (eg. by use of macros or VBA) that I can have all the charts updated automatically when data ranges are updated each time?

View 2 Replies View Related

Chart To Automatically Update When New Data Is Added

Aug 19, 2006

I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..

I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).

The new row will be added each time right below the header, right above the
most recent data given. So a row will be added above row 8!

View 9 Replies View Related

Dynamically Update Chart Source Range

Aug 31, 2007

I'm trying to create a chart and set its source dynamically.

I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,

ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))

And by using the chart wizard.

Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")

Setting the XValues property gives an "Unable to set the XValues property of a series class".

Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.

I've also tried

resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring

When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.

Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.

View 7 Replies View Related

Automatic Chart That Use Formula Result To Update Without Login

Jul 18, 2014

I want have a chart that use Formula result to update automatically. I know there is some vb methods to do this but my job is different and that ways work when values enter manually but i need a way that use formula result to update and don't make chart as long as existing values. I attached a sample of i need and i did explanation on it. The Salesman's are enter to column A by a VB and Their values of cost or earning are enter by a formula.

View 2 Replies View Related

Interval-Y In Duplicate Chart Go Offset When Data Update

Mar 2, 2014

I have two charts (Chart A and B) and 1 duplicate chart of chart A ("Chart A", didnt change the change name so remain the same).

"Chart A" is overlay above Chart B to see the actual different.

When the data is updated, interval-Y in "Chart A" is different (by +-10% offset) than Chart A which support to be the same.

At this stage, comparison is not possible.

The interval-Y is link to vb.

Checked when there is offset the maximum and minimum offset is different and else I cant find any other problem.

Do I have to state that there are two Chart A in the vb?

View 1 Replies View Related

Automatic Chart Title Update From Filtered Criteria

May 2, 2008

I am having difficulty figuring out how to get my chart title to automatically update when my chart changes via filtered criteria. I can get it to change by clicking the title bar on the chart, but would like the title to automatically update as soon as I select new criteria and the chart changes.

View 9 Replies View Related

Chart Loop To Change Axes Dynamically

Oct 11, 2006

I've got an excel worksheet that contains charts 5-14, each with different max axes on primary and secondary scales. Right now the code below works to adjust the axes, however I have pasted this into a script 10 times, once for each chart with different references to the chart number, the max primary axis and max secondary axis. how I could write the following as a loop so that I don't need to have to repeat this routine in my code?

Sub ChartsBillingsShare()
ActiveSheet.ChartObjects("Chart 5").Activate
With ActiveChart
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = Range("Y6").Value
End With.................................

View 4 Replies View Related

Loop Through Worksheets And Format Chart 1 - Runtime Error

Jun 27, 2014

I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or method" at this line:

Code:
.ActiveChart.Shapes("TextBox 1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change"

Here is my for each loop:

Code:
Sub UpdateTextAllStateCharts()
'This macro loops through worksheets in a list and performs the assigned task
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("C:C")
If sheet_name.Value = "" Then

[Code] .....

This line is also causing the same error:

Code:
With .Characters(42, 68).Font

View 2 Replies View Related

Loop Through Data And Create Chart With A Number Of Series

Apr 8, 2008

I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....

View 9 Replies View Related

Chart Hyperlinks: Attach A Macro So That When The Chart Is Clicked It Returns To Sheet - Home

Feb 24, 2007

I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.

View 7 Replies View Related

Update Data By Ado - Change/delete Data And Then Run Macro For Update Data In Source Spreadsheet

Dec 6, 2006

I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.

Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub

It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used

.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0

after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).

View 2 Replies View Related

Worksheet Change Macro Takes Too Much Time When Run With Update List Macro

Feb 1, 2009

I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.

My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?

View 5 Replies View Related

Update Of Worksheet Using Macro

Mar 5, 2008

I need to be able to open a workbook named "Cost Price List" and press an update button which will open Another workbook called "Numerical Pricelist" that will then look up the part number of the item and change the corresponding description only, using the "Numerical Pricelist as the correct master document. There are approx 5000 part Numbers but i have cut a small sample section.

View 9 Replies View Related

Macro To Update Graphs

Oct 11, 2008

I have multiple graphs that read from rows varying in length from 2 to 60 columns long, depending on user input. I need help in creating a macro that readjusts the graphs to read from smaller/larger data set.

I created one that said: If user input = 1, then set graph to (A1:A1), If user input = 2, then (A1:B1), but this is really inefficient. Is there a better way?

View 11 Replies View Related

Update The Date Macro

Nov 26, 2008

i have this macro that sorts out a few cells

View 4 Replies View Related

Update Textbox From Macro?

Mar 17, 2012

I am trying to update a text box from a macro. The text box is outside of the chart area. I am using these two lines and it is not working.

Sheets("chart1").Select
ActiveSheet.Shapes("Text Box 2").Text = strSql ' a string variable

View 7 Replies View Related

Update Cell Only Via Macro

Feb 13, 2007

away to have a cells formula only up date via a Macro

Example
A1+B1 = C1
the formula is only done through a Macro so that if it is disabled C1 would be empty?

I guess the trick is how does the macro update when a value is entered in A1 or B1?

View 9 Replies View Related

Macro Won't Update Pivots

Jul 10, 2008

I have a macro that is supposed to update 3 pivot tables with the change to just the first one but it doesn't work. Can anyone help?

Here is my code but nothing happens when I change the first pt (C & S). It doesn't update the other 3 tables.

Private Sub Worksheet_Calculate()
Dim PF1 As PivotField
Dim PF2 As PivotField
Dim PF3 As PivotField
Dim PF4 As PivotField
Dim x As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Set PF1 = ActiveSheet.PivotTables("PivotTable4").PageFields("State")
Set PF2 = ActiveSheet.PivotTables("PivotTable3").PageFields("State")
Set PF3 = ActiveSheet.PivotTables("PivotTable2").PageFields("State")
Set PF4 = ActiveSheet.PivotTables("PivotTable1").PageFields("State")
x = PF1.CurrentPage
PF2.CurrentPage = x
PF3.CurrentPage = x
PF4.CurrentPage = x
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Pivot Table 4 = C & S this is one I want to make the change to
Pivot Table 3 = Sales Pivot
Pivot Table 2 = 2005 Pivot
Pivot Table 1 = SMG Pivot

View 9 Replies View Related

Macro- Update Automatically

Oct 23, 2009

I have three columns as below,

Column A current Amount
Column B Increse %
Column C Revise Amount

If I put revise amount in column c, than Column B update automatically or if I put % in column B than column C update automatically, update must be live

View 9 Replies View Related

Auto Update Macro

May 10, 2006

I have a spreadsheet of 30 or so tabs, all of identical layout, with columns B to AS used. Each day I have to drag a row down to collect data from various other spreadsheets, on each of the 30 tabs (different data in each tab, but same format), i.e. today I dragged down B216:AS216 down to B217:AS217 for all 30 tabs. I could record a Macro on the first tab and then play it for the remaining 29, but I have to record the Macro each day so it drags down the next row down.

I am after a Macro that will drag down the columns to the current date (which is in column A and is already entered down to the end of the year) on all tabs, so if I left it for 5 days etc, and then ran the Macro, it will drag down 5 rows. But if I had to run it 5 times that wouldn't matter. A button on the first sheet would be brilliant too, meaning I could open it up, click the button, and then all 30 tabs would drag down to todays date.

View 4 Replies View Related

Macro To Update List

Jun 17, 2006

I ran into a dead with this simple but overlycomplicated task in Excel. How can I write a macro to update one workbook from many workbooks? My case. I have workbook A with 2 sheet tabs, sheet1 contains 6 columns: one for date, one for 0-20%, 21-40% ... 81-100%. Each of the rows underneath the heading contains a single data value ( total) from each of the 5 sheet tabs from the workbook being created each week of the month. Sheet2 build the chart reflecting the data gathered on sheet1.
I need a macro that will update workbook A sheet 1 or add the new data generated that particular week down the columns for each of the columns stated? How can I achieve this?

View 5 Replies View Related

Macro Update Where Row Count Is Variable

Oct 1, 2013

Every week I get a file download from SAP with multiple tabs and on each tab are variable number of rows (can by 10 or 20 or 200, doesn't matter). I recorded a macro to add additional information that is required for the team prior to discussion each week (namely column D, E, K and O).

I am not experienced w VB but I know there has to be a way to account for the fact the rows vary each week. See my code below (pasted only a portion covering a couple tabs but you can see in the first part how it was 11 rows this week. Next week could be 25 rows. So how do I properly reflect that in the macro?).....

VB:
Sheets("BPTO").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],css,2,FALSE)),(VLOOKUP(RC[-2],css,2,FALSE)),(VLOOKUP(RC[-1],css,2,FALSE)))"
Range("E2").Select

[Code] ......

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved