大体でIT

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

大体でIT

Excel VBAでシートを別ブックに保存する方法をご紹介します。「Copy」を使って新規ブックにコピーして、「Saveas」で名前を付けて保存するとできます。シートの別ブック保存を自動化して、業務を効率化していきましょう。

はじめに

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

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

シートの別ブック保存を自動化して、業務を効率化していきましょう。

では、シートを別ブックで保存する方法について、解説していきます。

この記事を読むメリット

  • シートを別ブックで保存する方法がわかります

本記事の内容を動画でまとめています

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

目次

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

やりたい内容は、シートを別ブックで保存したい、ということになります。

やりたいこと

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

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

元シート

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

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

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

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

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

元フォルダ

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

やりたいことは、次のように、マクロファイル内のシートを別ブックで保存したい、ということになります。

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

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

では、実際にVBAを使ってやってみます。

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

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

シートを別ブックで保存する

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

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

End Sub

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

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

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

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

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

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

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

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

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

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

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

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

手順③:新規ブックを閉じる

最後に新規ブックを閉じたいので、「〇〇.Close」を使います。

閉じる際のメッセージを非表示にしたいので、引数に「False」を入力しています。

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

別ブックで保存できた

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

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

ファイルを開いてみます。

「1つのシートのみ」を別ブックで保存できた

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

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

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

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

「複数シート」を、別ブックで保存する場合は、こんな感じになります。

'複数シートを別ブック保存(.xlsx形式)
Sub TEST2()
    
    '複数シートを新しいブックへコピーする
    ThisWorkbook.Worksheets(Array("TEST1", "TEST2")).Copy
    
    '新しく作成したブックを名前を付けて保存
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\TEST1.xlsx"
    
    '新しく作成したブックを閉じる
    ActiveWorkbook.Close False
    
End Sub

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

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

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

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

シート「TEST1」と「TEST2」を別ブックで保存したいので、配列を使って「Array("TEST1", "TEST2")」としています。

これで、シート「TEST1」と「TEST2」を新規ブックにコピーすることができます。

手順②~③

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

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

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

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

別ブックで保存できた

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

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

ファイルを開いてみます。

「複数シート」を別ブックで保存できた

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

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

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

すべてのシートを別ブックで保存する

全てのシートを、別ブックで保存したい場合は、このようになります。

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

End Sub

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

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

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

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

別ブックで保存できた

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

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

ファイルを開いてみます。

「全てシート」を別ブックで保存できた

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

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

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

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

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

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

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

日付をつけて保存する

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

'シートを別ブックで保存(日付をつける)
Sub TEST4()
        
    '「TEST1」シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '保存するブックの名前を作成
    Dim A, B, C, D
    A = "TEST" 'ブックの名前
    B = Format(Now(), "yyyymmdd-hhmmss") '日付
    C = A & "_" & B & ".xlsx" '拡張子を含むブックの名前
    D = ThisWorkbook.Path & "\" & C 'ブックのフルパス
    
    '新しく作成したブックを名前を付けて保存(日付付き)
    ActiveWorkbook.SaveAs Filename:=D
    
    '新しく作成したブックを閉じる
    ActiveWorkbook.Close False

End Sub

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

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

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

「日付を付けて」別ブックで保存できた

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

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

ファイルを開いてみます。

シートを別ブックで保存できた

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

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

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

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

保存するシート以外から値を引用している場合、数式が入ったままだと値がおかしくなったりするので、そんな場合は、「値だけを別ブックで保存する」という方法が便利です。

数式を値にして別ブックで保存する

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

'シートを別ブックで保存する(数式を値にする)
Sub TEST5()
        
    '「TEST1」シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '数式を値に変更する
    With ActiveWorkbook.Worksheets("TEST1").Range("A1").CurrentRegion
        .Value = .Value
    End With
    
    '新しく作成したブックを名前を付けて保存
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\TEST1.xlsx"
    
    '新しく作成したブックを閉じる
    ActiveWorkbook.Close False

End Sub

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

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

「.Value = .Value」の部分です。

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

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

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

別ブックで保存できた

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

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

ファイルを開いてみます。

数式を値に変換できた

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

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

VBAで拡張子を変更してシートを別ブックで保存

別ブックで保存する際に、「拡張子」を変更することもできます。

次の拡張子で保存してみます。

  • 「.csv」ファイル
  • 「.txt」ファイル
  • 「.xlsm」ファイル

では、やってみます。

「.csv」にして別ブックで保存

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

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

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

'シートを別ブックで保存する(.csv形式)
Sub TEST6()
    
    '「TEST1」シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '新しく作成したブックを名前を付けて保存(.csv形式)
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\TEST1.csv", FileFormat:=xlCSV
    
    '新しく作成したブックを閉じる
    ActiveWorkbook.Close False

End Sub

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

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

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

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

シートを「CSV形式」で保存できた

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

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

ファイルを開いてみます。

「CSV形式」で保存できた

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

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

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

「.txt」にして別ブックで保存

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

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

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

'シートを別ブックで保存する(.txt形式)
Sub TEST7()
    
    '「TEST1」シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '新しく作成したブックを名前を付けて保存(.txt形式)
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\TEST1.txt", FileFormat:=xlText
    
    '新しく作成したブックを閉じる
    ActiveWorkbook.Close False

End Sub

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

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

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

シートを「テキスト形式」で保存できた

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

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

ファイルをメモ帳で開いてみます。

「テキスト形式」で保存できた

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

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

「.xlsm」にして別ブックで保存

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

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

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

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

'シートを別ブックで保存する(.xlsm形式)
Sub TEST8()
    
    '「TEST1」シートを新しいブックへコピーする
    ThisWorkbook.Worksheets("TEST1").Copy
    
    '新しく作成したブックを名前を付けて保存(.xlsm形式)
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\TEST1.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    '新しく作成したブックを閉じる
    ActiveWorkbook.Close False

End Sub

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

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

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

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

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

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

ファイルを開いてみます。

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

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

「.xlsm」形式なので、数式は入力されたままです。

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

おわりに

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

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

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

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

シートを別ブックで保存する方法を、実務で使いこなしていきましょう。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す