EXCEL 小技・テクニック集

エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?

エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?

シート名を一括で編集する方法とは?

シートの数が増えれば増えるほどその管理が大変になりますが、その際にはシートの名前を再検討することもあります。
でも正直たくさんのシート名を編集するのめんどくさい。。
しかも頻繁にこの作業が発生するということであればさらにめんどくさく、サクッと一括変更(置換)したいといった要望がでるのもうなずけます。

そこで今回は、一括で変更(置換)する方法があるのか、紹介したいと思います。

VBA(マクロ)がないと一括の変更はできない!

まず、シート名の変更を一括で行う方法はVBA(マクロ)意外にはありません!
これはちょっとへこみますよね。。
ということなので、VBA(マクロ)を使った一括変更というのを紹介していきます。

処理@全シート名をまずは取得する

シート名を変更もしくは置換するということであれば、全シート名の一覧が必要になります。
VBAマクロでまずは全シート名を取得してみましょう。

まずは何もないブックを開きます。

シート名一括変更置換1

次に「Alt + F11」を押しますと
「Microsoft Visual Basic for Applications」というウィンドウが開きます。これがいわゆる「VBA」と言われているものです。

シート名一括変更置換2

次に、メニューバーの挿入→標準モジュールを選択すると
コードが入力できる画面が作成されます。
ここに処理を書いていきます。

シート名一括変更置換3

シート名一括変更置換4

右の入力画面に下記コードをコピペしましょう。

Sub all_sheet_name()
Dim w_sheet
Dim row_cnt
'all_sheet_nameがすでにある場合、処理を中止する。
For Each w_sheet In Worksheets
If w_sheet.Name = "all_sheet_name" Then
MsgBox "all_sheet_nameを削除してください", vbInformation
Exit Sub
End If
Next w_sheet


'all_sheet_nameを作成
Sheets.Add
ActiveSheet.Name = "all_sheet_name"

'全シート名を取得し、セルに入力する
row_cnt = 2
Cells(1, 1) = シート名
For Each w_sheet In Worksheets
If w_sheet.Name <> "all_sheet_name" Then
Cells(row_cnt, 1) = w_sheet.Name
row_cnt = row_cnt + 1
End If
Next w_sheet

End Sub

シート名一括変更置換5

これで全シート名の取得ができるようになりました。
確認のために、一旦元のエクセルファイルに戻り、作ったマクロを実行してみましょう。
VBAのウィンドウはそのままでよいので、元のエクセルのウィンドウに戻り
メニューバーから
表示→マクロ→マクロの表示
を選択すると、作ったマクロが表示されます。
all_sheet_nameを選択して、実行を押してみましょう。

シート名一括変更置換6

シート名一括変更置換7

シート名一括変更置換8

これでシート名の取得が一瞬でできましたね。
ちょっと手順が長かったような…ここで疲れてきた私ですが、次行きます。。。

Aシート名の一括変更・置換処理

さて、それではまず、それぞれのシート名に対して、変更後の内容をB列に入力していきましょう。 置換の場合は、A列のシート名をB列にコピペし、Ctl+H で置換しちゃいましょう。

シート名一括変更置換9

このように変更一覧が完成しましたので、先ほどのVBAウィンドウに新しくコードを書いていきます。
先ほど入力(コピペ)したコードの下に新しい処理をいれます。
次は下記コードをコピペしてください。

Sub sheet_name_change()
Dim w_sheet
Dim row_cnt
Dim flag

'一覧のシート名が存在しているか確認
For row_cnt = 2 To Range("A2").End(xlDown).Row
flag = 0
For Each w_sheet In Worksheets
If Cells(row_cnt, 1).text = w_sheet.Name Then
flag = 1
Exit For
End If
Next w_sheet
If flag = 0 Then
MsgBox Cells(row_cnt, 1) & "がありません。", vbExclamation
Exit Sub
End If
Next row_cnt

'シート名書き換え処理
On Error GoTo error1 'エラーが起きたらerror1にジャンプ
For row_cnt = 2 To Range("A2").End(xlDown).Row
Sheets(Cells(row_cnt, 1).Text).Name = Cells(row_cnt, 2)
Next row_cnt
Exit Sub

'エラーが起きたらここから
error1:
MsgBox "シート名:" & Cells(row_cnt, 2) & "はシート名に使えない文字が含まれていた可能性があります。", vbExclamation
End Sub

シート名一括変更置換10

それでは先ほど同様に元のエクセルファイルに戻って、マクロを実行してみましょう。
次はsheet_name_changeというマクロ名が表示されているので、選択して実行を押します。
※この際、シートは「all_sheet_name」を表示している状態にしてください。

シート名一括変更置換11

シート名一括変更置換12

これでシート名の一括変更が完了しました!お疲れ様です!

B他のファイルで同じ処理がしたいんだけども…

そうですよね。このエクセルファイル内で一括変更しててもしょうがないですよね。
実はこのマクロ処理、エクセルに組み込むことが可能です。
組み込んでしまえば、どのファイルを開いた時にも同様の処理が行えるようになります。

まずはこのファイルを保存するのですが、シートとかそのままでもいいので名前を付けて保存を選択します。
次に、保存ファイル形式はExcel アドイン(*.xlam)を選択しますと、保存先フォルダも…AddInsというフォルダに勝手に変わります。

ファイル名は特にしていないのですが、私は「sheet_name_change.xlam」にします。

シート名一括変更置換13

保存しました…が、保存されたの?ファイル名変わってないけど?という方、大丈夫です。
元のファイルはそのまま表示されていますが、ファイルは保存されています。

シート名一括変更置換14

次に、保存したマクロファイルを常にエクセルに組み込む設定をします。
メニューバーの「ファイル」→「オプション」→「アドイン」→「設定」

シート名一括変更置換15

シート名一括変更置換16

すると、保存したファイル名が表示されます!
これをチェックし、OKしてください。

シート名一括変更置換17

最後にツールバーに処理ボタンを追加します。
上の下矢印をクリックし、その他のコマンドをクリック
コマンドの選択を「マクロ」にすると
「all_sheet_name」と「sheet_name_change」が出てくるので追加してOK

シート名一括変更置換18

シート名一括変更置換19

シート名一括変更置換20

シート名一括変更置換21

これですべての設定は完了しました!
正常に動くか、またマクロを動かしてみてくださいね。

さて、こちらのマクロファイル、せっかく作ったのでプレゼントします。
ほしい人は下記ダウンロードもできますのでぜひ使ってみてくださいね。

↓ファイルダウンロード↓
sheet_name_change.zip

「エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?」についてご紹介させていただきました。
最後までお読みいただきありがとうございました。

罫線

関連コンテンツ

罫線

文字列抽出・変更・置換系トピック

SUBSTITUTEで複数置換を一気に行う方法

EXCEL Find関数で複数条件指定したい

EXCEL 置換関数にワイルドカードが使えない!代わりの方法を考えました。

EXCEL findを右から(後ろから)検索する方法

EXCEL 文字列の分割(区切り位置)を関数で行う方法

EXCELで文字列の記号を全て削除!記号を一覧にしてSUBSTITUTE関数で一括置換しよう

EXCELで改行を削除(置換)して消す方法/実はSUBSTITUTE関数でも出来るんです!

EXCELで不要な空白(文字列前後の空白や連続した空白)はtrim関数で削除!

EXCELで特定の文字列を含む行を抽出する関数

検索系トピック

VLOOKUPエラーの原因!N/A,REF,VALUE,NAMEになるのはなぜ?

VLOOKUPでのエラー(#N/A)の対処はIFやIFERRORを使って解決しよう!

VLOOKUPの近似一致(近似値)・あいまい検索って何に使うの?

EXCELで条件に一致したデータを検索&抽出するならVLOOKUP関数

VLOOKUPを"文字列を含む"で行う方法

VLOOKUP 検索範囲にワイルドカード

VLOOKUPを横にコピー!複数列をまとめて抽出するために列番号をずらす方法

VLOOKUPで左側(右から左に)の値を取得する方法はあるのか

VLOOKUPを複数条件/2つの条件を検索値にする方法

VLOOKUPで複数結果を全て抽出・表示する方法/重複したときに2番目以降も抽出できる?

SUMPRODUCT関数では文字列を抽出出来ない/条件に合致した文字列を抽出するならVLOOKUP

集計・ピボット系トピック

SUMIFSで合計範囲を可変にするならOFFSET関数を使いこなそう

SUMIFの条件でワイルドカード(アスタリスク)を使う方法

COUNTIFを含む条件でカウントする方法/ワイルドカード(アスタリスク)を使って含む条件を行おう

SUMIFを複数条件で集計/2つ以上の条件で集計したいときはSUMIFS関数で!

COUNTIFで空白以外(空白ではないセル)のカウントを行う方法

AVERAGEで0以外を集計するならAVERAGEIFで!エクセル平均集計を自在に

AVERAGEIFを複数条件で平均値を算出したいならAVERAGEIFSで

エクセル集計関数を極める!データ集計(合計)、個数カウント、条件付き集計などまとめて紹介!

EXCELで条件付きの合計を算出する時はSUMではなくSUMIFまたはSUMFISで

EXCELで期間集計(日付や範囲指定での抽出合計)ならSUMIFS関数

COUNTIF,COUNTIFSの使い方を徹底解説!条件の個数カウントを極めよう

エクセル 掛け算の関数PRODUCT関数/SUM関数の掛け算版があった!

EXCELの月別集計(日別データを月別に集計する方法)はSUMIFS関数で

SUMIF(SUMIFS)の計算が重い・遅いときは集計の鉄板ピボットテーブル?

ピボットテーブルの初期設定(デフォルト)が変えられない!私なりの解決策

ピボット更新で書式がもとに戻る時の対応

ピボットの空白部分を埋める方法

EXCEL ピボットテーブルで重い・遅いときの改善方法

EXCELで行ずつ・行おきの集計方法を紹介

VLOOKUPで合計(集計)は出せない!VLOOKUPの代わりにあの関数で条件付き合計を出そう!

EXCELで時間帯別に集計する方法/日時データから時間を抽出

マクロで自動化!簡易ツールの作成やVBAの活用例を紹介

エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?

複数のCSVファイルを結合(マージ)!エクセルVBAを使用したCSV結合ツールの作り方

EXCEL フォルダ内のブック(ファイル)を結合(統合)/VBAマクロで複数ファイルをまとめる!

EXCELで複数画像を一括挿入貼り付け!VBA(マクロ)で作っちゃいました。

エクセル マクロ(VBA)で複数のグラフを自動作成!コード教えます。

EXCEL 全シート名を取得し一覧に!VBAを利用して一瞬で解決

EXCEL シート毎にファイル分割保存する方法/マクロ(VBA)で解決!

EXCELで複数ファイルを集計するツール/マクロ(VBA)を使って作ってみました。

EXCELマクロ(VBA)って何?とりあえず試しに使ってみよう

その他EXCELテクニック

エクセル シート移動のショートカットはCtl+PageDownまたはCtl+PageUp

エクセル 一番下まで移動はスクロールだと大変?キーボードでの瞬間移動方法を紹介!

エクセルで以上・以下の条件を指定し、判定や集計を行う方法

エクセルでユニークなリスト(重複をなくしたリスト)をちゃちゃっと作る方法

エクセルで1から10まで連番を入力する方法/オートフィル機能を使ってみよう

エクセルプルダウンリストを極める!設定・作成方法から応用技まで紹介

EXCEL プルダウン(リスト)を連動させる二つの方法/入力規則を使いこなせ!

エクセルでエラーを表示しない方法/ISERROR関数とIFERROR関数で解決

EXCEL 文字の繰り返し関数(REPT関数)

EXCEL リンクでシート間を楽々移動!ハイパーリンクで別シートへ移動

EXCEL業務効率化!私が行っている4つの効率化テクニック

IF関数でワイルドカード(アスタリスク)は使えない?IF関数で含むを条件にする方法

EXCEL 時間の足し算(計算)ってどうやるの?時・分・秒の計算方法まとめました

EXCEL アルファベットでオートフィルは無理?

1行おきに行挿入する方法

EXCEL よく使うショートカット

EXCELフィルタをショートカットで行う方法

EXCEL 空白セルを一括で埋める方法

EXCELで2列以上の複数列を一列にまとめる方法

EXCEL 大量シートの管理/indirect関数の活用

EXCELで重複したデータをカウント!連番をつけ重複データを見つける方法

エクセルで行または列を固定し、スクロールさせずに常に見える状態にする方法

EXCELファイルサイズが大きい!重い!容量を軽く小さくする方法はある?

EXCEL 列の数字(番号)取得はCOLUMN関数で/英語アルファベットの取得方法は?

EXCELでフィルタした行を削除するのが重い(遅い)時の解決方法

EXCELでシート名の参照なんて出来たの!?indirect関数の使い方

エクセル家計簿を自作してみました(無料でダウンロードもできます)

コラム

あなたのエクセルスキルはどのくらい?エクセルが使える人、出来る人のレベルとは

罫線

★人気記事 TOP10

エクセルプルダウンリストを極める!設定・作成方法から応用技まで紹介

VLOOKUP 検索範囲にワイルドカード

EXCEL Find関数で複数条件指定したい

EXCELで期間集計(日付や範囲指定での抽出合計)ならSUMIFS関数

VLOOKUPを複数条件/2つの条件を検索値にする方法

EXCEL 置換関数にワイルドカードが使えない!代わりの方法を考えました。

VLOOKUPを"文字列を含む"で行う方法

EXCEL findを右から(後ろから)検索する方法

VLOOKUPの近似一致(近似値)・あいまい検索って何に使うの?

EXCEL 大量シートの管理/indirect関数の活用

★新着記事

エクセル シート移動のショートカットはCtl+PageDownまたはCtl+PageUp

VLOOKUPエラーの原因!N/A,REF,VALUE,NAMEになるのはなぜ?

エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?

エクセル 一番下まで移動はスクロールだと大変?キーボードでの瞬間移動方法を紹介!

エクセルで以上・以下の条件を指定し、判定や集計を行う方法

エクセルでユニークなリスト(重複をなくしたリスト)をちゃちゃっと作る方法

エクセルで1から10まで連番を入力する方法/オートフィル機能を使ってみよう

SUMIFSで合計範囲を可変にするならOFFSET関数を使いこなそう

エクセル家計簿を自作してみました(無料でダウンロードもできます)

EXCELでお困りの方へ

EXCEL効率化したい仕事人

当サイトでは皆さまがEXCEL作業で行き詰った問題を解決するべく王道のテクニックからちょっと特殊なテクニックまで 様々な中〜上級技・プチテクニックを公開し、少しでも皆様の業務効率改善に役立てていただければと思い、ネタをまとめています。

EXCELの作業で悩み・お困りごとはありませんか?
集計に時間がかかり過ぎ。効率化をしたい。などなど日頃から奮闘しているあなたに少しでも参考になる情報を与えることが出来れば幸いです。

質問・相談なども受け付けておりますが、最近忙しくて返事が遅れてしまいます。。。
サイト内の内容についてのご質問は優先的に受け付けておりますが、それ以外のご相談などはお断りすることもありますのでご了承ください。
※サンプルのエクセルファイルなど添付していただくと回答もスムーズに行える場合が多いです。

お問い合わせはコチラ