フォーマットシートをコピーしてリスト名称のシートを連続作成する方法

フォーマットシートをコピーしてリスト名称のシートを連続作成する方法

スポンサーリンク


Excelを使用しているとき、フォーマットとなるシートをコピーして資料を作成することがあります。
1つや2つくらいならそんなに手間ではないですよね。

しかし、10や20もシートを作成して、全てのシート名称をつけるとなるとかなり手間がかかります。
簡単にサクッと出来る方法があれば知りたいですよね?

実は、VBAを使用すればかんたんにリストから複数のシートを作成することが出来ます。
しかも、フォーマットとなるシートをコピーしながら全てのシートにリストの名称をつけることが可能です。

VBAでマクロを用意したExcelファイルを用意しておけば、次からはいくつあってもシート作成まではほとんど手間もかからずに作成できます。
実際、私はこのフォーマットをコピーするExcelファイルでかなり時間短縮しています。

このページを読み終えれば、あなたもフォーマットシートをコピーしてリストからシート作成するのは2分もかからずに出来るようになります。

VBAの内容の説明と、VBAの分からない方でも使えるようにページ最後にダウンロード用ファイルを用意してありますのでご活用ください。

スポンサーリンク

1.定型シートで複数資料を作成するときに便利

定型シートで複数資料を作成するときに便利

 

会社業務のほとんどは定型フォーマット

会社での業務においては、ほとんどの資料は定型の様式が用意されています。
決められた場所に記入していくことで、かんたんに短時間で資料が完成するようになっていますよね。

1つだけシートを流用して作成するのであれば手間はかかりません。
しかし、複数のシートを作成するときにはシート作成だけでも手間がかかります。

新規シートを複数作成するだけでも手間なのに、定型シートをコピーしながら複数シートを名称をつけながら作成するとなれば面倒ですよね。

定型シートは作業を楽に、ミスを少なくするためのもの。
ならば、複数シートを作成するのも定型化してしまうのが、これから紹介する方法です。

 

原価検討をするときに便利

例えば、多くの職種の業務に共通するところで挙げるならば原価の検討や見積り作成。
おそらくほとんどの企業ではフォーマットとなるシートが用意されていることと思います。

製品や多くの種類の見積書を作成するなら、全てのシートに別々の名称をつけたいですよね。
定型のフォーマットを製品などの種類の数だけシート作成していくのは面倒です。

決められた定型フォーマットを流用して見積りを作成するときに名称リストを作成しておけば、定型シートをコピーしながらシート名称をリストの名称にすることが出来ます。

ご紹介する方法を使えば、同じような定型のシートをコピーして複数のシートを作成するときに便利です。

 

目次へ戻る

 

2.フォーマットシートをコピーして複数シートをリスト名称で作成するVBAコード

フォーマットシートをコピーして複数シートをリスト名称で作成するVBAコード

 

使う場面は人それぞれではあるものの、1度覚えて(作成して)しまえば便利に使える方法です。
以下にVBAコードと、VBAコードの登録方法を記載します。

サンプルVBAコード

これよりVBAコードをご紹介します。
自分でマクロにコピーして使用したい、コードを改変して使いたい方は以下よりコピーしてください。

リストを下に読み込むマクロと、リストを右に読み込むマクロの2種類のVBAコードを記載しています。

カーソルのあるセルからリストを下に読み取って複数シートを作成する

 

※下をクリックするとVBAコードが表示されます。

点線以下よりVBAコード
ーーーーーーーーーーーーーーーーー
Sub リストを下に連続シート作成()

Dim i As Long
Dim Addc As Long
Dim Addr As Long
Dim n As Long
Dim a As Long
Dim MySheet As Worksheet

Dim ShName As String
Dim Ws As Worksheet, flag As Boolean

Application.ScreenUpdating = False

Addc = ActiveCell.Column
Addr = ActiveCell.Row
i = 0
Set MySheet = ActiveSheet

Do

n = Addr + i

If MySheet.Cells(n, Addc) = “” Then Exit Do
ShName = MySheet.Cells(n, Addc).Value

flag = False
For Each Ws In Worksheets
If Ws.Name = ShName Then flag = True
Next Ws

If flag = False Then
Worksheets(“fmt”).Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ShName
Else

End If

i = i + 1

Loop

Application.ScreenUpdating = True

a = MsgBox(“シート作成完了!!”, vbInformation)

End Sub

ーーーーーーーーーーーーーーーーー
ここまでVBAコード

カーソルのあるセルからリストを右に読み取って複数シートを作成する

 

※下をクリックするとVBAコードが表示されます。

点線以下よりVBAコード
ーーーーーーーーーーーーーーーーー
Sub リストを右に連続シート作成()

Dim i As Long
Dim Addc As Long
Dim Addr As Long
Dim n As Long
Dim a As Long
Dim MySheet As Worksheet

Addc = ActiveCell.Column
Addr = ActiveCell.Row
i = 0
Set MySheet = ActiveSheet

Do

n = Addc + i

If MySheet.Cells(Addr, n) = “” Then Exit Do
ShName = MySheet.Cells(Addr, n).Value

flag = False
For Each Ws In Worksheets
If Ws.Name = ShName Then flag = True
Next Ws

If flag = False Then
Worksheets(“fmt”).Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ShName
Else

End If

i = i + 1

Loop

Application.ScreenUpdating = True

a = MsgBox(“シート作成完了!!”, vbInformation)

End Sub

ーーーーーーーーーーーーーーーーー
ここまでVBAコード

 

VBAの登録方法

VBAなんてやったことないから分からないという方のために、コードを簡単に登録する方法をまとめました。
以下のページをご覧になれば、あなたも簡単にVBAコードを登録できるようになります。

Excelでマクロ(VBA)を登録して実行する方法|手順さえ分かれば簡単Excelでマクロ(VBA)を登録して実行する方法|手順さえ分かれば簡単

 

目次へ戻る

 

3.複数シートをリスト名称で作成するマクロの使い方

複数シートをリスト名称で作成するマクロの使い方

 

ご紹介したマクロの使うルールとマクロの使い方を説明します。
以下に記載するルールでマクロを使用してください。

フォーマットシート名称は”fmt”にする

ご紹介したマクロを使用するときには、コピー元となるシートの名称は ”fmt” としてください。
マクロ内ではコピー元シートを ”fmt” として読み出すように作成しています。

例えば、すでにフォーマットとして作成してあるシートがあるならば、マクロのあるExcelファイル(ブック)にフォーマットのシートをコピーします。
コピーしたシートはコピー前のシート名称となっているので、シートのタブをダブルクリックして fmt に変更してください。

 

リストは空白セルまで読み取る

マクロを実行するときに指定しているセルを基準にリストを読み取ります。
読み取りが終わる条件は空白セルがあったときです。

かんたんなリストを前提に作成していますので、読み取る行数、列数が増えれば実行時間もかかります。
10000行もある場合は、それなりに時間がかかりますので御了承ください。

 

読み取りたいリストの上端にカーソルを合わせてマクロ実行

リストがタテに並んでいるときには『リストを下に連続シート作成』のマクロを実行してください。
カーソルのあるセルから下に読み取ってシートを作成します。

STEP1
読み取り開始セルを選択
上端のセルを選択する

クリックで拡大

読み取りたいリストの上端のセルを選択する

STEP2
マクロ選択ウインドウを表示
Alt+F8でマクロウィンドウを表示

クリックで拡大

alt + F8 でマクロのウインドウを表示する

STEP3
マクロ実行
リストを下に連続シート作成を選択して実行

クリックで拡大

『リストを下に連続シート作成』を選択して実行する

STEP4
マクロ処理完了
リストを下に読んだ名称のシートが作成される

クリックで拡大

処理が終わると「シート作成完了!!」と表示され、リストが作成されている

処理が完了するとメッセージボックスに『シート作成完了!!』と表示されますので、”OK” をクリックして終了です。

 

読み取りたいリストの左端にカーソルを合わせてマクロ実行

リストがヨコに並んでいるときには『リストを右に連続シート作成』のマクロを実行してください。
カーソルのあるセルから右に読み取ってシートを作成します。

STEP1
読み取り開始セルを選択
左端のセルを選択する

クリックで拡大

読み取りたいリストの左端のセルを選択する

STEP2
マクロ選択ウインドウを表示
Alt+F8でマクロウィンドウを表示

クリックで拡大

alt + F8 でマクロのウインドウを表示する

STEP3
マクロ実行
リストを右に連続シート作成を選択して実行

クリックで拡大

『リストを右に連続シート作成』を選択して実行する

STEP4
マクロ処理完了
リストを右に読んだ名称のシートが作成される

クリックで拡大

処理が終わると「シート作成完了!!」と表示され、リストが作成されている

タテにリストを読み込んだときと同様にメッセージボックスで「シート作成完了!!」と表示されますので、”OK” をクリックしてください。

 

目次へ戻る

 

スポンサーリンク

4.参考ファイルのダウンロード

 

以下のパスワードを入力欄に貼り付けて、Downloadボタンをクリックしてファイルをダウンロードしてください。

PASS:fmt-sheet-create

 

5.さいごに

さいごに

 

以前紹介した方法では新規シートを作成する方法でした。

EXCEL|リストから複数シートを作成Excelでリストから複数シートを作成する(参考VBA、ダウンロードあり)

今回ご紹介した方法は定型シートをコピーしてシートを作成する方法です。
新規シート作成の方法とうまく使い分けして作業を楽にしてください。

業務は型にはめられるものははめてしまって、考えなければならないところだけを考えるようにしていくと時間短縮できます。

Excelでの資料作成はマクロなどをうまく使って、時間短縮して効率良く作業を進めていくといいですよ。

あなたもご紹介した方法をうまく活用して作業を楽にしてくださいね。