如何用VBA复制Excel统计输出区域至新工作表并让图表数据独立？

问题根源 #

直接复制包含图表的区域时，图表的数据源仍指向原工作表的单元格引用。当清空原工作表后，这些失效的引用导致图表无法加载数据。

解决方案：修改图表数据源指向新工作表 #

通过VBA复制区域后，遍历新工作表中的所有图表，将其数据源引用从原工作表替换为新工作表，确保图表绑定到新表的本地数据。

以下是完整的宏代码示例：

Sub CopyStatsWithLinkedCharts()
    Dim sourceSheet As Worksheet
    Dim newSheet As Worksheet
    Dim sourceRange As Range
    Dim cht As ChartObject
    Dim ser As Series
    Dim oldSheetRef As String
    Dim newSheetRef As String
    
    ' 配置原工作表（替换为你的实际工作表名称）
    Set sourceSheet = ThisWorkbook.Worksheets("统计输入表")
    ' 定义需要复制的统计输出区域
    Set sourceRange = sourceSheet.Range("AA:AT")
    
    ' 创建新工作表并命名
    Set newSheet = ThisWorkbook.Worksheets.Add
    newSheet.Name = "统计结果_" & Format(Now(), "YYYYMMDD_HHMMSS")
    
    ' 复制源区域到新表（包含数值、格式和图表）
    sourceRange.Copy
    newSheet.Range("A:T").PasteSpecial xlPasteAll
    
    ' 替换图表数据源的工作表引用
    oldSheetRef = sourceSheet.Name & "!"
    newSheetRef = newSheet.Name & "!"
    
    For Each cht In newSheet.ChartObjects
        ' 遍历图表中的所有数据系列
        For Each ser In cht.Chart.SeriesCollection
            ' 将系列公式中的原表引用替换为新表引用
            ser.Formula = Replace(ser.Formula, oldSheetRef, newSheetRef)
        Next ser
    Next cht
    
    ' 清除剪贴板，避免Excel提示
    Application.CutCopyMode = False
End Sub

关键细节说明 #

1. 引用替换逻辑：通过替换图表系列公式中的工作表名称前缀，将数据源从原表切换到新表，无论原表名称是否包含空格都能生效。
2. 多系列处理：循环遍历图表的所有SeriesCollection，确保所有数据系列都完成引用更新。
3. 命名规范：新工作表使用时间戳命名，避免重复命名冲突。

额外注意事项 #

• 如果原统计区域使用了命名区域，需要同时将命名区域复制到新工作表，或修改命名区域的引用指向新表。
• 测试前可手动查看图表的系列公式（右键图表→选择数据→编辑系列），确认原引用格式是否与代码中的替换逻辑匹配。

内容的提问来源于stack exchange，提问作者Jacco Koet