Excel VBA update Scatter Plot

[Lookatthis123]

Hi I was hoping to get some help on some code that will update my scatter plot on click of button on Excel sheet. I'm using Excel 2007. Data is on Pivot Tables and the row range will always change.
Basically I have Scatter plot that collects data from two separate Pivot Tables on two sheets.

one called “customer” and sheet two called “All customers.”

Sample: on each sheet.
X Y
Weight Cost
100 $50
200 $60
145 $45

X is my weight and Y is my Cost
on click code shall go to customers sheet get X (Weight Starts on cell B9) and Y (Cost Starts on Cell D9) find last row in columns update scatter plot then go to All customers sheet get X and Y and update Scatter Plot.

 

[scott-atkinson]

I don't use Excel 2007 so will not be able to help you, but if you do not get any help from this forum, there is a specific Excel Help forum that you can try, it can be found at the following URL

http://www.excelforum.com/

They will be able to help you there.

 

[DanWall]

I find the easiest way, is to add another range on a hidden sheet, and use a lookup in that range to get the correct row's values.

Then reference the scatter plot to that range.

It will update automatically.

 

[Lookatthis123]

thank you both DanWall I will take your advice I have to look up on how to do named ranges and apply to my sheet.

 

[DanWall]

No need for a named range....

I'll post an example shortly of what I mean.

 

[DanWall]

Here you go.

It's not as complex as what you're trying to do, but it shows an example of how you would use a lookup to determine the correct row to use.

It should get you started at least.

 

[Lookatthis123]

DanWall thank for showing me example I will Try this looks like it can work for me.
Thank you very much.