excel 2007 chart macro graphs data only from sheet on which macro was recorded

i've got a few dozen sheets full of data that i need to graph.
all sheets are set up/formatted the same, therefore, i will be using the same data range for each chart. (ex.X=$C$6:$C$1000 , Y=$D$6:$D$1000)
the problem i need to overcome:
when i run the macro on sheet 'B' that I recorded on sheet 'A', it still grabs the data from sheet 'A'.
in the example below i recorded the macro on sheet ea13-0243. when i switch to sheet ea13-0244 and run the macro, it uses the data from ea13-0243. What do i need to change in the program to do away with the 'ea13-0243' that will allow the active sheet data be graphed?
Thanks for your help

Please Login or Register  to view this content.

This should allow it to work on whatever sheet is the active sheet.

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.

Here's a way to set the data without having to go to a higher number than needed for the last row.

Please Login or Register  to view this content.