Chart series range not changing when copying sheet

[lac87]

I'll preface this by saying charts are not my forte, so I hope I'm using the right lingo to explain myself clearly.
I have a chart with 3 series referencing cells within the worksheet the chart is in. I need to be able to copy the worksheet and have the chart still reference the sheet it's in. The problem I'm having is when I copy the worksheet, only 2 of the series update to reference the new sheet name. The other 2 series, the Y axis reference updates to the new sheet name but the X axis continues to reference the original sheet. How can I get the x axis of those 2 series to reference the sheet the chart is in?
Screenshot below shows blue line with 3 data points & ZMW matching the first of those data points - these are the 2 series with correct references. LW & TOW points should match the remaining 2 data points of the blue line, but they're offset on the x axis due to the incorrect sheet references.

 

[lac87]

Edit. "I have a chart with 3 series" - should say 4 series.

 

[lac87]

Anyone?

 

[NdNoviceHlp]

Hi Iac87 it doesn't seem that your having much luck with this one. Care to share how you're copying the sheet? Manually or VBA? Do the charts contain named ranges for series? I'm not clear, is your chart data on the same sheet as you're copying or does it reference another sheet? Maybe this will bump will help. Dave

 

[lac87]

Hi Dave, I've copied it both ways - I have a macro (in a different workbook) that opens the workbook and copies the sheet, and I've also copied it manually. Same problem both ways.

No named ranges involved.

The chart has a couple of series that use data from a different sheet, but they copy fine. The four series mentioned in my post all refer to cells on the same sheet as the chart.

 

[frabulator]

I would record a macro of you making the chart in question. Inside that macro you should be able to see how Excel makes the charts using VBA. Use that to hard code in X and Y value paths. That way, you can recreate the chart anywhere you want instead of worrying about copy and paste (which is very finicky at best).

 

[NdNoviceHlp]

Hi Iac87. Trial copying the sheet(s) using a collection and see if this helps. Run this code in the wb that U want the sheet with the chart copied to. You will need to change the wb file path and sht name for the chart to suit. HTH. Dave

Sub test()
'open selected file
Dim Sht As Worksheet, ShtCollect As Collection
Dim FSO As Object, FilDir As Object, Cnt As Integer
Set FSO = CreateObject("Scripting.FilesystemObject")
'change file path to suit
Set FilDir = FSO.getfile("C:\yourfoldername\YourFileName.xlsm")
Workbooks.Open Filename:=FilDir
'load sheets in collection
Set ShtCollect = New Collection
For Each Sht In Workbooks(FilDir.Name).Sheets
' ***** adjust ChartShtName to your sht name
If LCase(Sht.Name) = LCase("ChartShtName") Then
ShtCollect.Add Workbooks(FilDir.Name).Sheets(Sht.Name)
Exit For
End If
Next Sht
'copy collection sheets to wb
For Cnt = 1 To ShtCollect.Count
ShtCollect(Cnt).Copy After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next Cnt
'close wb
Workbooks(FilDir.Name).Close SaveChanges:=False
Set FSO = Nothing
End Sub

 

[lac87]

Thanks for your replies. I should have mentioned, the workbook needs to be iPad-friendly so I can't copy the sheet exclusively using VBA.

Was hoping there would be an explanation as to why just those two axis references weren't playing the game. Since posting, I've also found that occasionally they do copy correctly, but haven't been able to figure out when/why.

The sheet being copied is part of a template that most of the time doesn't need many copies of this sheet, so I wanted to keep it smaller and just copy extras when required. Looks like I might just have to include the max number of copies in the template to avoid having to copy it. Feasible, just not as nice.