－ちょっと使えるネタを紹介－
Excel VBAでシートを別ブックに保存する方法をご紹介します。「Copy」を使って新規ブックにコピーして、「Saveas」で名前を付けて保存するとできます。シートの別ブック保存を自動化して、業務を効率化していきましょう。
訪問ありがとうございます。こちらの記事ではエクセルのシートを、別ブックで保存する方法についてご紹介します。
別ブック保存の際には、数式を値に変換したり、拡張子を変更することができます。
シートの別ブック保存を自動化して、業務を効率化していきましょう。
では、シートを別ブックで保存する方法について、解説していきます。
この記事を読むメリット
目次から見たい項目へ移動すると便利ですよ。
目次
やりたい内容は、シートを別ブックで保存したい、ということになります。
別ブックで保存するシートはこちらです。
「TEST.xlsm」というファイルに「TEST1」と「TEST2」、「TEST3」というシートを作成しました。
表の中の「売上」のところに数式が入っています。
シート「TEST1」～「TEST3」に入っているデータは、すべて同じです。
保存されているフォルダはこちらになります。
「TEST.xlsm」というマクロファイルが保存されています。
やりたいことは、次のように、マクロファイル内のシートを別ブックで保存したい、ということになります。
では、実際に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」というファイルが新しく作成されています。
ファイルを開いてみます。
「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」の全てシートが別ブックで保存されています。
元のファイルに入力されていた数式も、そのままコピーされます。
シートを別ブックで保存する際は、ファイル名に日付をつけることをおすすめします。
ファイル名がすでにあるファイル名と被ってしまうと、エラーとなってしまうからです。
日付と時間をファイル名に追加すれば、ファイル名が被ることもありませんし、いつ保存したかもわかるので便利です。
ファイル名に日付を付けて、シートを別ブックで保存する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のような日付と現在時刻を、作成しています。
作成した日付をファイル名につけて、名前を付けて保存しています。
ファイル名に日付をつけて保存した結果がこちらです。
日付がついたエクセルファイルが、作成されていることがわかります。
ファイルを開いてみます。
ファイル名に、日付と現在時刻が追加されていることがわかります。
シートを別ブックで保存する際に、数式を値にして、保存する方法を紹介します。
保存するシート以外から値を引用している場合、数式が入ったままだと値がおかしくなったりするので、そんな場合は、「値だけを別ブックで保存する」という方法が便利です。
数式を値にして、別ブックで保存する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」というファイルが、できていることがわかります。
ファイルを開いてみます。
売上の列の数式が、値になっていることがわかります。
別ブックで保存する際に、「拡張子」を変更することもできます。
次の拡張子で保存してみます。
では、やってみます。
エクセルのシートを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形式で、シートを別ブックで保存した結果です。
「TEST1.csv」のファイルが、作成されていることがわかります。
ファイルを開いてみます。
CSV形式で保存していますので、すべて値になっています。
シート名もファイル名と同じになっていて、罫線もなくなっています。
テキスト形式で、シートを別ブックで保存する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」というテキストファイルが、作成されています。
ファイルをメモ帳で開いてみます。
シート内のデータが、テキスト形式で保存されていることがわかります。
マクロ形式のエクセルファイルにして、シートを別ブックで保存する方法です。
マクロ形式に変換はできます。ただし、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が出来上がります。
シートを別ブックで保存する方法を、実務で使いこなしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する