Manipulating range of a line chart using macros and buttons

Hey guys,

I have a few questions about macros using excel 2007, but I am just going to start with this one for now.

I have a line chart base on daily data from January of 1970 until now, what I am trying to do is create macro and buttons to go with it that would allow me to control the start and end date displayed in my chart. Whether it’s a year, just a month or week and even a day. Any ideas on how I can accomplish that.

PS I attached the document I am using to practice this on.

Thanks, Bobby

Suggestion...

Create another tab. You will continue to use the first tab when adding new data each day. This won't change.

However, the second tab will contain the chart. It will also contain a cell for the user-desired START and END date. When these dates are filled in, and a custom-button clicked, the macro you write will then copy the entire desired range of data from Sheet 1 to Sheet 2. The graph then grabs the data from Sheet 2 and displays it.

The data then, from Sheet 2, is constantly changing, being overwritten, etc. This is okay. Your original data on Sheet 1 is safe and is never modified.

The macro is a simple, DO UNTIL Loop, to loop through all of the the data in Column A of Sheet 1, looking at the dates. It copies over the entire range desired. The Pseudocode is something like:

Start_Date = Cells(1,1) : End_Date = Cells(2,1)

DO
copy data from Sheet 1 to Sheet 2
LOOP UNTIL Column A's data < Start_Date

I under stand what you are telling me but for some reason it is not working I think I might be writing the code wrong....and thank you for your help

Originally Posted by Ed_Collins

Suggestion...

Create another tab. You will continue to use the first tab when adding new data each day. This won't change.

However, the second tab will contain the chart. It will also contain a cell for the user-desired START and END date. When these dates are filled in, and a custom-button clicked, the macro you write will then copy the entire desired range of data from Sheet 1 to Sheet 2. The graph then grabs the data from Sheet 2 and displays it.

The data then, from Sheet 2, is constantly changing, being overwritten, etc. This is okay. Your original data on Sheet 1 is safe and is never modified.

The macro is a simple, DO UNTIL Loop, to loop through all of the the data in Column A of Sheet 1, looking at the dates. It copies over the entire range desired. The Pseudocode is something like:

Start_Date = Cells(1,1) : End_Date = Cells(2,1)

DO
copy data from Sheet 1 to Sheet 2
LOOP UNTIL Column A's data < Start_Date

This is what I had in mind.
It only took a few minutes to put together.
It's crude and probably needs to be polished and cleaned up, but it should get you started.

snp historical prices11-modified.xlsm

that's pretty much what I need for the first part of this project, I'm going to hold off from moving for now until I fully get how to do this. Thanks for your help, do you know of any books or site you could recommend so that I learn to use VBA something that goes from beginner to advance if possible?

once again thanks. bobby