Excel VBA 業務改善

シート操作でエクセルVBAの自動化をさらに効果的にできるかも

普段エクセルで行っている単純作業を自動化できるVBA。

プログラムの学習としても使えるので「プログラミングには縁がない・・」という方も気軽に学習できると思います。

「プログラムはちょっとニガテ」という方でも、エクセルの便利機能「マクロの記録」を使えば、自分で行った処理をエクセルがプログラムにしてくれます。
プログラムを作る時間がない方、プログラムの効果を手軽に実感したい方におススメです。

そんなエクセルVBAのプログラムですが、かんたんなプログラムを作る場合「一つのシートでの処理」となることが多いです。

「全シートで処理ができたらさらに効果的に自動化ができるかも」と思う方もいらっしゃるかもしれません。
そこで今回は、エクセルVBAのシート操作について紹介したいと思います。

こんな方におすすめ

  • エクセルの自動化をより効果的にしたい方
  • シート内の処理に苦戦している方
  • エクセルVBAに興味のある方

シート処理ができると効果的

エクセルVBAでプログラム(マクロの記録など)を作成する場合、同じ処理を全てのシートで行いたい場合には少し不便です。

もし、今開いているシートに対する処理をするプログラムだとしたら、「シート1を開いてプログラムを実行」次に「シート2を開いてプログラムを実行」・・・とせっかくのプログラムの効果を活かしきれません。

特定のシートに対するプログラムの場合

  • 特定のシートに対する処理を行うプログラムを運用
  • しかし、本当は全てのシートに同じ処理を行いたい・・

このような場合は、シート選択の処理を組み合わせることで、さらに効果的なプログラムを作ることができるかもしれません。

全シートに処理をすれば効果的になることも

全シート処理をする方法

処理内容が分かりやすいものが「for文とシート指定の組み合わせ」です。

for分は「指定した範囲で処理を繰り返す」ことができるプログラムの書き方です。

for文の繰り返し内容を「シートの最初から最後まで」とすることで、全シートの処理ができるようになる・・という仕組みです。

全シート名の取得

「全シートの処理・・・本当にそんなことできるの?」と思う方もいらっしゃるかもしれませんので、こちらのプログラムを紹介します。


まずは、全シート名を取得するプログラムを紹介します。

このプログラムは業務改善に直接つながる訳ではありませんが、全シート操作の動きを理解するのに役立つと思います。

プログラムのイメージ


このプログラムでは、シート1から順番に「シート名を取得」した後に「シート1のA列に取得したシート名」を転記します。

Sub 全シート名取得()

'【1】変数の宣言
' sheetName:シート名を入れるための変数
' i:シート番号とシート名を転記する行を決める変数
Dim sheetName As String, i As Long


'シートの最初から最後まで以下の処理を行う
For i = 1 To Worksheets.Count
' シートの名前を取得して、変数sheetNameに入れる
  sheetName = Worksheets(i).Name
  
' シート1のA列のi行目に、取得したシート名を転記する。
  Sheet1.Cells(i, 1) = sheetName
Next

End Sub


ポイント

プログラム実行にシート1のA列を見ると、全シート名が転記できていると思います。

ということは、プログラムに書く「すべてのシートに処理をする」というプログラムが書けるということです。


全シートに処理を行えることが分かったところで具体的なプログラムの紹介をしていきたいと思います。

全シートに同じ処理をする場合

全シートに対して同じ処理をするプログラムを紹介します。

このプログラムは、全シートに対して「A1セルに1と入力する」処理を行う内容です。


プログラムのコードはこちらです。

Sub 全シートに同じ処理()
Dim sheetName As String, i As Long

'シートの最初から最後まで処理を繰り返す
For i = 1 To Worksheets.Count

'処理内容
'シートのA1セルに「1」と入力する。
  Worksheets(i).Range("A1") = 1
Next

End Sub


ポイント

こちらのプログラムの「処理内容」の中の処理を書き換えることで、複雑な作業の自動化をすることもできます。

Worksheets(i).Range("A1") = 1 ←ここを消して好きな処理に書き換えるだけ


ブック内のシートを分解する

エクセルブックにあるシートを、各ブックごとに分解する処理も紹介します。

プログラムのイメージ

例えば・・・

  • 事業所ごとにシートを分ける
  • 担当者ごとにシートを分ける
  • 商品ごとにシートを分ける
  • 勘定科目ごとにシートを分ける

このように一つのブックで情報を管理する場合「シートの内容を各ブックに分解したい」と思うこともあるかもしれません。
そんな時にこちらのプログラムが役立ちます。


コートはこちらです。

Sub 全シートの情報を別ブックに分割()
Dim sheetName As String, i As Long, ws As Worksheet
Dim filePath As String, fileName As String, saveName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False


For i = 1 To Worksheets.Count
  ThisWorkbook.Worksheets(i).Copy



'****ファイル名をシート名にする場合************
fileName = Worksheets(1).Name
'*******************************************

'***保存先ファイルパスをプログラム実行ファイルと同じ場所にする場合***********
filePath = ThisWorkbook.Path
'*********************************************************************

'***保存名***********************
saveName = filePath & "\" & fileName & ".xlsx"
'********************************


ActiveWorkbook.SaveAs fileName:=saveName, _
                    FileFormat:=xlOpenXMLWorkbook

ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True


Next
End Sub

ブック内のシートを分解する【値貼り付け】

ブック内のシートを分解する場合に「各シートを参照した式がある」とエラーになってしまうかもしれません。

そんな時に一番簡単なのが「関数を値に修正する」ことです。

先ほど紹介したプログラムの中に「シート全体を値貼り付けするコードを追加するだけです」
(スマートな書き方もありますが、今回はマクロの記録で作成したコードを追加しました)


コートはこちらです。

Sub 全シートの情報を値貼り付けして別ブックに分割()
Dim sheetName As String, i As Long, ws As Worksheet
Dim filePath As String, fileName As String, saveName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False


For i = 1 To Worksheets.Count
  ThisWorkbook.Worksheets(i).Copy



'****ファイル名をシート名にする場合************
fileName = Worksheets(1).Name
'********************************

'***保存先ファイルパスをプログラム実行ファイルと同じ場所にする場合***********
filePath = ThisWorkbook.Path
'********************************

'***保存名***********************
saveName = filePath & "\" & fileName & ".xlsx"
'********************************

'値貼り付けを行う
Worksheets(1).Cells.Select
Selection.Copy
Selection.PasteSpecial (xlPasteValues)
Worksheets(1).Range("A1").Select

'保存
ActiveWorkbook.SaveAs fileName:=saveName, _
                    FileFormat:=xlOpenXMLWorkbook

ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True


Next
End Sub

もし、値貼り付けしたデータが欲しい方、関数の参照先があるために分解したブックの内容がうまく表示できない場合にはこちらのプログラムを試してみてください。

シート処理で使うコードの補足

シート処理で覚えておくと便利なコードを紹介します。

紹介するもの

  • 最終シート番号の取得方法
  • Worksheets(i)について


順番に紹介していきます。

最終シート番号の取得

最終シート番号の取得について紹介します。

これができるようになると、ブックの中のシート数を気にする必要がなくなります。


最後のシート番号の取得

全シート処理を行う場合に問題になるのが「シートの数」です。

エクセルブックを開いた時に、シートの数が同じということはありません。
(ある時は1つだけ、ある時は10以上など様々です)

プログラムを実行するためのエクセルブックでも、誰かがシートの追加をしてしまうだけでもシート数は変わってしまいます。

そんな時に便利なのが「Worksheets.Count」です。

このコードを書けば、シートの最終番号を取得できるので、ブック内のシート数が変わったとしても問題ありません。

for文と組み合わせることでシート処理が漏れなく行えるようになります。

メモ

For i = 1 To Worksheets.Count
 'ここに全シートに対して行いたい処理を書く
Next

Worksheets(i)って何?

今回の記事で紹介しているコードの中に出てくる「Worksheets(i)」について紹介します。

こちらは「繰り返しのfor文」と「シート番号」が分かるとスッキリすると思います。


まずは、for文から

For i = 1 To Worksheets.Count

'処理内容を書く

Next

for文は、「for ~ next で囲った中の処理」を「指定した回数だけ行う」というものです。

シート処理でいうと、「For i = 1 To Worksheets.Count」の部分が回数を指定している部分です。


Worksheets.Countは、シートの最終番号(エクセルブックの中にシートが10ある場合、10となる)を取得できるのでシートの数だけ同じ処理を行えるようになります。


次は、シート番号について

エクセルブックのシート番号は、シートの左から順に1,2,3・・・と付いています。
(そのため、シートの順番を変更しても常に左から数えます)


for文とシート番号を紹介をしたので、Worksheets(i)について紹介していきたいと思います。

こちらのプログラムを例にしたいと思います。

Sub シート名を表示()
For i = 1 To Worksheets.Count
  MsgBox Worksheets(i).Name
Next
End Sub

こちらのプログラムは全シートの名前を表示していくだけの内容です。

行っていること

前提:ブックの中にシートが3つある場合

  • For i = 1 To Worksheets.Count(この中に3が入る)

そのため、3回処理を行うという意味になります。

for文の中ではこのように処理が行われます。

  • 1回目は「i」の中に「1」が入った状態で処理が行われる。
  • 2回目は「i」の中に「2」が入った状態で処理が行われる。
  • 3回目は「i」の中に「3」が入った状態で処理が行われる。

続いて処理内容について見ていきます。

  • MsgBox Worksheets(i).Name

ここで行っている処理は「シート番号がiのシート名を表示する」です。

for文では、繰り返すたびにiの数字が1,2,3と変わっていきます。

その仕組みを利用して、Worksheets(i)の中の番号も変わるようにしています。

その結果

  1. 1回目はWorksheets(1)のシート名を表示
  2. 2回目はWorksheets(2)のシート名を表示
  3. 3回目はWorksheets(3)のシート名を表示

という処理ができるようになります。

繰り返して行う処理を書き換えることで、毎回行わないといけない提携処理を自動化することができます。


あとがき

今回は、エクセルVBAでシート処理をする方法を紹介しました。

今までマクロの記録で「特定シートの処理」を行ってきた方は、処理の幅が広がると思います。

全シートをエクセルブックに分解するプログラムの出力形式をPDFに変更するなど、応用することができると思います。

同じ処理をシートの数だけ繰り返していた方は試してみてください。

-Excel VBA, 業務改善
-, , ,