Dynamic vs hardcoded macro

**wptaylor4** · 06-11-2013, 10:09 AM

Hey folks,

New to the site and to programming, but I think this might be an easy one for the seasoned pros on here.

I need a chart that shows the last 36 months of data I've put in. I also would like to keep the old data and just keep adding to the columns for every new month, so I would like a macro that I can have move the data captured down a row every month. I recorded one, but it hard codes to the same set of 36 rows, as opposed to moving down a row every month. Any help? I have included the macro I recorded called "chartupdater" below:

Sub ChartUpdater()
'
' ChartUpdater Macro
' Once the numbers get in, this is how to update the chart
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range( _
"APPROVED!$A$3:$C$3,APPROVED!$A$132:$C$179")
End Sub

**AlphaFrog** · 06-11-2013, 11:36 AM

Originally Posted by wptaylor4

Hey folks,

New to the site and to programming, but I think this might be an easy one for the seasoned pros on here.

I need a chart that shows the last 36 months of data I've put in. I also would like to keep the old data and just keep adding to the columns for every new month, so I would like a macro that I can have move the data captured down a row every month. I recorded one, but it hard codes to the same set of 36 rows, as opposed to moving down a row every month. Any help? I have included the macro I recorded called "chartupdater" below:

Sub ChartUpdater()
'
' ChartUpdater Macro
' Once the numbers get in, this is how to update the chart
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Range( _
"APPROVED!$A$3:$C$3,APPROVED!$A$132:$C$179")
End Sub

Your example code charts 48 rows
APPROVED!$A$132:$C$179

This code will chart the last used 48 rows on sheet Approved. Change the 48 to suit.

Please Login or Register  to view this content.

**MarvinP** · 06-11-2013, 11:38 AM

Hi wptaylor4 and welcome to the forum,

I think you should simply start adding data to the bottom of your table and not "bump" the data down. Find a screencast of how I imagined doing this problem. I start out by making some fake data using the RandBetween function to create some dates and then X and Y values to graph.

Then I create a Pivot Table and Chart to display the data. You may find this in a different place in 2010 vs my 2013 version of Excel. I think Pivot Chart is in Insert and Pivot Table, while the 2013 Icon is new.

Then I change the Date range of the Pivot chart to AFTER some random date. This is to see how it works. Then I record a macro to see what Excel VBA is doing with all these keystrokes. I find where the date is in the VBA code and change it to "CStr(Date - 3 * 365)" which is 3 years ago from today.
I put that into the VBA code and now have a macro that will do what I think you want.

See http://screencast.com/t/cFulMIdYti for the steps described above.