从另一个工作簿（包括图表）复制工作表 [英] Copy worksheet from another workbook including charts

问题描述

我想从另一个工作簿复制一个工作表并替换ThisWorkbook中的工作表。但是，我不想删除ThisWorkbook中的工作表，因为在其他工作表上有引用该特定工作表的公式。通过首先删除工作表，我的公式将最终显示为#REF。

I want to copy a worksheet from another workbook and replace a sheet in ThisWorkbook. However, I do not want to delete the sheet in ThisWorkbook, since I have formulas on other worksheets refering to this certain worksheet. By deleting the worksheet first, my formulas will end up as #REF.

因此，我编写了以下代码，但该代码不会复制图表：

Therefore I have written the following code but this code does not copy charts:

Sub Copy_from_another_workbook

    Dim wb As Workbook
    Dim sWorksheet As String

    ThisWorkbook.Worksheets("Destinationsheet").Cells.ClearContents
    Set wb = Workbooks.Open(ThisWorkbook.Worksheets("input").Range("sFileSource"), ReadOnly:=True, UpdateLinks:=False)
    sWorksheet = ThisWorkbook.Worksheets("input").Range("sWorksheetSource")

    wb.Worksheets(sWorksheet).Cells.Copy
    ThisWorkbook.Worksheets("Destinationsheet").Activate
    ThisWorkbook.Worksheets("Destinationsheet").Range("A1").Select
    Selection.PasteSpecial xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
    Selection.PasteSpecial xlPasteColumnWidths
    Selection.PasteSpecial xlPasteFormats
    Selection.UnMerge

    wb.Close

End Sub

此代码不会抛出错误，但不会复制图表。我还没有找到使用pastespecial复制图表的方法，并且从此帖子，表明您在选择范围后不能使用粘贴方法。

This code trows no errors but does not copy charts. I have not yet found a way to copy charts with pastespecial, and I understood from this post that you can not use the Paste method when ranges are selected.

如何粘贴包括图表在内的数据并仍然可以使用pastespecial，因为我也不想粘贴公式？

How to paste the data including charts and still being able to use pastespecial since I do not want the formulas to be pasted as well?

还是有另一种方式来实现所需的结果？

Or is there another way to achieve the required outcome?

推荐答案

将代码更改为：

Sub Copy_from_another_workbook

    Dim wb As Workbook
    Dim sWorksheet As String
    Dim rCell As Range

    Set wb = Workbooks.Open(ThisWorkbook.Worksheets("input").Range("sFileSource"), ReadOnly:=True, UpdateLinks:=False)
    sWorksheet = ThisWorkbook.Worksheets("input").Range("sWorksheetSource")
    wb.Worksheets(sWorksheet).Copy before:=ThisWorkbook.Worksheets("Destinationsheet")

    ThisWorkbook.Activate

    For Each rCell In ThisWorkbook.Worksheets("SheetWithFormulas").Range("b1:c30")
        rCell.Formula = Replace(rCell.Formula, "Destinationsheet", "'" & sWorksheet & "'")
    Next

    ThisWorkbook.Worksheets(sWorksheet).Cells.Select
    Selection.Copy
    Selection.PasteSpecial xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
    wb.Close

    ThisWorkbook.Worksheets("Destinationsheet").Delete
    ThisWorkbook.Worksheets(sWorksheet).Name = "Destinationsheet"

End sub

这篇关于从另一个工作簿（包括图表）复制工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！