I'm trying to add charts programmatically by passing the cell range to a function. Simple version is like this:

Application.Run _
"myPlotFunct", ActiveSheet, Range(Cells(1,1),Cells(5,5))

I have no problem with the first chart but the 2nd, 3rd,... don't like this command because the ActiveSheet is a chart after each run through the function. My solution is:

set mySheet = ActiveSheet
Application.Run _
"myPlotFunct", ActiveSheet, Range(Cells(1,1),Cells(5,5))
mySheet.Activate

This works fine but is awfully clunky. How do I pass the range over to the function without having to switch pages in between? I've tried:

set mySheet = ActiveSheet
Application.Run _
"myPlotFunct", mySheet(Range(Cells(1,1),Cells(5,5)))

and I've also tried:

set mySheet = ActiveSheet
Application.Run _
"myPlotFunct", mySheet.Range(Cells(1,1),Cells(5,5)))

No luck so far.