[[20220831100054]]　『別ブックから複数のシートの特定の最終行を取得し』（まるまる）　ページの最後に飛ぶ

『別ブックから複数のシートの特定の最終行を取得したい』（まるまる）

ご教示願いたいです。
内容としては在庫管理です。

別ブック内に複数のシートがあり｡
※製品毎にシートがある。

これをVBAで各シートのB列を起点に最終行を取得して在庫表ブックに一覧で把握したい｡
※B列は入力する際に日付が入るため。

何卒、ご指導宜しくお願い致します。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

＞B列を起点に最終行を取得

「B列の最終行を取得」とは、何か違うのですか？

＞在庫表ブックに一覧で把握したい｡

最終行だけ把握して、意味があるのですか？

（わからん） 2022/08/31(水) 10:10

分からん様

コメントありがとうございます。
入出庫及び数量が縦に入力されていくため
基本的に最終行が最新のデータになっております。
情報が少なく申し訳ありません。

　　　A　　　　B　　　　　C　　　　　D　　　　E
1　CODE　　　日付　　　　入庫　　　　出庫　　在庫

上記のようになっておりまして
基本的に　B列はかならずなにか入力する際は日付がはいるため空白がありませんので
最終行を取得しやすいのかなと考えました。
（まるまる） 2022/08/31(水) 10:17

＞最終行を取得しやすいのかなと考えました。

ごもっともですが「最終行を取得」とは、行番号を取得することだと思っています。

（わからん） 2022/08/31(水) 10:26

＞VBAで各シートのB列を起点に最終行を取得して在庫表ブックに一覧で把握したい。
どこでどう詰まっているのですか？
単純に【対象ブックの各シート】を巡回して【B列最終行】を調べ【在庫表ブック（の出力用シート）】に出力するだけですよね？

現状のコードを示して、どこがどのようになってしまうのか（××の箇所で○○というエラーが発生する、エラーにはならないが××になるはずが○○になる等）説明されてはどうでしょうか？

（もこな２） 2022/08/31(水) 10:29

 おはようございます。。。＾＾
こんな感じでせうか。
シート名Sheet1が対象です。
Sub my_getlastrow()
    With Worksheets("Sheet1")
        MsgBox .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
End Sub
（隠居Z） 2022/08/31(水) 11:15

1.コードは「在庫表」ブックに記述する
2.データ収集対象ブックをファイルダイアログを開き決定する
3.収集したデータは「在庫表」ブックに新シートを追加し記述する
4.シートごとに最新の在庫状況が最終行に記述されているので各シートの最終行のデータを収集
5.「最終行」はB列基準で決定する

練習としてこんな感じのマクロを作ってみたいと思います。
何か違うところがあったら教えてください。
（下手の横好き） 2022/08/31(水) 11:56

トピ主からの反応がないので、詰まっている箇所が今ひとつ解りませんが、集約用のシートに、製品名（シート名）と最新の日付（B列最終行）の在庫数（E列）をコピーしていけばいいだけだったりしませんかね。

    Sub テキトー()
        Dim 最終行の行番号 As Long
        Dim 出力行 As Long
        Dim WS As Worksheet
        Dim 出力シート As Worksheet

        Stop 'ブレークポイントの代わり

        Set 出力シート = Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        出力シート.Name = Format(Now, "yyyymmdd_hhmmss")
        出力シート.Range("A1：E1").Value = Array("由来ブック", "由来シート", "CODE", "日付", "在庫")
        出力行 = 2

        For Each WS In Workbooks("hoge.xlsx").Worksheets
            出力シート.Cells(出力行, "A").Value = WS.Parent.Name
            出力シート.Cells(出力行, "B").Value = WS.Name

            最終行の行番号 = WS.Cells(WS.Rows.Count, "B").End(xlUp).Row
            If 最終行の行番号 < 2 Then
                出力シート.Cells(出力行, "C").Value = "データ無し"
            Else
                Intersect(WS.Range("A:B,E:E"), WS.Rows(最終行の行番号)).Copy 出力シート.Cells(出力行, "C")
            End If

            出力行 = 出力行 + 1
        Next WS

    End Sub

（もこな２） 2022/08/31(水) 13:14

 Sub Sample()
    Dim wbZaiko As Workbook
    Dim wbData  As Workbook
    Dim ws      As Worksheet
    Dim importData() As Variant
    Dim filePath As String
    Dim lastRow As Long
    Dim i As Long
    'データ収集対象ブックをファイルダイアログを開き決定する
    filePath = Application.GetOpenFilename("Excel ファイル,*.xls?")
    'ファイルが選択されなかったら終了
    If filePath = "False" Then Exit Sub

    '「在庫表」ブックはThisWorkbookとする
    Set wbZaiko = ThisWorkbook
    'データ収集対象ブックを開く
    Set wbData = Workbooks.Open(filePath)

    'データ収集用配列を再定義
    ReDim inportData(1 To wbData.Worksheets.Count, 1 To 5)
    'データ収集用ブックの全シートを巡回
    For Each ws In wbData.Worksheets
        'B列最終行を基準にして
        lastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
        'A～E列の値を配列に入れる
        For i = 1 To 5
            importData(ws.Index, i) = ws.Cells(lastRow, i).Value
        Next
    Next
    'データ収集対象ブックを閉じる（保存しない）
    wbData.Close SaveChanges:=False

    '「在庫表」ブックにシートを追加
    With wbZaiko.Worksheets.Add
        'シート名は本日の日付をyyyymmdd形式で
        .Name = Format(Date, "yyyymmdd")
        'A1セルを基準に収集データを入力
        .Range("A1").Resize(UBound(importData, 1), UBound(importData, 2)) = importData
    End With
End Sub

つくってみました。
（下手の横好き） 2022/08/31(水) 14:12

もこな様　隠居Z様　下手の横好き様

ご返信遅くなり申し訳ございません。
また、ご教示頂きありがとうございます。

私が基礎的なことを理解できていないためどこでつまっているのか再確認していました。
一番、頭が真っ白になってしまうことは、
別のファイルの指定したシートの特定列最終行の値を取得したいときに
どうやって別ファイルを開くんだ？となってしまいます・・・・。

皆様から頂きましたコードをテストしましたが
各々素晴らしい結果でございました。

自分なりに考えたのが下記

Sub test()
Dim i As Long, j As Long
Dim lRow As Long

Row = Cells(Rows.Count, "F").End(xlUp).Row

     For i = 1 To lRow
     For j = 1 To lRow
      Cells(i, j)= _
             MsgBox  ExecuteExcel4Macro ("'C～\[ブック名.xlsm]Sheet1'!R" & i & "C" & j)

End Sub
こんなんしか思いつかなくて・・・・。

（まるまる） 2022/09/01(木) 09:49

わからん様

抜けておりすみまんせ。
最終行の考え方を再度、勉強致します。
ありがとうございます。
（まるまる） 2022/09/01(木) 10:01

もこな２様

一点お伺いしたいのですが、
複数シート（例えば　10品目）ある場合は

 ＞For Each WS In Workbooks("hoge.xlsx").Worksheets
こちらを複数回かけばよいのでしょうか？

（まるまる） 2022/09/01(木) 10:36

追記

もなこ2様の頂きましたコードは参照元のBOOKを開いていないとエラーになりますか？
私が無知のため、参照元をとじて実行したら「インデックス範囲～」と出てきてしまいました。

申し訳ございません。
（まるまる） 2022/09/01(木) 10:40

おはよ～ございます。＾＾
ブックを開く方法は色々有ると思いますが
私は
Workbooks.Open(開きたいブックのフルパス)
をよく使いますです。
Set
で変数に格納すれば扱いやすいと思います。
ループ処理すれば大量ブックでも処理可能かと思いますです。
思うばっかで。。。相済みません。＾＾；
開けたブックは、用が済んだら必ず閉じませう。。。(￣▽￣；)
でわ
m(_ _)m
（隠居Z） 2022/09/01(木) 10:47

隠居Z

ご教示ありがとうございます。
なるほど、先に開いて処理して
end sub 前に閉じれば良いのですね！！

（まるまる） 2022/09/01(木) 10:53

＞※製品毎にシートがある。
レイアウトは「上記のようになっておりまして」と同じなんですか。
（???） 2022/09/01(木) 11:10

 Sub test()
    Dim c
    For Each c In Worksheets
        MsgBox c.Name
    Next
End Sub
シートはこんな感じで、一度眺めてみれば
解りやすいかもしれません
私は、ループ処理を組む時は思い通りの動作を
しているか、よく、視認いたします。←ちょい面倒ですけどね＾＾；
m(__)m
（隠居Z） 2022/09/01(木) 11:11

隠居Z様

ありがとうございます。
ステップインとういうものを知り
上記コードテストしてみました。
動きの理解が出来、少し向上できました！！
（まるまる） 2022/09/01(木) 11:33

理解しやすいように、最小限のコード

 Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim r As Long

    Set wb = Workbooks.Open("C:\****\***.xlsx")

    For Each ws In wb.Worksheets
        r = ws.Cells(Rows.Count, 2).End(xlUp).Row
        ws.Rows(r).Copy ThisWorkbook.Sheets("最新").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
    Next

    wb.Close False

 End Sub

（マナ） 2022/09/01(木) 12:26

???様

はい、その通りでございます。
（まるまる） 2022/09/01(木) 13:14

マナ様

ご教示ありがとうございます。
動作させて理解してみます。

＞ws.Rows(r).Copy ThisWorkbook.Sheets("最新").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
ここでインデックス～エラーになるので何が理解できていないか勉強してみます。
（まるまる） 2022/09/01(木) 13:17

＞Sheets("最新")

実際のシート名に修正

（マナ） 2022/09/01(木) 13:21

既に皆さんからアドバイス頂いているのでおなか一杯かもしれませんが何点か。

■１
＞どうやって別ファイルを開くんだ？となってしまいます
既にアドバイスがあるように、そのまま"開けば"いいだけです。

＞参照元のBOOKを開いていないとエラーになりますか？
経験されたように、開いてなければエラーになります。

■２
＞自分なりに考えたのが～
VBAの世界では基本的にシートやセル（オブジェクトと言います）は、ちゃんと指定すればいちいちアクティブにしたり選択したりする必要はありません。
また、【標準モジュール】でシートの指定を省略した場合、ActiveSheetを指定したものとみなされるルールです。

したがって、複数のブックやシートを相手にする処理を考えるならば、どのブック、どのシートが対象なのかをコード中に明記するようにすると良いです。

■３
＞複数シート（例えば　10品目）ある場合は～こちらを複数回かけばよいのでしょうか？
　For Each WS In Workbooks("hoge.xlsx").Worksheets

上記は、【WS】という変数に[hoge.xlsx]というブックのシート（の集まり）から１つずつ取り出しなさいという命令になっています。

ステップ実行をしてWSに何が格納されたかチェックしてみるとよくわかると思いますが、[hoge.xlsx]というブックに複数シートあったならば、

 ループ１回目・・・・・【WS】に１番目のシートが格納される
 ループ２回目・・・・・【WS】に２番目のシートが格納される
 ループ３回目・・・・・【WS】に３番目のシートが格納される
　　 ・
　　 ・
　　 ・

というように、【WS】が変化しつつ、シートの数分繰り返し処理をすることになりますので、１０回書く必要はありません。

■４
データ元となるブックが１つなのか複数あるのかは質問から読み取ることができませんでしたが、たとえば、自ブックと同じフォルダに存在するブックを片っ端から集約していくならば下記のような感じになります。
興味があれば【ステップ実行】して研究してみてください。

    Sub 研究用さんぷる()
        Dim ファイル名 As String
        Dim WB As Workbook
        Dim SH As Worksheet
        Dim 出力シート As Worksheet
        Dim 出力行 As Long
        Dim 最終行 As Long

        Stop 'ブレークポイントの代わり

        Set 出力シート = Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        出力シート.Name = Format(Now, "yyyymmdd_hhmmss")
        出力シート.Range("A1：E1").Value = Array("由来ブック", "由来シート", "CODE", "日付", "在庫")
        出力行 = 2

        ファイル名 = Dir(ThisWorkbook.Path & "\*.xls?")
        Do Until ファイル名 = ""
            If ファイル名 <> ThisWorkbook.Name Then
                Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & ファイル名)

                For Each SH In WB.Worksheets
                    出力シート.Cells(出力行, "A").Value = WB.Name
                    出力シート.Cells(出力行, "B").Value = SH.Name

                    最終行 = SH.Cells(SH.Rows.Count, "B").End(xlUp).Row
                    If 最終行 < 2 Then
                        出力シート.Cells(出力行, "C").Value = "データ無し"
                    Else
                        Intersect(SH.Range("A:B,E:E"), SH.Rows(最終行)).Copy 出力シート.Cells(出力行, "C")
                    End If
                    出力行 = 出力行 + 1
                Next SH
            End If

            WB.Close False
            ファイル名 = Dir()
        Loop

    End Sub

（もこな２） 2022/09/01(木) 16:59

もこな２様

ご返信遅くなりすみません。

ちゃんと指定すればいちいちアクティブにしたり選択したりする必要はありません。目からうろこです。
この理解が出来ていませんでした。
必ずシートを動かす際はActiveにするものとばかり・・・・。

＞For Each
こちらも勘違いをしておりました。
シート内のセルに働きかけるものと思ってました。

頂いたコードで勉強しますっ！！！！！
ありがとうございます。

（まるまる） 2022/09/02(金) 10:56

解決したようなので
Power Queryを勉強中の方向けに。

＞これをVBAで各シートのB列を起点に最終行を取得して在庫表ブックに一覧で把握したい｡

こんなときこそ、Power Queryの出番ですね。

 let
    ソース = Excel.Workbook(File.Contents("C:\****\*****\******.xlsx"), null, true),
    フィルターされた行 = Table.SelectRows(ソース, each ([Kind] = "Sheet")),
    ヘッダー昇格 = Table.TransformColumns(フィルターされた行, {"Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}),
    最終行 = Table.TransformColumns(ヘッダー昇格, {"Data", each Table.LastN(_, 1)}),
    テーブル結合 = Table.Combine(最終行[Data]),
    削除された他の列 = Table.SelectColumns(テーブル結合,{"CODE", "日付", "在庫"})
 in
    削除された他の列

↑詳細エディターを開いて、編集し作成しています。
シンプルでわかりやすいロジックなので。
もちろんメニュー操作だけも可能ですが、
ロジックは変わります。ステップも少し増えます。

（マナ） 2022/09/05(月) 23:55

Power Queryを勉強中の方向け（追加）

操作だけで実行する例

 ・データの取得/ファイルから/Excelブックからで、対象ブック指定
 ・ナビゲーター画面でブックを選択し、データの変換ボタンをクリック
 ・[Kind]列でフィルター:Sheetのみ選択
 ・[Name][Data]列以外を削除
 ・[Data]列を展開
 ・１行目をヘッダーとして使用
 ・フィルターで、タイトル行を削除
 ・インデックス列追加
 ・[Name]列を選んで、グループ化
 　　新しい列名：グループ　操作；すべての行
 　　新しい列名：最大値　　操作：最大　　　　列：インデックス
 ・[グループ]列を展開
 ・条件列の追加　新しい列名；カスタム
 　　列名　　　　　　演算子　　　　　　　値（列の選択）　出力
 　　インデックス　　指定の値に等しい　　最大値　　　　　　1　
 ・[カスタム]列でフィルター:１のみ選択
 ・不要な列を削除
 ・閉じて読み込む

インデックス列を追加し、各Sheetごとにインデックスが最大値の行を残しています。
最大値はグループ化で求めています。

（マナ） 2022/09/10(土) 20:51

コメント返信：

[ 一覧(最新更新順) ]

[[20220831100054]] 『別ブックから複数のシートの特定の最終行を取得し』（まるまる） ページの最後に飛ぶ

[[20220831100054]]　『別ブックから複数のシートの特定の最終行を取得し』（まるまる）　ページの最後に飛ぶ