[[20120209133706]] 『複数シートのセルの抽出A』(n) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数シートのセルの抽出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行目まではどのシートも同じレイアウトなので、ここまででも構わないので
一覧にしたいと思っています。

 マクロの実行後もシートを増やしたり、内容が変更になったりします。



 一つのブックに 1000シートですか。。。
 探すのも追加するのも大変じゃないですか?

 纏める方法としては
 「1シート1行」でしょうか?
 それとも、「18行目から表形式になって」の部分はその形を維持したまま
 にするのでしょうか?

 集めてその後、何かに利用するのですよね?

 >表形式の下には、送料、総計、支払期日、納期などが入っていて
 これはどの位置に入っているのですか?
 例えば、データが終わってから2行空けてから入っている とか
 規則性などは?

 >マクロの実行後もシートを増やしたり、内容が変更になったりします。 
 と言う事は、マクロ実行の度に 1000シートの情報を全て取得し直す必要が出てくる
 と思いますが。。。まぁこれは コードが完成してからそのスピードに依りますかね。。。
 でも、シートもまだまだ増えて行くでしょうし 避けたい感じは有りますよね。

 変更する可能性が無くなった物の見分け方 とか無いのでしょうか?

 (HANA)

> 探すのも追加するのも大変じゃないですか?
今までは、商品名を検索にかけて、1分も2分もかけて探し出すという具合でした。
それを別のシートにまとめられれば、フィルタなどで簡単に探し出せます。
それが目的です。

「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.