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