使用 VBA 將 Excel 公式複製到另一個工作表

若要使用 VBA 將公式從一個 Excel 工作表複製到另一個工作表，請將來源區域的大小指定FormulaR1C1為目標區域的大小。這樣可以保留公式格式，而不會複製儲存格格式，並且相對引用會自動調整以適應目標儲存格。下面的巨集使用工作表名稱，因此它不依賴哪個工作表處於活動狀態。

開始之前

請使用桌面版 Excel；網頁版 Excel 可以開啟包含巨集的工作簿，但無法建立、執行或編輯 VBA 宏，詳情請參閱 Microsoft 的網頁版 Excel 指南。請確認工作表名稱以及來源位址和目標位址。在本例中，公式位於 `<table>` 中Source!C2:C10，並將放置在 ` <table>` 中Summary，起始位址為 `<table> C2`。

範例中使用了ThisWorkbook`<workbook>`，這表示巨集程式碼儲存在該工作簿中。請將巨集保留在該工作簿中，如果程式碼儲存在其他位置，則需要變更工作簿參考。工作表名稱選自該工作簿的Worksheets集合；引號中的名稱必須與工作表標籤完全相符。

使用 VBA 將公式複製到另一個工作表

從 Excel 的「開發工具」標籤開啟 Visual Basic 編輯器，選擇「Visual Basic」，然後選擇「插入」>「模組」。將以下過程貼到模組中：

Sub CopyFormulasToAnotherSheet()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim targetCell As Range
    Dim targetRange As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("C2:C10")
    Set targetCell = wb.Worksheets("Summary").Range("C2")
    Set targetRange = targetCell.Resize(sourceRange.Rows.Count, _
                                        sourceRange.Columns.Count)

    targetRange.FormulaR1C1 = sourceRange.FormulaR1C1
End Sub

更改 `<source> Source`、Summary`<source>` 和範圍位址以符合您的工作簿。 `<source>`targetCell是第一個目標單元格；Resize`<source>` 會使目標單元格的高度和寬度與來源單元格相同，這是多單元格賦值所必需的。對於單一公式，請將來源儲存格設定為一個儲存格，例如 `<source>` C2。

運行並保存巨集

1. 將插入點放在CopyFormulasToAnotherSheetVisual Basic 編輯器內的任一位置，然後按F5鍵。您也可以返回 Excel，選擇“開發工具”>“巨集”，選擇該過程，然後選擇“運行”。
2. 檢查目標單元格Summary。如果來源公式類似於 ` =A2*B2\begin {formula Source!C2}`，則複製的公式模式Summary!C2也相同=A2*B2。如果公式需要繼續讀取來源工作表中的值，請在來源公式中限定這些引用，例如 `\begin{formula}` =Source!A2*Source!B2。
3. 將工作簿另存為啟用巨集的 Excel 工作簿（.xlsm），以便保留 VBA 程式。如果 Excel 詢問巨集安全性，請僅執行您信任的巨集。

選擇合適的公式計算方法

FormulaR1C1複製公式結構。公式儲存格左側兩列的參考在目標儲存格中仍保持左側兩列的位置；絕對引用則保持不變。當您希望在相應的目標區域中使用相同的計算模式時，此功能非常有用。它不會複製格式。如果來源區域包含常數或空白儲存格，它們也會作為值或空白儲存格傳輸，因此請使用僅包含您要複製的公式的區域。

若要保持 Excel 正常的複製行為（包括公式和格式），請使用Range.Copy指定目標位置：

wb.Worksheets("Source").Range("C2:C10").Copy _
    Destination:=wb.Worksheets("Summary").Range("C2")

這會複製儲存格的全部內容和格式；Excel 會像標準複製一樣調整相對引用。當目標位置需要接收的不僅僅是公式時，請使用此方法。 Microsoft 文件中同時介紹了FormulaR1C1 屬性和Range.Copy 方法。

解決常見問題

• 「下標超出範圍」：請檢查工作表名稱是否與標籤頁上顯示的名稱完全一致，包括空格。
• 公式指向錯誤的儲存格： FormulaR1C1它使用了指向目標工作表的相對參考。如果公式必須繼續指向來源工作表，請在公式中新增工作表名稱。
• 程式碼使用了錯誤的工作簿： ThisWorkbook它指向包含巨集的檔案。如果代碼位於個人巨集工作簿中，請wb改為使用名稱指向已開啟的工作簿。
• 巨集未列出：請確認文件已另存.xlsm為受信任的文件，並已啟用巨集。 Microsoft 的巨集說明說明如何啟用「開發人員」標籤並執行程式。

當來源名稱、目標名稱、範圍和引用行為經過精心設定後，巨集可以複製單一公式或整個區塊，而無需選擇工作表或依賴剪貼簿。