未来エンジニア養成所Blog

プログラミングを皆に楽しんでもらうための情報をお届けします。

各 Excel シートにある表の最終行を 別シートへコピーする(ExcelVBA)

今回はExcelVBAを使って、集計シートを作成します。


処理内容

Excelの各シートには、下記のように商品の個数が記録されていて、最終行には合計個数が記録されています。


シート「日報1月1日」

sheet1


シート「日報1月2日」

sheet2


シート「日報1月3日」

sheet3


この各シートの合計個数のみを「集計シート」にまとめます。イメージは下記の通り。

sheet4


ソースコード

まずはコード全体です。

Public Sub 集計()

    Dim sheet_no As Integer
    Dim copy_source As Range
    Dim copy_target As Range
    
    For sheet_no = 1 To Sheets.Count
    
        If Left(Sheets(sheet_no).Name, 2) = "日報" Then
        
            Set copy_source = Sheets(sheet_no).Range("A1").CurrentRegion
            Set copy_source = copy_source.Offset(copy_source.Rows.Count - 1, 1).Resize(1, 1)
            
            Set copy_target = Sheets("集計シート").Range("A1").CurrentRegion
            Set copy_target = copy_target.Offset(copy_target.Rows.Count).Resize(1, 1)
            
            copy_target = Mid(Sheets(sheet_no).Name, 3)
            
            Set copy_target = copy_target.Offset(, 1)
            
            copy_source.Copy
            copy_target.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            
        End If

    Next

End Sub


コード解説

変数定義

まずは変数を3つ定義しています。

Dim sheet_no As Integer
Dim copy_source As Range
Dim copy_target As Range

sheet_no : どのシートを対象にするのか、シート番号を指定するための変数です。

copy_source : コピー元となるセルを保持しておくための変数です。

copy_target : コピー先となるセルを保持しておくための変数です。


繰り返し処理

次に繰り返しの処理です。

For sheet_no = 1 To Sheets.Count
    
  :
  :

Next

変数「sheet_no」を1からシートの数(Sheets.Cout)分繰り返しを実施します。

今回の場合ですと、まずは Sheets(1) になる「日報1月1日」シートを処理して、次に Sheets(2) になる「日報1月2日」シートを処理して、・・・・、と順次それぞれのシートを一つずつ最後まで繰り返し処理をしていきます。

繰り返しを Sheets.Count までとすることで、シートの数が変わっても問題がないようにしています。


条件分岐

次にIf文の条件判定で、「日報」とつくシートだけをコピー対象とするようにしています。

If Left(Sheets(sheet_no).Name, 2) = "日報" Then
        
  :
  :
            
End If

「Left(Sheets(sheet_no).Name, 2)」は、シート名から先頭2文字を取得しています。その取得した2文字が「日報」であった場合のみコピー対象としています。


コピー元の取得

次はコピー元の取得です。

Set copy_source = Sheets(sheet_no).Range("A1").CurrentRegion
Set copy_source = copy_source.Offset(copy_source.Rows.Count - 1, 1).Resize(1, 1)


「Sheets(sheet_no).Range("A1").CurrentRegion」で表全体を取得します。

イメージとしては、下記のような感じで表全体を選択している状態です。

sheet5


その後で、「copy_source.Offset(copy_source.Rows.Count - 1, 1).Resize(1, 1)」をしています。

「Offset(copy_source.Rows.Count - 1, 1) 」ですから、選択した行数分下に移動(今回の例だと5行分下に移動)して、さらに右に1移動します。

イメージとしては、選択している場所が下記のように移動している状態です。

sheet6


そして、さらに 「Resize(1, 1) 」ですので、選択している箇所を 1行1列 にサイズを変更します。

イメージとしては、選択している場所が下記のように1つのセルだけが選択されている状態です。

sheet7


コピー先の取得

今度はコピー先の場所を取得します。

Set copy_target = Sheets("集計シート").Range("A1").CurrentRegion
Set copy_target = copy_target.Offset(copy_target.Rows.Count).Resize(1, 1)

コピー元を取得した時と同様に、「CurrentRegion」「Offset」「Resize」を使用して取得しています。


日付のコピー

集計シートに対象となる日付をコピーします。コピー元はシート名です。

copy_target = Mid(Sheets(sheet_no).Name, 3)

シート名には「日報」という文字が含まれていますので、先頭の「日報」を除いた文字列をMid関数を使用して取得しています。

その所得した文字列を「copy_target」(コピー先)へ代入することで、集計シートの日付欄に日付が記載されます。


セルの移動

日付欄を記述したら、日付欄の右側に個数を貼り付けたいので、コピー先の対象を移動させます。

Set copy_target = copy_target.Offset(, 1)

「Offset(, 1)」で右に1セル分移動します。


個数のコピー&ペースト(形式を選択して貼り付け)

最後に個数をコピーして貼り付けます。

copy_source.Copy
copy_target.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

コピー元は「copy_source」で選択していますので、「copy_source.Copy」でコピーします。

各シートの合計個数は「=SUM(B2:B4)」のようにExcelのSUM関数を使用して計算しています。そのため、そのまま貼り付けてしまうと、集計シートには「=SUM(B2:B4)」が貼り付けられてしまいます。

今回は「式」ではなく「値」を貼り付けたいので、「PasteSpecial」(形式を選択して貼り付け)を使用します。

「Paste:=xlPasteValues」で値を貼り付けます。

そして最後に「Application.CutCopyMode = False」でコピー元の選択を解除します。


最後に

簡単なコピー&ペーストの例ですが、これを応用していくことで、様々な場面で活用できるでしょう。



さらにExcelVBAを学習されたい方は、下記の本を参考にしてみてください。



オンラインプログラミングスクール
未来エンジニア養成所Logo