大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAでシートを別ブックに保存する方法をご紹介します。手順はブックを新規作成してシートをコピー、最後にSaveAsで名前を付けて保存でできます。

はじめに

訪問ありがとうございます。こちらの記事ではエクセルのシートを、別ブックで保存する方法についてご紹介します。

別ブック保存の際には、数式を値に変換したり、拡張子を変更することができます。

この記事で紹介すること

  • シートを別ブックに保存にする方法

目次から見たい項目へ移動すると便利ですよ。

目次

別ブックで保存したいシート

別ブックで保存するシートはこちらです。

別ブックで保存したいシート

元シート

『TEST.xlsm』というファイルに『TEST1』と『TEST2』、『TEST3』というシートを作成しました。

表の中の『売上』のところに数式が入っています。

シート『TEST1』~『TEST3』に入っているデータは、すべて同じです。

保存されているフォルダはこちらになります。

ブックが保存されているフォルダ

元フォルダ

『TEST.xlsm』というマクロファイルが保存されています。

VBAでシートを別ブックで保存

Excel VBAでシートを別ブックで保存する方法を説明します。

Excel VBAコード

シートを別ブックで保存するExcel VBAのコードです。

'シートを別ブック保存(.xlsx形式)
Sub TEST1()
        
    '『TEST1』シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '保存するブックの名前を作成
    a = ThisWorkbook.Path & "\TEST1.xlsx"
    
    '新しく作成したブックを名前を付けて保存
    ActiveWorkbook.SaveAs Filename:=a

End Sub

Excel VBAの手順は次のようになります。

  • ①新規のブックにシートをコピー
  • ②保存する名前を作成
  • ③新規のブックを名前を付けて保存

手順①:新規のブックにシートをコピー

新規のブックにシートをコピーするには、『〇〇.Copy』とすればできます。簡単です。

手順②:保存する名前を作成

保存するファイルの名前は、フルパスで指定します。

フルパスは、『C:\User\TEST\TEST1.xlsx』みたいな感じです。

名前は好きな名前を付けられます。

現在のフォルダから、フルパスを記載すると便利なので、『a = ThisWorkbook.Path & "\TEST1.xlsx"』としています。

これで、現在のフォルダと同じフォルダに、ファイルが保存されます。

手順③:新規のブックを名前を付けて保存

新規のブック『ActiveWorkbook』を名前を付けて保存します。

名前を付けて保存は、『〇〇.SaveAs』です。

引数『Filename:=〇〇』にフルパスのファイル名を記載します。

これでシートを別ブックで保存することができます。

結果

シートを、別ブックで保存すると次のようなファイルができます。

別ブックで保存後のファイル

シートを別ブックで保存した後のファイル

『TEST』というシートだけが、別ブックで保存されています。

もちろん、元のファイルに入力されていた数式も、そのままコピーされています。

シートを別ブックに保存したあとのフォルダは、このようになります。

別ブックで保存後のフォルダ

シートを別ブックで保存した後のフォルダ

『TEST1.xlsx』というファイルが新しく作成されています。

VBAで複数シートを別ブックで保存

複数シートを、別ブックで保存する方法を紹介します。

Excel VBAコード

複数シートを、別ブックで保存するVBAコードはこちらです。

'複数シートを別ブック保存(.xlsx形式)
Sub TEST2()
        
    'コピーするシートの配列を作成
    ReDim a(1 To 2) As Variant
    a(1) = "TEST1"
    a(2) = "TEST2"
        
    '複数シートを新しいブックへコピーする
    ThisWorkbook.Worksheets(a).Copy
    
    '保存するブックの名前を作成
    b = ThisWorkbook.Path & "\TEST1.xlsx"
    
    '新しく作成したブックを名前を付けて保存
    ActiveWorkbook.SaveAs Filename:=b

End Sub

手順は次のようになります。

  • ①別ブックで保存するシートの配列を作成
  • ②新規ブックにシートをコピー
  • ③保存する名前を作成
  • ④新規のブックを名前を付けて保存

という感じです。手順①だけが先ほどと違います。

手順①別ブックで保存するシートの配列を作成

別ブックで保存したいシートの配列を作成します。

シート『TEST1』と『TEST2』を別ブックで保存したいので、それぞれを配列に保存します。

手順②~④

あとは、先ほどと同じです。

複数シートを新規ブックにコピーして、新規ブックを名前を付けて保存します。

これで、任意の複数シートを別ブックで保存することができます。

結果

複数のシートを、別ブックで保存すると、次のようなファイルができます。

複数シートを別ブックで保存

複数シートを別ブックで保存した後のファイル

『TEST1』と『TEST2』のシートだけが別ブックで保存されています。

元のファイルに入力されていた数式も、そのままコピーされます。

シートを別ブックに保存したあとのフォルダは、このようになります。

別ブックで保存後のフォルダ

複数シートを別ブックで保存した後のフォルダ

『TEST1.xlsx』というファイルが新しく作成されています。

VBAで全てのシートを別ブックで保存

全てのシートを、別ブックで保存する方法をご紹介します。

Excel VBAコード

全てのシートを、別ブックで保存するVBAコードはこちらです。

'全てのシートを別ブック保存(.xlsx形式)
Sub TEST3()
        
    'すべてのシートを新しいブックへコピーする
    ThisWorkbook.Worksheets().Copy
    
    '保存するブックの名前を作成
    a = ThisWorkbook.Path & "\TEST1.xlsx"
    
    '新しく作成したブックを名前を付けて保存
    ActiveWorkbook.SaveAs Filename:=a

End Sub

全てのシートを新規ブックにコピーするには、『ThisWorkbook.Worksheets().Copy』とします。

このVBAコードで、『Worksheets()』がポイントですね。

これで、全てのシートを別ブックで保存することができます。

結果

全てのシートを、別ブックで保存すると次のようなファイルができます。

全てシートを別ブックで保存

全てのシートを別ブックで保存した後のファイル

『TEST1』と『TEST2』、『TEST3』の全てシートが別ブックで保存されています。

元のファイルに入力されていた数式も、そのままコピーされます。

シートを別ブックに保存したあとのフォルダは、このようになります。

別ブックで保存後のフォルダ

全てのシートを別ブックで保存した後のフォルダ

『TEST1.xlsx』というファイルが新しく作成されています。

VBAで日付をつけてシートを別ブックで保存

シートを別ブックで保存する際は、ファイル名に日付をつけることをおすすめします。

ファイル名がすでにあるファイル名と被ってしまうと、エラーとなってしまうからです。

日付と時間をファイル名に追加すれば、ファイル名が被ることもありませんし、いつ保存したかもわかるので便利です。

Excel VBAコード

ファイル名に日付を付けて、シートを別ブックで保存するExcel VBAコードです。

'シートを別ブックで保存(日付をつける)
Sub TEST4()
        
    '『TEST1』シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '保存するブックの名前を作成
    b = "TEST" 'ブックの名前
    c = Format(Now(), "yyyymmdd-hhmmss") '日付
    d = b & "_" & c & ".xlsx" '拡張子を含むブックの名前
    e = ThisWorkbook.Path & "\" & d 'ブックのフルパス
    
    '新しく作成したブックを名前を付けて保存(日付付き)
    ActiveWorkbook.SaveAs Filename:=e
    
End Sub

『Format(Now(), "yyyymmdd-hhmmss")』で20200821-121212のような日付と現在時刻を、作成しています。

作成した日付をファイル名につけて、名前を付けて保存しています。

結果

ファイル名に日付をつけて保存した結果がこちらです。

別ブックで保存後のファイル(日付)

ファイル名に日付をつけてシートを別ブックで保存

ファイル名に、日付と現在時刻が追加されていることがわかります。

別ブックで保存した後のフォルダは、こちらになります。

別ブックで保存後のフォルダ(日付)

ファイル名に日付をつけてシートを別ブックで保存した後のフォルダ

日付がついたエクセルファイルが、作成されていることがわかります。

VBAで数式を値にしてシートを別ブックで保存

シートを別ブックで保存する際に、数式を値にして、保存する方法を紹介します。

保存するシート以外から値を引用している場合、数式が入ったままだと値がおかしくなったりするので、値だけを別ブックで保存したい。といった場合があるかと思います。

Excel VBAコード

数式を値にして、別ブックで保存するExcel VBAコードはこちらです。

'シートを別ブックで保存する(数式を値にする)
Sub TEST5()
        
    '『TEST1』シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '数式を値に変更する
    With ActiveWorkbook.Worksheets("TEST1")
        .Range(.Cells(1, "A"), .Cells(5, "D")).Value = .Range(.Cells(1, "A"), .Cells(5, "D")).Value
    End With
    
    '保存するブックの名前を作成
    b = ThisWorkbook.Path & "\TEST1.xlsx"
    
    '新しく作成したブックを名前を付けて保存
    ActiveWorkbook.SaveAs Filename:=b

End Sub

ポイントは、数式が入った範囲に値を貼り付けるということです。

『範囲.Value = 範囲.Value』というコードで、同じ範囲を指定して値を貼り付けます。

『.Range(.Cells(1, "A"), .Cells(5, "D")).Value = .Range(.Cells(1, "A"), .Cells(5, "D")).Value』の部分です。

これで数式を値にすることができます。

この操作は新しく作成したシート上で実行します。元のファイルで実行してしまうと、数式が消えてしまうので注意です。

結果

数式を値にして、シートを別ブックで保存した結果です。

別ブックで保存後のファイル(数式を値に変換)

数式を値にしてシートを別ブックで保存した結果

売上の列の数式が、値になっていることがわかります。

別ブックでシートを保存した後のフォルダは、こちらになっています。

別ブックで保存後のフォルダ(数式を値に変換)

数式を値にしてシートを別ブックで保存した後のフォルダ

『TEST1.xlsx』というファイルが、できていることがわかります。

VBAで.csvにしてシートを別ブックで保存

エクセルのシートをCSV形式にして、別ブックで保存するExcel VBAを紹介します。

データ解析用で、エクセルのシートをCSV形式で保存したい。というときに参考になるかと思います。

Excel VBAコード

CSV形式にして、シートを別ブックで保存するExcel VBAコードです。

ポイントは名前をつけて保存する際に、FileFormatの引数にCSV形式を指定することです。

SaveAsの引数で、『FileFormat:=xlCSV』というように指定します。

これで.csvでシートを保存することができます。

結果

CSV形式で、シートを別ブックで保存した結果です。

別ブックで保存後のファイル(CSV形式)

CSV形式でシートを別ブックで保存した結果

CSV形式で保存していますので、すべて値になっています。

シート名もファイル名と同じになっています。罫線もなくなっています。

シートを別ブックで保存後のフォルダをみてみます。

別ブックで保存後のフォルダ(CSV形式)

CSV形式でシートを別ブックで保存した後のフォルダ

『TEST1.csv』のファイルが、作成されていることがわかります。

VBAで.txtにしてシートを別ブックで保存

テキスト形式で、シートを別ブックで保存するExcel VBAを説明します。

こちらも、データ解析用でテキストデータ形式で保存したい。といった場合に参考になるかと思います。

Excel VBAコード

テキスト形式で、シートを別ブックで保存するExcel VBAコードです。

ポイントは、名前を付けて保存する際に、FileFormatの引数にテキスト形式を指定することです。

SaveAsの引数で、『FileFormat:=xlText』と指定すると、テキスト形式で保存することができます。

結果

テキスト形式で、シートを別ブック保存した結果です。

別ブックで保存後のファイル(テキスト形式)

テキスト形式にしてシートを別ブックで保存した結果

メモ帳で開いてみました。

シート内のデータが、テキスト形式で保存されていることがわかります。

テキスト形式で、シートを別ブックで保存した後のフォルダは、こちらです。

別ブックで保存後のフォルダ(テキスト形式)

テキスト形式にしてシートを別ブックで保存した後のフォルダ

『TEST1.txt』というテキストファイルが、作成されています。

VBAで.xlsmにしてシートを別ブックで保存

マクロ形式のエクセルファイルにして、シートを別ブックで保存する方法を紹介します。

マクロ形式に変換はできます。ただし、VBAコードはコピーはされません。

あまり使う方法が考えられませんので、おまけという感じです。

Excel VBAコード

マクロ形式にして、シートを別ブックで保存するExcel VBAコードです。

ポイントは、先ほどと同じで、名前を付けて保存する際に、FileFormatでマクロ形式を指定することです。

SaveAsの引数で、『FileFormat:=xlOpenXMLWorkbookMacroEnabled』とすれば、マクロ形式で保存することができます。

結果

マクロ形式で、シートを別ブックで保存した結果です。

別ブックで保存後のファイル(マクロ形式)

マクロ形式でシートを別ブックで保存した結果

.xlsmのファイルが作成できています。数式が入力されたままです。

もちろんVBAコードはコピーされません。

シートを別ブック保存した後のフォルダは、こちらです。

別ブックで保存後のフォルダ(マクロ形式)

マクロ形式でシートを別ブックで保存した後のフォルダ

『TEST1.xlsm』のマクロ形式のファイルが作成されています。

おわりに

この記事では、シートを別ブックで保存する方法をご紹介しました。

シートを別ブックで保存する際には、ファイル名に日付をつけると、すでにあるファイル名と名前が被らないのでおすすめです。

別ブックで保存する際には、数式を値にしたり、拡張子を変更したりすることもできます。

目的や用途に合わせて、これらの方法を組み合わせると、作りたいExcel VBAが出来上がるかと思います。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す