[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数シートのセルの抽出A』(n)
教えていただけたら幸いです。
見積書を作ってシートで増やしていって、1000シートくらいになっています。 日付、aA顧客名、商品、価格などが入った50セル程度の情報を 別シートDに一覧として表示させたいです。
Sub Test()
Dim myShi As Worksheet For Each myShi In Worksheets If myShi.Name <> "D" Then With Worksheets("D").Range("A65536") .End(xlUp).Offset(1, 0).Value = myShi.Range("B2").Value .End(xlUp).Offset(0, 1).Value = myShi.Range("B3").Value End With End If Next End Sub
こちらのやり方で、B2、B3に当てはまるセルを別シートに一覧表示させることができました。 しかし、さらに、50程度のセルを別シートに一覧表示させるためにはどうしたらよいかがわかりません。
B2の情報はDシートのA2から下に、B3の情報は別シートのB2から下に一覧となって表示されました。 ということは、B4、B5、C1、・・・は別シートのC2、D2、・・・というふうに表示できるのかと考えています。
また、もうひとつ、可能でしたら教えていただきたいのは、 元のシート群には、 B2に情報が入っている場合と入っていない場合があり、 できた表をみたところ、情報が入っていないセル分は上に詰められてしまっているよう です。 情報が入っていないセルも空のまま一覧に表示させる方法はありますでしょうか。
もう少し詳しく教えて下さい。
一つのブックに1000シート入っているのですか? それとも、1000シートがいくつかのブックに別れているのですか?
>日付、aA顧客名、商品、価格などが入った50セル程度の情報 この50セルに規則性は有りますか? 具体的に見積書はどの様なレイアウトに成っていますか?
このマクロを実行するタイミングはいつですか? そのブックでデータの変更が無くなった時点で一回実行するのでしょうか? マクロ実行後も、シートを増やしたり 内容が変更になったりするのでしょうか?
(HANA)
ひとつのブックに1000シートくらい入っています。
見積書をシートコピーして作るため、増え続けています。5年分くらいです。
見積書のレイアウトは 列はAからEまでの5列。
行は37行から40行くらいまででA4用紙に印刷可能な範囲にしてあります。
そのうち、D4に見積ナンバー、D5に日付、A7に宛先会社名、A8に担当者名、
18行目から表形式になって、A18が商品名、C18が数量、C19が単価、C20が単価×数量の合計額
これが、28行目くらいまでで終了するシートもあれば、38行目くらいまであるシートもある
という具合です。
表形式の下には、送料、総計、支払期日、納期などが入っていて、これらの情報も一覧に入ればありがたいのですが、表の部分の行数がシートごとにちがうため、 ただセルを指定しただけでは29行目以降は無理かなと思っています。
28行目まではどのシートも同じレイアウトなので、ここまででも構わないので 一覧にしたいと思っています。
マクロの実行後もシートを増やしたり、内容が変更になったりします。
n
一つのブックに 1000シートですか。。。 探すのも追加するのも大変じゃないですか?
纏める方法としては 「1シート1行」でしょうか? それとも、「18行目から表形式になって」の部分はその形を維持したまま にするのでしょうか?
集めてその後、何かに利用するのですよね?
>表形式の下には、送料、総計、支払期日、納期などが入っていて これはどの位置に入っているのですか? 例えば、データが終わってから2行空けてから入っている とか 規則性などは?
>マクロの実行後もシートを増やしたり、内容が変更になったりします。 と言う事は、マクロ実行の度に 1000シートの情報を全て取得し直す必要が出てくる と思いますが。。。まぁこれは コードが完成してからそのスピードに依りますかね。。。 でも、シートもまだまだ増えて行くでしょうし 避けたい感じは有りますよね。
変更する可能性が無くなった物の見分け方 とか無いのでしょうか?
(HANA)
「1シート1行」でしょうか? これは別シートにまとめるときといういみでしょうか。
kyokoさんのマクロを使えば、50列くらいで済むので、1シート1行で、1000行くらいになるということになります。
それとも違う意味でしょうか。わからなくてすみません。
kyokoさんのマクロを生かしたやリ方でかまいません。
たぶん、offset()の指定ができれば、よいのだと考えています。
↓ End(xlUp).Offset(1, 0).Value = myShi.Range("B2").Value
offset()の勉強を自分ですればいいのですが、なかなか理解できていません。
>それとも、「18行目から表形式になって」の部分はその形を維持したまま
にするのでしょうか? 個別のシートの表はその形を維持したままです。
毎日毎日見たいわけではないので、取得しなおすのは構いません。 もし、この1000シートの情報を一覧にまとめることができたら、 逆に、その時点で、このブックは終了させることができるようになります。
kyokoさんのマクロを生かした方法で、offsetの指定方法をどうか教えていただけないでしょうか。(n)
過去ログの方法で行くかどうかは 過去ログの方法が今回のやりたい事とマッチしているかどうか 先に検討するのが良いと思いますよ?
簡単なイメージを書いてみます。 Sheet1 [A] [B] [C] [D] Sheet2 [A] [B] [C] [D] [1] 顧客名 A商店 No 1 [1] 顧客名 Bマート No 2 [2] [2] [3] 品名 単価 数量 金額 [3] 品名 単価 数量 金額 [4] りんご 80 5 400 [4] いちご 100 5 500 [5] みかん 30 5 150 [5] ばなな 50 5 250 [6] 合計 550 [6] りんご 80 5 400 [7] [7] 合計 1,150 [8] [8] 送料 500 [9] 総合計 1,650
まとめ1 [A] [B] [C] [D] [E] [F] [G] [H] [I] [1] 顧客名 No 品名 単価 数量 金額 合計 送料 総合計 [2] A商店 1 りんご 80 5 400 550 [3] A商店 1 みかん 30 5 150 550 [4] Bマート 2 いちご 100 5 500 1,150 500 1,650 [5] Bマート 2 ばなな 50 5 250 1,150 500 1,650 [6] Bマート 2 りんご 80 5 400 1,150 500 1,650 [7] [8] [9]
まとめ2 [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [1] 顧客名 No 品名 単価 数量 金額 品名 単価 数量 金額 品名 単価 数量 金額 [2] A商店 1 りんご 80 5 400 みかん 30 5 150 [3] Bマート 2 いちご 100 5 500 ばなな 50 5 250 りんご 80 5 400
後から商品名で検索などをするのなら、まとめ1の様にしておく方が見やすいと思います。 しかし、1シート40件が1000シート有るなら 40000行必要です。 また、このままでは 共通部分(少なくともA,B列)を重複させる事になります。 (それはそれで、別に構わないと思いますが。)
>1シート1行で、1000行くらいになるということになります。 でしたら、まとめ2 のイメージですよね?
1000シートが1000行で済みますが エクセルに何かをやらせようと思った時 面倒な事に成りそうにも思います。
また、1シート40件有った場合 40*5 = 200列必要に成ります。 ちなみに、HA列が209列にあたります。 こんな横に長い表を作って 果たして見やすいかどうか。。。?
「それでも良いんです!!」って事なら無理にとめる事はしませんが。
でも >個別のシートの表はその形を維持したままです。 とも書いてありますね。 って事は まとめ1 の様で良いのでしょうか。。。?
それとも、 まとめ1の B:F部分と まとめ2のA:B部分を両方作っておく って事なのかな。。。?
か、 まとめ2のA:B部分を作って 個別のシートはそのままでおいておくので 詳細はそれで確認するから良い と言う事なのか。。。?
(HANA)
うまく説明できずすみません。
私の考えていたのはまとめ2になります。 いろいろ考えたのですが、この際、単価数量金額などの情報は、必要ありません。 (各シートを見に行けばよいので)
各シートのD4、D5、A8、A19からA46までを
Sheet1 [A] [B] [C] [D] Sheet2 [A] [B] [C] [D] [4] 2/10 [4] 空セル [5] 001 [5] 002 [7] A商店 [7]B商店 [8] ○様 [8]△様 [19] リンゴ [19]ミカン [20] 空セル [20]イチゴ
別シート
[A] [B] [C] [D] [E] [F] [G] [H] [1] D4(2/10) D5(001) A7(A商店) A8(○様) A19(リンゴ) A20(空セル) A21 A22 [2] D4(空セル) D5(002) A7(B商店) A8(△様) A19(ミカン) A20(イチゴ) A21 A22 : [1000]
これで十分だと思っています。 この別表を使ってなにかをすることは今のところ考えていません。
まとめ1のようにできたほうが確かに見やすいのですが、やはりシートが多すぎると思います。 (n)
分かりました。 こんなコードにしてみてはどうですか?
過去ログのコードは、データを貼り付ける(?)際に毎回 最終行を取得してましたが 今回は対象シートを一つ変えるたびに myCntをカウントアップして行き、一行ずつ下がる様にして居ます。
>この際、単価数量金額などの情報は、必要ありません。(各シートを見に行けばよいので) と言う事なので、B列の番号は抜ける事は無いと思うので 各シートのA1セルへハイパーリンクを設定するコードを追加してみました。 不要の場合は、===で挟まれた部分を削除してもらえると良いと思います。
'------ Sub Test1() Dim myShi As Worksheet Dim myCnt As Long With Worksheets("D") '←データをまとめるシート名に変更 Application.ScreenUpdating = False .Range("2:" & Rows.Count).ClearContents For Each myShi In Worksheets If myShi.Name <> .Name Then myCnt = myCnt + 1 With .Range("A" & myCnt) .Offset(1, 0).Value = myShi.Range("D4").Value .Offset(1, 1).Value = myShi.Range("D5").Value '===↓ハイパーリンクの設定=== .Hyperlinks.Add Anchor:=.Offset(1, 1), _ Address:="", SubAddress:=myShi.Name & "!A1" '===↑ハイパーリンクの設定=== .Offset(1, 2).Value = myShi.Range("A8").Value myShi.Range("A19:A46").Copy .Offset(1, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True End With End If Next Application.Goto .Range("A1"), True Application.CutCopyMode = False Application.ScreenUpdating = True End With End Sub '------
やってみて下さい。
(HANA)
HANA様 ありがとうございます。 やはり、いまの自分ではとても作れそうにないマクロです。
さきほどのなかで↓が抜けていたため、編集で直しました。
[8] ○様 [8]△様
これを加えた場合のマクロはどうなりますでしょうか。
お手数をおかけして申し訳ありません。(n)
基本的には同じですよ? D4,D5,A8が .Offset(1, 0).Value = myShi.Range("D4").Value .Offset(1, 1).Value = myShi.Range("D5").Value .Offset(1, 2).Value = myShi.Range("A8").Value A19:A46だけ少しちがって、範囲をコピーして .Offset(1, 3).〜 で貼り付けています。
元々 各シートのD4 が A列 ・・・ .Offset(1, 0) 各シートのD5 が B列 ・・・ .Offset(1, 1) 各シートのA8 が C列 ・・・ .Offset(1, 2) 各シートのA19〜が D列〜・・・ .Offset(1, 3) それが 最初の二つは同じで 各シートのA7 が C列 各シートのA8 が D列 各シートのA19〜が E列〜 に変わっただけですので、変更は分かりますよね?
やってみて上手く行かない場合は、どの様に変更したのか ↑の様に対応させて教えて下さい。
(HANA)
HANA様、遅くなってすみません。 自分でやってみたところ、できました! 教えてくださって、どうもありがとうございました。(n)
出来ましたか!! 良かったです♪ 一応、過去ログの方からもリンクをはっておきますね。
(HANA)
リンク貼っていただいてありがとうございます!(n)
お世話になります。まとめ1の方法について [[20120222113042]]『複数シートのセル抽出B』で 改めて質問させていただきました。 教えていただけたら幸いです。 (n)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.