Problems setting a chart range

[sturly]

Hi
First timer, so please bear with me. I have the code below running to prompt for a new sheet name, create it, copy data and a chart to it, and up to that point it all works fine. Problem is I need the chart on the new sheet to point to the data on that sheet and not from whence it came ?
Note : The Data is always in the same position on each sheet C9:I9

Any guidance would be greatly appreciated ?

KR ... Ian

Code Below :
Sub Button2_Click()

Dim newwks As Worksheet
Dim OldWks As Worksheet
Dim NewName As String
Dim UpperLeftCell As String
Dim numbPoints As Long
Dim newsht As String
Dim oldsht As String


Set OldWks = Worksheets("Tables")

NewName = Trim(InputBox(prompt:="what do you want to call it?"))

If NewName = "" Then
Exit Sub
End If

Set newwks = Worksheets.Add
On Error Resume Next
newwks.Name = NewName
If Err.Number <> 0 Then
MsgBox "Something went wrong with the naming!" & vbLf & _
"Please change: " & newwks.Name & " to what you want."
Err.Clear
End If

OldWks.Range("Chart_Data").Copy
newwks.Range("b2").Select
ActiveSheet.Paste
newwks.Range("b2:k9").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = False

newwks.Range("B2:L35").Select

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$B$2:$L$35"
With ActiveSheet.PageSetup
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

"THIS IS THE BIT I AM HAVING PROBS WITH"

newwks.Cells(1, 1) = "'" & newsht
newsht = ActiveSheet.Text

ActiveSheet.ChartObjects().Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=newsht & !R9C3:R9C10"

End Sub

[MartinLiss]

Are you doing this coding from within Excel?

[sturly]

Hi

Many thanks for your reply, yes i am doing this from within Excel

[MartinLiss]

Okay then I've moved your thread here where VBA questions are answered.

[westconn1]

"THIS IS THE BIT I AM HAVING PROBS WITH"

so what happens?
wrong result?
nothing?
error?

[sturly]

Hi

Thanks for your reply, as stated, all code works up until the bit I have highlighted as "THIS IS THE BIT I AM HAVING PROBS WITH"

From here is where I am looking at changing the chart values range to those in the newly created sheet.

[westconn1]

all code works up until the bit I have highlighted

so what happens then?

[sturly]

Sorry

I had better try and explain a little clearer. The last part of code beneath :

newwks.Cells(1, 1) = "'" & newsht
newsht = ActiveSheet.Text

ActiveSheet.ChartObjects().Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=newsht & !R9C3:R9C10"

I san attempt to re-assign the values to the current worksheet, I tried using the previously defined "newsht" variable but nothing happens. I cannot hard type the sheet name as this changes each time the user runs and generates a new sheet.

HTH

Ian

[westconn1]

you need specify which chartobject to work with

values has to be an array, or possibly a range object
possibly the formula property of the series collection, is where you can assign "=newsht & !R9C3:R9C10"

if you have on error resume next anywhere, remove it so you can see any errors that occur
if you are using inline error handling at some point make sure to reset error handling after that point