第26回.月別ブックより部署別シートに担当別に集計するNo2
マクロ再入門の課題として、
月別ブックより部署別シートに担当別に集計する、
これを実現する実際のマクロを順に見ていきます。
シート「ファイル一覧」に、
サブフォルダ「月別データ」内の全Excelファイルの一覧を取得
シート「データ」に、
ファイル一覧で取得したExcelファイルの先頭シートのデータ集める。
シート「データ」より、
部署別のシートに、担当者・年月ごとの集計値を出力する。
今回は、
・ファイル一覧
・データ収集
この部分の解説になります。
Sub ファイル一覧()
Dim i As Long
Dim FileName As String
Dim PathName As String
Dim ws As Worksheet
Set ws = Worksheets("ファイル一覧")
PathName = ThisWorkbook.Path & "\月別データ\"
ws.Cells.ClearContents
i = 1
FileName = Dir(PathName & "*.xls*")
Do While FileName <> ""
ws.Cells(i, 1) = PathName & FileName
i = i + 1
FileName = Dir()
Loop
End Sub
変数iは、シートにファイル名を書き出す時の行位置に使います。
変数FileNameは、Dir関数で取得したファイル名を入れるのに使います。
変数PathNameは、「月別データ」のフォルダを入れるのに使います。
変数wsは、Worksheets("ファイル一覧")の短い名前として使います。
変数PathNameに、「月別データ」のフォルダを入れます。
Worksheets("ファイル一覧")に前回出力内容が残っている場合があるので、一旦データ消去します。
変数FileNameに、Dir関数を使って「月別データ」フォルダからファイル名を取得
Dir関数のファイル名取得が無くなるまで、以下を処理
・Worksheets("ファイル一覧")のi行に、パスとファイル名つなげてフルパスで出力。
・Worksheets("ファイル一覧")に出力する行位置iを1進める
・Dir関数で次のファイル名を取得
不明な箇所があるようなら、
第21回.ファイル一覧を取得する
Sub データ収集()
Dim i As Long
Dim iLast As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim ws一覧 As Worksheet
Dim wsデータ As Worksheet
Set ws一覧 = Worksheets("ファイル一覧")
Set wsデータ = Worksheets("データ")
With wsデータ
.Cells.Clear
.Range("A1") = "日付"
.Range("B1") = "部署"
.Range("C1") = "担当"
.Range("D1") = "売上数"
.Range("E1") = "売上金額"
.AutoFilterMode = False
End With
For i = 1 To ws一覧.Cells(Rows.Count, 1).End(xlUp).Row
iLast = wsデータ.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set wb = Workbooks.Open(ws一覧.Cells(i, 1))
Set ws = wb.Worksheets(1)
ws.Range("A1").CurrentRegion.Offset(1, 0).Copy Destination:=wsデータ.Cells(iLast,
1)
wb.Close savechanges:=False
Next
End Sub
Worksheets("データ")は、全セルを一旦消去して、1行目に見出しを書いています。
あらかじめ1行目には見出しを入れて置き、2行目以降を消去しても良いです。
CurrentRegion.Offset(1, 0).Clear
と言ったコードになりますね、余裕があれば書いてみて下さい。
ws.Range("A1").CurrentRegion.Offset(1, 0).Copy
この部分ですね。
注意するのは、出力する行位置です。
事前に、
iLast = wsデータ.Cells(Rows.Count, 1).End(xlUp).Row + 1
これで出力してある最終行の次の行を取得してあります。
第22回.複数ブックよりデータを集める
第22回のコードと見比べてみて下さい。
しっかりと違いを把握を出来れば、VBAコードが理解できているという事です。
同じテーマ「マクロVBA再入門」の記事
第20回.全てのシートに同じ事をする（For～Worksheets.Count）
第27回.月別ブックより部署別シートに担当別に集計するNo3
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。