【VBA】シートを別ブックにコピーして配布用ブックを作る

社内向けのデータ作成マクロで、結果をいったん一時シートに出し、目視で確認してから、シートコピーで別ブックにして、メールで社内に配る運用をしていました。配る側としては、数式やクエリを含まない、結果だけのブックにしたい場面でした。

この記事では、その流れをVBAで組む手順と、新しいブックに何が残るのかを実際に確かめた結果をまとめます。結論から言うと、シートをコピーすれば必ずクリーンになる、とは限りませんでした。

こんな方向けです
一時シートをコピーして別ブックにする流れ
解決コード
解説
よくある失敗
応用例
試していないこと
まとめ

こんな方向けです

VBAで集計やデータ作成のマクロを組んでいる
結果だけを別ブックにして、メールなどで配りたい
元ブックの数式やクエリを、配布先に見せたくない

検証環境は、Excel 16.0（Windows、日本語環境）です。他のバージョンとMac版は未検証です。

一時シートをコピーして別ブックにする流れ

出力専用の一時シートを作る
配布する内容を書き、数式は値にする
目視で確認する
新しいブックにコピーして保存し、一時シートを削除する

解決コード

Option Explicit

Private Const OUT_NAME As String = "Report"

Public Sub ExportDistBook(ByVal wbSrc As Workbook, ByVal savePath As String, _
                          Optional ByVal askConfirm As Boolean = True)
    Dim wsTmp As Worksheet
    Dim wbOut As Workbook
    Dim prevAlerts As Boolean
    Dim errNo As Long
    Dim eSrc As String
    Dim eDesc As String

    prevAlerts = Application.DisplayAlerts
    On Error GoTo Cleanup

    ' 1. 出力専用の一時シートを作り、配布する内容を書く
    Set wsTmp = wbSrc.Worksheets.Add(After:=wbSrc.Worksheets(wbSrc.Worksheets.Count))
    wsTmp.Name = OUT_NAME
    FillTmp wsTmp

    ' 2. 数式を値にする
    With wsTmp.UsedRange
        .Value = .Value
    End With

    ' 3. 目視確認（OKでなければ何も出力せず片付けへ）
    If askConfirm Then
        wbSrc.Activate
        wsTmp.Activate
        If MsgBox("Output OK? Export to a new workbook.", vbYesNo) = vbNo Then GoTo Cleanup
    End If

    ' 4. シートを新しいブックにコピーして保存
    wsTmp.Copy
    Set wbOut = ActiveWorkbook
    Application.DisplayAlerts = False
    wbOut.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook

Cleanup:
    errNo = Err.Number
    eSrc = Err.Source
    eDesc = Err.Description
    On Error Resume Next
    Application.DisplayAlerts = False
    If Not wbOut Is Nothing Then wbOut.Close SaveChanges:=False
    If Not wsTmp Is Nothing Then wsTmp.Delete
    Application.DisplayAlerts = prevAlerts
    On Error GoTo 0
    If errNo <> 0 Then Err.Raise errNo, eSrc, eDesc
End Sub

Private Sub FillTmp(ByVal ws As Worksheet)
    ws.Range("A1").Value = "item"
    ws.Range("B1").Value = "amount"
    ws.Range("A2").Value = "A"
    ws.Range("B2").Formula = "=Data!A1*2"
    ws.Range("A3").Value = "total"
    ws.Range("B3").Formula = "=SUM(Data!A1:A3)"
End Sub

FillTmp は例です。Data シートのA1〜A3を参照する数式を書いています。呼び出しは、保存先を ThisWorkbook.Path から組みます。

ExportDistBook ThisWorkbook, ThisWorkbook.Path & "\dist.xlsx"

解説

Copyを引数なしで呼ぶと新しいブックになる

Worksheet.Copy は、Before も After も指定しないと、コピーしたシートだけを持つ新しいブックを作ります。新しいブックは ActiveWorkbook になるので、直後に Set wbOut = ActiveWorkbook で受けます。

実測でも、新しいブックのシートは1枚だけで、元ブックのほかのシートは入りませんでした。列幅と表示形式も引き継がれました。

数式のままだと、外部リンクが残る

他のシートを参照する数式のままコピーすると、新しいブックに数式が残り、元ブックへの外部リンクになりました。数式の文字列も元ブックを指しています。配布先には、元のシート構成が見えてしまいます。

コピーの前に .Value = .Value で値にすると、数式も外部リンクも残りませんでした。コピーしたあとに新しいブックで値にしても、同じ結果でした。

名前が残ることがある

数式が使っている名前は、新しいブックにコピーされ、元ブックを指したまま残りました。数式で使っていない名前は、コピーされませんでした。

数式を値にしても、この名前は消えません。コピーの前に値にしても、コピーのあとに値にしても残りました。コピーの前に、再計算する、元ブックを保存する、値を退避して書き戻す、といった方法に変えても同じでした。

さらに、名前を使った数式を消した場合や、数字で上書きした場合でも、名前はコピーされました。一方、名前を使う数式を入れていないブックからは、名前はコピーされませんでした。元ブックを保存して閉じ、開き直してからコピーした場合も、名前はついてきませんでした。

名前が残っているかどうかは、LinkSources では判定できません。名前が残った状態でも、0件のときと1件のときがありました。名前は Names で直接確認してください。

シート単位の名前は、コピーしたシートについてきて、そのシート自身を指しました。

クエリの出力シートは、そのままコピーしない

クエリの出力テーブルがあるシートをコピーすると、クエリと接続が新しいブックに残りました。名前も1件ついてきました。この名前は、非表示のシート単位の名前 ExternalData_1 で、コピー先のシート自身を指していました。クエリのコードは元と同じでした。

一方、元ブックにクエリがあっても、コピーする一時シートが値だけなら、クエリも接続も名前も残りませんでした。接続専用のクエリでも同じです。

最初から値だけを書いた一時シートなら、何も残らないということです。数式を書いてから値にする場合は、名前を使った数式があると、名前が残ります。

残ったものを消す

ここまでの結果から、名前は、名前を使う数式を入れたあと、開き直していない元ブックからコピーすると残る、というのが実測の範囲です。元ブックを開き直せば残りませんが、マクロの途中でそれを挟むのは現実的ではありません。そのため、この記事では「残ったら、コピーの直後に消す」対処にしています。名前は、元ブックの外を指すものか、参照が壊れたものだけを消します。シート単位の名前を巻き込まないためです。

配布ファイルとして保存し、開き直して確かめた結果です。名前が残ったまま保存したファイルには、名前と外部リンクが1件残っていました。消してから保存したファイルでは、名前も外部リンクも0件でした。

Public Sub DeleteBookNames(ByVal wb As Workbook)
    Dim i As Long
    Dim r As String
    For i = wb.Names.Count To 1 Step -1
        r = wb.Names(i).RefersTo
        If InStr(r, "[") > 0 Or InStr(r, "#REF!") > 0 Then wb.Names(i).Delete
    Next i
End Sub

Public Sub DeleteBookQueries(ByVal wb As Workbook)
    Dim ws As Worksheet
    Dim i As Long
    For Each ws In wb.Worksheets
        For i = ws.ListObjects.Count To 1 Step -1
            ws.ListObjects(i).Unlist
        Next i
    Next ws
    For i = wb.Connections.Count To 1 Step -1
        wb.Connections(i).Delete
    Next i
    For i = wb.Queries.Count To 1 Step -1
        wb.Queries(i).Delete
    Next i
End Sub

名前を消す部品は、先に値にしてからコピーした場合も、コピーしてから値にした場合も、名前が0になり、値は残り、外部リンクも0でした。シート単位の名前は消えずに残りました。

クエリを消す部品は、シート上のテーブルをすべて通常のセル範囲に戻し、接続、クエリの順に消します。クエリも接続もテーブルも、ついてきた名前も0になり、値は残りました。クエリと無関係なテーブルも範囲に戻るので、そのようなテーブルがあるシートでは使わないでください。

どちらも、新しいブックを ActiveWorkbook で受けた直後に呼びます。ExportDistBook に組み込んだ形では通して試しておらず、コピー直後の新しいブックに対して単独で確認しました。

名前やクエリ、接続の見方は、【Excel】引き継ぎブックで先に見る外部リンク・名前・接続にまとめています。

結果を並べて確認する

ここまでの結果を、再現用の最小コードで並べました。新しいブックに何が残ったかを、数式・リンク・名前・クエリ・接続の数で表示しています。Aは数式のまま、Bは先に値にした場合、Cは名前を使った数式を値にした場合、Dはクエリの出力シート、Eは値だけのシートです。「+」の行は、消す部品を呼んだあとです。

新しいブックに残った数式・リンク・名前・クエリ・接続の数を、7つの場合で並べたイミディエイトウィンドウの出力 — 再現用の最小コードの実行結果

DisplayAlertsとエラー時の後片付け

保存と削除の前に Application.DisplayAlerts を False にして、最後に元の値へ戻します。True のままで一時シートを Delete すると、「このシートは完全に削除されます。続けますか？」という確認が出ます。False にして実行した検証では、確認で止まりませんでした。