othersutff.vba

Sub Copy_Charts_and_Change_Source_Data()

    Dim DataSheetName1 As String, DataSheetName2 As String
    Dim sourceChartSheet As Worksheet, destChartSheet As Worksheet
    Dim chartObj As ChartObject
    Dim chartObjCopy As ChartObject
    Dim chSeries As Series
    Dim chartIndex As Integer
    
    'Names of the 2 data sheets
    
    DataSheetName1 = "CU-2"   'CHANGE THIS
    DataSheetName2 = "CU-8"   'CHANGE THIS
    
    'COPY CARTS AND CHANGE SOURCE DATA
    'Source sheet - the existing charts will be copied from this sheet
    
    Set sourceChartSheet = Sheets("CU-2")   'CHANGE THIS
    
    'Destination sheet - the charts will be copied to this sheet
    
    Set destChartSheet = Sheets("CU-8")   'CHANGE THIS
    
    chartIndex = destChartSheet.ChartObjects.Count
    
    For Each chartObj In sourceChartSheet.ChartObjects
    
        'Copy this chart to destination chart sheet, keeping its position
        
            chartObj.Copy
            destChartSheet.Range(chartObj.TopLeftCell.Address).PasteSpecial xlPasteAll
            chartIndex = chartIndex + 1
            Set chartObjCopy = destChartSheet.ChartObjects(chartIndex)
            chartObjCopy.Left = chartObj.Left
            chartObjCopy.Top = chartObj.Top
        
        'Change the sheet name in all series in the destination chart
        
        For Each chSeries In chartObjCopy.Chart.SeriesCollection
            chSeries.FormulaR1C1 = Replace(chSeries.FormulaR1C1, DataSheetName1, DataSheetName2)
        Next
    
    Next
    
End Sub


Sub LoopThroughCharts()
'PURPOSE: Loop through every graph in the active workbook
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

Dim sht As Worksheet
Dim CurrentSheet As Worksheet 'Is this needed?
Dim cht As ChartObject

Dim DataSheetName1 As String, DataSheetName2 As String
Dim sourceChartSheet As Worksheet, destChartSheet As Worksheet
Dim chartObj As ChartObject
Dim chartObjCopy As ChartObject
Dim chSeries As Series

Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet 'Is this needed?
Set sourceChartSheet = Sheets("CU-2")

For Each sht In ActiveWorkbook.Worksheets
    If ActiveWorksheet <> sourceChartSheet Then 'Ideally, I'd like to exclude a longer list of sheets to NOT receive the charts... need to figure out how to do that.
        'Delete existing charts
            For Each chartObj In ActiveSheet.ChartObjects
                chartObj.Delete
            Next
  End If
        'Copy charts from source to destination chart sheet, keeping their positions
        Set destChartSheet = ActiveWorksheet
        For Each chartObj In sourceChartSheet.ChartObjects
            chartObj.Copy
            destChartSheet.Range(chartObj.TopLeftCell.Address).PasteSpecial xlPasteAll
            chartIndex = chartIndex + 1
            Set chartObjCopy = destChartSheet.ChartObjects(chartIndex)
            chartObjCopy.Left = chartObj.Left
            chartObjCopy.Top = chartObj.Top
        
          For Each cht In sht.ChartObjects
            'cht.Activate
            'Change Series reference to current workhseet...
            For Each chSeries In cht.Chart.SeriesCollection
                    chSeries.FormulaR1C1 = Replace(chSeries.FormulaR1C1, "CU-2", sht.Name)
                Next 'Does this need something after the word next?  Why not?
  
              Next cht
  
Next sht


CurrentSheet.Activate
Application.EnableEvents = True

End Sub

vbastuff.vba

Private Sub Delete_Charts() 'This works do not edit.
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> "CU-2" Then
    If sht.ChartObjects.Count >= 1 Then
            sht.ChartObjects.Delete
            End If
    End If
Next sht

End Sub

Sub Copy_Charts()
    Dim DataSheetName1 As String, DataSheetName2 As String
    Dim sourceChartSheet As Worksheet, destChartSheet As Worksheet
    Dim chartObj As ChartObject, newChartObj As ChartObject
    Dim chartObjCopy As ChartObject
    Dim chSeries As Series
    Dim chartIndex As Integer
    
    'Names of the 2 data sheets
    
    DataSheetName1 = "CU-2"   'CHANGE THIS
    DataSheetName2 = "CU-8"   'CHANGE THIS
    
    'Source sheet - the existing charts will be copied from this sheet
    Set sourceChartSheet = Sheets("CU-2")   'CHANGE THIS
    
    'Destination sheet - the charts will be copied to this sheet
    Set destChartSheet = Sheets("CU-8")
    
    For Each chartObj In sourceChartSheet.ChartObjects
        Set newChartObj = chartObj.Duplicate.Chart.Parent
        newChartObj.Top = chartObj.Top
        newChartObj.Left = chartObj.Left
        newChartObj.Chart.Location xlLocationAsObject, destChartSheet.Name
        Debug.Print(newChartObj.Chart)

'        For Each chSeries In newChartObj.Chart.SeriesCollection
'            chSeries.FormulaR1C1 = Replace(chSeries.FormulaR1C1, DataSheetName1, DataSheetName2)
'        Next

    Next chartObj

End Sub