別々に保存のシートを一つのブックにまとめる。シート移転マクロ

vbatougousheeteyecatch

エクセルVBAを使って、バラバラに保存されたエクセルシートを移動・移転させて一つのBOOKにまとめます。
マクロを個人用マクロブックに記述すれば、いろんなエクセルBOOKで使用できます。

こんにちは、じゅんぱ店長（@junpa33）です。

日々業務で作るいろいろな資料。生み出されるエクセルファイルは大変な量になっていると思います。

そしてそのエクセルの中のシートの枚数も大変な数になっているでしょう。

例えば、テンプレートファイルをコピペして毎日作る業務日報、1日1ファイルで1ヶ月30個のエクセルファイルなんてこともあるのでは。

そんな状態でも、1日1シートとして、１ヶ月分30シートを1つのエクセルファイルで収めることが出来るかもしれません。

今回は、このバラバラになったエクセルファイルのシートを、一つのエクセルに移転させるVBA　ー「シート移転マクロ」ー　についての説明です。

シート移転マクロの作成手順

作成の手順としては以下のように行います。

  1. シート移転マクロを作成する
  2. 作成したマクロを個人用マクロブックに転記する
  3. 起動テストをして完了

作成のポイントとして

  • このシート移転マクロを特定のエクセルBOOKに記述した場合、マクロを使用するためには、そのエクセルBOOKが立ち上げっていることが必要になります。
  • 個人用マクロブックに記述すればいろんなエクセルBOOKで使用することが出来ます。
  • 今回作成するシート移転マクロは、” 操作の視点 ” として、移転したいシートが存在しているエクセルBOOKから見た場合のコード記述となっています。
  • 複数のエクセルBOOKを扱うので、作業したいBOOKやシートのフォーカス状態の確認と、どちらのエクセルBOOKからマクロを実行するのかを間違えないようにする。
目次へ戻る

シート移転マクロを作成する

シート移転マクロを作成します。

完成したVBAコードは以下になります。

先ほどの作成のポイントでも書きましたが、

このマクロは、

移転させたいシートがあるエクセルBOOKで、移転したいシートがアクティブになっている状態で、マクロを実行することが前提です。

VBA
Sub シート移転マクロ()
    Dim NBnamePass, NBname As Variant
    Dim ActSh As Worksheet
        If ActiveWorkbook.Worksheets.Count = 1 Then
            MsgBox "シート枚数が1枚だけのブックはシート移転出来ません。" _
                & vbCrLf & "新規シートを追加してください。", vbExclamation
            Exit Sub
        End If
        
        Set ActSh = ActiveSheet
        ChDir ActiveWorkbook.Path
        NBnamePass = Application.GetOpenFilename(FileFilter:="Excelファイル,*.xls*", _
                Title:="移転先ファイル選択")
        If NBnamePass = "False" Then
            Exit Sub
        Else
            On Error Resume Next
            Workbooks.Open Filename:=NBnamePass
            On Error GoTo 0
        End If
        NBname = Dir(NBnamePass)
        ActSh.Move After:=Workbooks(NBname).Worksheets(Worksheets.Count)
        Workbooks(NBname).Close True
End Sub
コード作成のポイント
Dim NBnamePass, NBname As Variant
Dim ActSh As Worksheet

このマクロで使う変数を宣言します。

  • NBnamePass　は、ダイアログで移転先のエクセルBOOKのフルパス（存在する場所）を取得する変数です。
  • NBname　は、移転先のエクセルBOOKの名前を取得する変数です。
  • ActSh　は、移転させたいシートをオブジェクト変数に入れておきます。
　　If ActiveWorkbook.Worksheets.Count = 1 Then
        MsgBox "シート枚数が1枚だけのブックはシート移転出来ません。" _
            & vbCrLf & "新規シートを追加してください。", vbExclamation
        Exit Sub
    End If

エクセルBOOKに存在するシート枚数が1枚しかない時は「Moveメソッド」が利用できないので、最初にチェックを行います。

        Set ActSh = ActiveSheet
        ChDir ActiveWorkbook.Path
        NBnamePass = Application.GetOpenFilename(FileFilter:="Excelファイル,*.xls*", _
                Title:="移転先ファイル選択")
        If NBnamePass = "False" Then
            Exit Sub

ファイルダイアログを開きますが、初期表示のフォルダを、ChDirでカレントフォルダ指定します。今回は、同一カテゴリフォルダ内での移動と見なして、移転させたいシートのBOOKと同じディレクトリにしています。

開いたダイアログで選択できるファイルの種類は、エクセルファイルに限定しています。

「キャンセル」ボタンをクリックしたときは、このマクロは終了します。

        Else
            Application.DisplayAlerts = False
            On Error Resume Next
            Workbooks.Open Filename:=NBnamePass
            On Error GoTo 0
            Application.DisplayAlerts = True
        End If
  • Application.DisplayAlerts = False　は、エクセルのアラート表示を無効にします。
  • On Error Resume Next　は、発生したエラーを無効にしてコードの進行を停止させません。

この2つはいずれも特別処置的に行うもので、対処するコード行が終わった時点で元の状態に復帰させておくことが必要です。

無効にしたアラート表示はこれです。

アラートの表示を無効にすることで、常に新たにエクセルファイルを開くことになります。すでに開いていて追加でシート操作・セル操作した変更内容については、このシート移転マクロ実行前に上書き保存しておくことが必要です。

また、

「いいえ」をクリックすると、コードの進行上エラーが発生します。

この「いいえ」ボタンをクリック出来ないように表示させないということもあります。

    NBname = Dir(NBnamePass)
    ActSh.Move After:=Workbooks(NBname).Worksheets(Worksheets.Count)
    Workbooks(NBname).Close True

NBnamePass　で、ダイアログで移転先のエクセルBOOKのフルパス（存在する場所）を取得しましたが、Dir関数を使って、NBname　として、移転先のエクセルBOOKの名前を取得します。

そして、Moveメソッドで、エクセルBOOK間でシートの移動を実行します。

最後に移転先のエクセルBOOKを上書き保存して閉じます。

シート移転マクロの裏ワザ効果

このシート移転マクロを利用することで、バラバラになっているエクセルシートを一つのエクセルBOOKにまとめることが出来ます。

移転したいシートは移転先エクセルBOOKに移動し、上書き保存で終了します。

移転元のエクセル（元あったシートのエクセル）は画面上、未保存でまだ残っています。

裏ワザ

移転元のエクセルを閉じる時に、変更を「保存しない」で終了すると、シート移転前のエクセルBOOKが温存されることになります。

結果、移転したいシートを移転先エクセルBOOKに、まるまるコピーしたのと同じ状態になります。

「保存して終了」・・・・＞シート移転

「保存しないで終了」・・＞シートコピー

目次へ戻る

個人用マクロブックにマクロを記述

作成したマクロ（「シート移転マクロ」プロシージャー）を個人用マクロブックに転記します。

この記事にあるマクロコードをそのまま個人用マクロブックに張り付けていただいてＯＫです。

個人用マクロブックをまだ作っていない方は、こちらの方法で先に作成してください。

vbapersonalbkeyecatch 個人用マクロブックの簡単作成と削除方法と使い方ガイド

作成した上でコードをペーストします。

vbacopipeeyecatch Webで見つけたマクロをコピペで使う

ご自分で書き込む方は、標準モジュールでも個人用マクロブックでも、こちらのマクロコードを書き込んでください。

色んなエクセルで利用したい場合は、個人用マクロブックに直接記述の方が良いでしょう。

ご自分で書き込んでいけばエクセルVBAの勉強にもなると思います。

目次へ戻る

起動テストする

  1. 移動させたいシートがあるエクセルを起動し、そのシートを選択してフォーカスが当たった状態（アクティブ）にします。
  2. ショートカットキーの「Alt」＋「F8」でマクロのダイアログを呼び出します。
  3. マクロ名「PERSONAL.XLSB!シート移転マクロ」を選択し実行をクリックします。
  4. 移転先ファイル選択ダイアログで移転先を選択します。
  5. 「開く」をクリックすれば、 ” アクティブになった移転させたいシート ” が移転先に移動し自動保存されます。
  6. 移転先ファイルを起動してシートが移転していることを確認します。
目次へ戻る

まとめ

エクセルファイルを整理整頓する作業は、 ” 面倒くさい ” 作業そのものです。

特にエクセルファイル内のシートまで整理しだしたら ” うんざり業務 ” まさにそのものになります。

けれども、エクセルマクロやVBAを知っている人であれば、そのうんざり度もずいぶん軽減されるでしょう。

是非、こういった事務効率化度合いを高く大きく図れる知識やスキルは身に着けておきましょう。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

＜記事内容についての告知＞VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。
目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

シート名一覧表作成マクロをブック登録して色んなエクセルで使う

次の記事

エクセルブックをパスワードロックするユーティリティなマクロ