Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1559 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【73344】AdvancedFilterメソッドについて ニャンソ 12/12/21(金) 23:34 質問[未読]
【73346】Re:AdvancedFilterメソッドについて UO3 12/12/22(土) 0:44 発言[未読]
【73348】Re:AdvancedFilterメソッドについて ニャンソ 12/12/22(土) 8:33 質問[未読]
【73349】Re:AdvancedFilterメソッドについて kanabun 12/12/22(土) 10:08 発言[未読]
【73350】Re:AdvancedFilterメソッドについて kanabun 12/12/22(土) 10:17 発言[未読]
【73353】Re:AdvancedFilterメソッドについて ニャンソ 12/12/22(土) 16:22 回答[未読]
【73352】Re:AdvancedFilterメソッドについて ニャンソ 12/12/22(土) 16:18 質問[未読]
【73351】Re:AdvancedFilterメソッドについて UO3 12/12/22(土) 14:07 発言[未読]
【73354】Re:AdvancedFilterメソッドについて ニャンソ 12/12/22(土) 16:28 回答[未読]
【73355】Re:AdvancedFilterメソッドについて UO3 12/12/22(土) 18:23 発言[未読]
【73356】Re:AdvancedFilterメソッドについて ニャンソ 12/12/22(土) 19:14 お礼[未読]
【73358】Re:AdvancedFilterメソッドについて ニャンソ 12/12/24(月) 21:24 回答[未読]
【73360】Re:AdvancedFilterメソッドについて kanabun 12/12/25(火) 0:05 発言[未読]
【73362】Re:AdvancedFilterメソッドについて ニャンソ 12/12/25(火) 20:34 お礼[未読]
【73361】Re:AdvancedFilterメソッドについて UO3 12/12/25(火) 10:39 発言[未読]
【73363】Re:AdvancedFilterメソッドについて ニャンソ 12/12/25(火) 20:54 回答[未読]
【73364】Re:AdvancedFilterメソッドについて UO3 12/12/26(水) 9:53 発言[未読]
【73366】Re:AdvancedFilterメソッドについて ニャンソ 12/12/26(水) 19:46 お礼[未読]
【73369】Re:AdvancedFilterメソッドについて UO3 12/12/27(木) 12:33 発言[未読]
【73371】Re:AdvancedFilterメソッドについて ニャンソ 12/12/27(木) 22:03 回答[未読]
【73372】Re:AdvancedFilterメソッドについて UO3 12/12/27(木) 22:10 発言[未読]
【73379】Re:AdvancedFilterメソッドについて ニャンソ 12/12/28(金) 21:00 回答[未読]
【73373】Re:AdvancedFilterメソッドについて UO3 12/12/27(木) 22:15 発言[未読]
【73380】Re:AdvancedFilterメソッドについて ニャンソ 12/12/28(金) 21:02 回答[未読]
【73382】Re:AdvancedFilterメソッドについて ニャンソ 12/12/28(金) 22:00 発言[未読]
【73385】Re:AdvancedFilterメソッドについて ニャンソ 12/12/29(土) 20:11 回答[未読]
【73374】Re:AdvancedFilterメソッドについて kanabun 12/12/28(金) 11:23 発言[未読]
【73375】Re:AdvancedFilterメソッドについて kanabun 12/12/28(金) 11:42 質問[未読]
【73376】Re:AdvancedFilterメソッドについて UO3 12/12/28(金) 12:05 発言[未読]
【73377】Re:AdvancedFilterメソッドについて kanabun 12/12/28(金) 19:37 発言[未読]
【73378】Re:AdvancedFilterメソッドについて kanabun 12/12/28(金) 20:05 発言[未読]
【73381】Re:AdvancedFilterメソッドについて ニャンソ 12/12/28(金) 21:56 回答[未読]
【73383】Re:AdvancedFilterメソッドについて kanabun 12/12/29(土) 9:31 発言[未読]
【73384】Re:AdvancedFilterメソッドについて kanabun 12/12/29(土) 9:40 発言[未読]
【73386】Re:AdvancedFilterメソッドについて ニャンソ 12/12/29(土) 21:23 回答[未読]
【73387】Re:AdvancedFilterメソッドについて kanabun 12/12/29(土) 21:58 発言[未読]
【73388】Re:AdvancedFilterメソッドについて ニャンソ 13/1/2(水) 21:17 回答[未読]
【73390】Re:AdvancedFilterメソッドについて kanabun 13/1/3(木) 0:26 発言[未読]
【73391】Re:AdvancedFilterメソッドについて ニャンソ 13/1/3(木) 20:39 回答[未読]
【73392】Re:AdvancedFilterメソッドについて kanabun 13/1/3(木) 23:11 発言[未読]
【73396】Re:AdvancedFilterメソッドについて ニャンソ 13/1/4(金) 20:59 回答[未読]
【73409】Re:AdvancedFilterメソッドについて kanabun 13/1/5(土) 19:04 発言[未読]
【73428】Re:AdvancedFilterメソッドについて ニャンソ 13/1/7(月) 21:05 回答[未読]
【73429】Re:AdvancedFilterメソッドについて kanabun 13/1/7(月) 23:27 発言[未読]
【73451】Re:AdvancedFilterメソッドについて ニャンソ 13/1/11(金) 20:54 回答[未読]
【73452】Re:AdvancedFilterメソッドについて kanabun 13/1/11(金) 23:09 発言[未読]
【73477】Re:AdvancedFilterメソッドについて ニャンソ 13/1/14(月) 20:07 回答[未読]
【73488】Re:AdvancedFilterメソッドについて kanabun 13/1/15(火) 9:39 発言[未読]
【73508】Re:AdvancedFilterメソッドについて ニャンソ 13/1/16(水) 21:17 回答[未読]
【73511】Re:AdvancedFilterメソッドについて kanabun 13/1/16(水) 22:51 発言[未読]
【73521】Re:AdvancedFilterメソッドについて ニャンソ 13/1/17(木) 20:46 回答[未読]
【73522】Re:AdvancedFilterメソッドについて kanabun 13/1/17(木) 21:58 発言[未読]
【73525】Re:AdvancedFilterメソッドについて ニャンソ 13/1/17(木) 22:47 回答[未読]
【73389】Re:AdvancedFilterメソッドについて ニャンソ 13/1/2(水) 21:26 回答[未読]
【73365】Re:AdvancedFilterメソッドについて kanabun 12/12/26(水) 9:54 発言[未読]
【73367】Re:AdvancedFilterメソッドについて ニャンソ 12/12/26(水) 20:06 回答[未読]
【73368】Re:AdvancedFilterメソッドについて kanabun 12/12/26(水) 21:09 質問[未読]
【73370】Re:AdvancedFilterメソッドについて ニャンソ 12/12/27(木) 21:49 回答[未読]
【73359】Re:AdvancedFilterメソッドについて ニャンソ 12/12/24(月) 21:25 質問[未読]
【73344】AdvancedFilterメソッドについて
質問  ニャンソ  - 12/12/21(金) 23:34 -
引用なし
パスワード
   エクセルのフィルタ機能を使い、別シートへ転記する方法

book1sheet1に

地区・従業員NB・従業員名・申請金額・実金額

のデータがあり、フィルタで地区ごとにして
book2sheet1に

従業員NB・従業員名・申請金額・実金額

を転記するにはどのようにマクロを登録すれば良いでしょうか？

検索等しましたが、あまり分からなく…

お力を貸して頂ければと思います。

よろしくお願い致します。
【73346】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/22(土) 0:44 -
引用なし
パスワード
   ▼ニャンソ さん：

こんばんは
フィルターオプション（AdvancedFilter)の組合せでできるかと思いますが
たとえばBook1側の地区が、Ａ，Ｂ，Ｃ　と３種類あったとします。
【地区毎にして】とありますが、Book2側には、この３種類がどのように転記されるのですか？
Ａ　だけを抽出して　Ａだけにするのですか？
それとも地区を１行にまとめ、数値項目を合計してＡ，Ｂ，Ｃすべて転記するのですか？
前者であれば抽出する地区をどうやって指定する予定ですか？
後者であれば、Book2側に地区情報がありませんが、それでいいのですか？
【73348】Re:AdvancedFilterメソッドについて
質問  ニャンソ  - 12/12/22(土) 8:33 -
引用なし
パスワード
   ▼UO3 さん：

おはようございます。
説明不足で申し訳ありません。

>【地区毎にして】とありますが、Book2側には、この３種類がどのように転記されるのですか？

Ａ地区のみ表示しＡ地区の従業員NB・従業員名・申請金額・実金額をBook2側に表示させ
その後、Ｂ地区・Ｃ地区と続けたいです。

>Ａ　だけを抽出して　Ａだけにするのですか？
そうです。

>前者であれば抽出する地区をどうやって指定する予定ですか？

book1でフィルタにて抽出した地区をbook2に転記したいです。

出来れば、フィルタもマクロに登録したいです。

よろしくお願い致します。

>▼ニャンソ さん：
>
>こんばんは
>フィルターオプション（AdvancedFilter)の組合せでできるかと思いますが
>たとえばBook1側の地区が、Ａ，Ｂ，Ｃ　と３種類あったとします。
>【地区毎にして】とありますが、Book2側には、この３種類がどのように転記されるのですか？
>Ａ　だけを抽出して　Ａだけにするのですか？
>それとも地区を１行にまとめ、数値項目を合計してＡ，Ｂ，Ｃすべて転記するのですか？
>前者であれば抽出する地区をどうやって指定する予定ですか？
>後者であれば、Book2側に地区情報がありませんが、それでいいのですか？
【73349】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/22(土) 10:08 -
引用なし
パスワード
   ▼ニャンソ さん：

> book1sheet1に
>
> 地区・従業員NB・従業員名・申請金額・実金額

Book1 Sheet1 に

　　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
1　　地区　　従業員NB　従業員名　申請金額　実金額
2　　Ａ
3　　Ａ
4　　Ｂ
5　　Ｃ
6　　Ｂ
7　　Ａ

とあるとき、
▼
同じシートの Ｇ列に
1　　　　　　　　　　　　地区
2　　　　　　　　　　　　Ａ
3　　　　　　　　　　　　Ｂ
4　　　　　　　　　　　　Ｃ
5　　　　　　　　　　　　

と、「地区」の種類を書き出す方法(フィルタオプションで)
はご存知ですか？

▼
　　 Ｇ列
1　　地区
2　　Ａ

と書いておいて、
元表から 「地区」が「Ａ」の行だけを 別シートに
抽出転記する方法（手作業でのフィルタオプション）はご存知ですか？
^^^^^^^^^^^^^^^^
【73350】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/22(土) 10:17 -
引用なし
パスワード
   もっとも、データを「地区」ごとにまとめるだけなら、

Book1 Sheet1 に

　　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
1　　地区　　従業員NB　従業員名　申請金額　実金額
2　　Ａ
3　　Ａ
4　　Ｂ
5　　Ｃ
6　　Ｂ
7　　Ａ

を 別シートにそっくりコピーしてから、
表全体を選択して、
A列をキーにして「並び替え」

これだけでＯＫですね？
【73351】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/22(土) 14:07 -
引用なし
パスワード
   ▼ニャンソ さん：

こんにちは

実現すべき姿と、その姿を実現するための手順が明確になれば
それに適したコードはいかようにも書くことができます。

kanabunさんもおっしゃっていますが、

・単に、Book2側で、Ａの下にＢ，Ｂの下にＣ、・・・　ということなら
　シートの内容をコピペして並び替え
・Book2側に、たおえば今回は Ｂ　とか　今回は　Ｃ　といったように抽出するなら
　フィルターオプションやオートフィルターを使ったコードがよろしいですが
　その場合、その、Ｂ　や　Ｃ　と、どのように指定したいのか、これはコードの問題ではなく
　操作手順としてどうかんがえておられるのか。
・あるいは、Ａ，Ｂ，Ｃ，・・・と、それぞれの地区ごとに、シートを分けて
　地区の数だけシートを作成するということなのか。

このあたり、やりたいのは、どんんな形なんでしょう？
【73352】Re:AdvancedFilterメソッドについて
質問  ニャンソ  - 12/12/22(土) 16:18 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。

>と、「地区」の種類を書き出す方法(フィルタオプションで)
>はご存知ですか？

A列ではなくG列での抽出ということでしょうか？
分からないです。

>元表から 「地区」が「Ａ」の行だけを 別シートに
>抽出転記する方法（手作業でのフィルタオプション）はご存知ですか？
>^^^^^^^^^^^^^^^^

手作業でフィルタをしコピペするということですか？

無知ですみません。
【73353】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/22(土) 16:22 -
引用なし
パスワード
   ▼kanabun さん：

その地区だけを表示し別シートに転記です。
Ａ地区だけを表示、それ以外は非表示にします。
その後、そのシートを印刷

それを地区全部繰り返す形です。
【73354】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/22(土) 16:28 -
引用なし
パスワード
   ▼UO3 さん：

こんばんは。


>・Book2側に、たおえば今回は Ｂ　とか　今回は　Ｃ　といったように抽出するなら

そのように抽出したいです。

>・あるいは、Ａ，Ｂ，Ｃ，・・・と、それぞれの地区ごとに、シートを分けて
>　地区の数だけシートを作成するということなのか。
>
>このあたり、やりたいのは、どんんな形なんでしょう？

kanabun さんへも書きましたが、
その地区だけを表示し別シートに転記です。
Ａ地区だけを表示、それ以外は非表示にします。
その後、そのシートを印刷

それを地区全部繰り返す形です。

全地区分を1つの同じシートに転記します。

よろしくお願い致します。
【73355】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/22(土) 18:23 -
引用なし
パスワード
   ▼ニャンソ さん：

まだ、実際の処理イメージの希望がクリアではないのですがとりあえず。
Book1,Book2 がともに開かれている状態で以下を試してみてください。
なお、Book1,Book2のブック名は実際のものに変更してくださいね。

Sub Sample()
　　Dim c As Range
　　Dim sh As Worksheet
　　
　　Application.ScreenUpdating = False
　　
　　Set sh = Workbooks("Book2").Sheets("Sheet1")
　　
　　With Workbooks("Book1").Sheets("Sheet1")
　　　　.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"), Unique:=True
　　　　.Range("H1").Value = .Range("G1").Value
　　　　For Each c In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
　　　　　　.Range("H2").Value = c.Value
　　　　　　sh.Cells.ClearContents
　　　　　　.Columns("A:E").AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　CriteriaRange:=.Range("H1:H2"), CopyToRange:=sh.Range("A1"), Unique:=False
　　　　　　sh.PrintOut
　　　　Next
　　　　.Range("G1").CurrentRegion.Clear
　　End With
　　　　
　　Application.ScreenUpdating = True
　　
End Sub
【73356】Re:AdvancedFilterメソッドについて
お礼  ニャンソ  - 12/12/22(土) 19:14 -
引用なし
パスワード
   ▼UO3 さん：

ありがとうございます。

説明が下手で申し訳ありません。

週明けに会社にて処理してみますので、

結果につきましては来週ご報告致します。
【73358】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/24(月) 21:24 -
引用なし
パスワード
   ▼ニャンソ さん：
こんばんは。

本日行ってみましたが、実行エラーが出ました。

.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"),

指定された範囲を使用してコマンドを完了出来ませんでした。
範囲内のセルを選択し、コマンドを実行してください。

と出ました。

私の説明不足です、申し訳ありません。
【73359】Re:AdvancedFilterメソッドについて
質問  ニャンソ  - 12/12/24(月) 21:25 -
引用なし
パスワード
   以前説明したものから来週より処理方法が変わりました。
申し訳ありません。
再度説明させて頂きます。


Book1-1
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額

Book1-2
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額

Book2
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ
空白・従業員NB・従業員名・申請日・申請金額・実金額

Book3
Ａ・Ｂ・Ｃ・Ｄ・Ｅ
県名・地区・グループ番号・従業員番号・従業員名

Book1は他部署より送られてきます。
その中にグループ番号はないので、Book3よりコピペします。

Book1-2・Book2の1〜7行目は見出し？となっており、Ｂ8から表があります。


処理手順

1.私が処理する地区のみ（3地区）フィルタにて表示し、別シートへコピペ
2.そこにBook3からグループ番号をコピペ
3.グループ番号・従業員番号順に並び変え
4.グループ番号1のみ表示しBook2へコピペ
5.合計金額（申請金額・実金額それぞれ）を算出
6.Book2を印刷
7.Book2の内容を削除
4〜7を12回同じシートに行う


＊グループは12あります。
＊Book2の申請日は手書きのため空白

上記が手作業で処理を行う手順になります。

まだ不明な点があれば説明致します。

以前と質問内容が変わり申し訳ありません。

よろしくお願い致します。
【73360】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/25(火) 0:05 -
引用なし
パスワード
   ▼ニャンソ さん：

>本日行ってみましたが、実行エラーが出ました。
>
>.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"),
>
>指定された範囲を使用してコマンドを完了出来ませんでした。
>範囲内のセルを選択し、コマンドを実行してください。
>
>と出ました。
>
>私の説明不足です、申し訳ありません。

上のエラーが出たというステートメントは、何をしているところか
分かりますよね？
A列が「地区」で、地区の種類(リスト)を「空いている列」G列に
フィルタオプションの指定で書き出しているところです。

このステートメントは

　　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
1　　地区　　従業員NB　従業員名　申請金額　実金額
2　　Ａ
3　　Ａ
4　　Ｂ
5　　Ｃ
6　　Ｂ
7　　Ａ

というような表を想定していますから、
そういうレイアウトの表であれば、別にエラーは出ないと思います。
●目的は「地区」の種類を書き出すこと
そのとき、想定しているのは
　(1) 元データ範囲は 「地区」の列
　　　タイトル行は 1行目。
　(2) 抽出先は 元表より 1列空けた先の G列
ということで、

>.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"),

となっています。

エラーとなるのは、表のレイアウトが変わって
> Book1-1
> Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
> 空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額

となっているからです。
なので、
元データ範囲を
.Columns("A:A")
でなく、 「地区」の列 → D列ですか？ そこのタイトル行から D列のデータ最終セル
までの範囲に変更し、

また、抽出先を
> .Range("G1")
でなく、 元表のデータ範囲の外、 たとえば AA列に変更すれば、
　　.Range("AA1")
そこのエラーは無くなるのではないですか？


もっとも
「地区」のユニークなリストは、もう分かっていて、
> 1.私が処理する地区のみ（3地区）
とあらかじめ決まっているのなら、抽出地区名のリスト作りに関しては
フィルタオプションで抽出するまでもなく、
AA列ならAA列に その3地区名を書き出しておいて、それらをとなりの
AB列 [AB2]に地区名を順に書き出し

　　AB列
1　　地区
2　　Ａ地区

CriteriaRange:= を [AB1:AB2] として 別シートに抽出し印刷する

というような流れになるのじゃありませんか？
シートのレイアウトが変わったからうまく行かないのではなく、
UO3 さんのサンプルコードは
Book1 Sheet1 に

　　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
1　　地区　　従業員NB　従業員名　申請金額　実金額
2　　Ａ
3　　Ａ
4　　Ｂ
5　　Ｃ
6　　Ｂ
7　　Ａ

のような表のときの
　(1)地区名リスト、
　(2)順に地区名で元表より抽出AdvancedFilter
するサンプルですから、
まず、この元表で別シートに抽出できることを確認し、
サンプルコードの各行が どういう処理をしているのか確認しておけば、

元表が変わっても、応用できるんじゃないかと思います。
【73361】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/25(火) 10:39 -
引用なし
パスワード
   ▼ニャンソ さん：

おはようございます

>指定された範囲を使用してコマンドを完了出来ませんでした。
>範囲内のセルを選択し、コマンドを実行してください。

kanabunさんからもコメントがありますが、

.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"),

これは　「A列から」一意の値を取り出すコードです。
なのでA列が空白なら、実行時エラーになるのは当然ですね。

新しい要件にあわあせて、はい、コードですというのもできますが、
また、そもそもの要件、まず、Ａ，次にＢ、・・・という【連側操作手順】のイメージがわきません。
なので、サンプルとして、このようなことですかと、確認の意味も含めたコードです。

当初のレイアウトのシートを作り、そこで実行して、処理内容が意図したものかどうか
それを確認願います。その結果を踏まえて、じゃぁ、新しい要件なら、こういったコードという
回答をするという順番にしませんか？
【73362】Re:AdvancedFilterメソッドについて
お礼  ニャンソ  - 12/12/25(火) 20:34 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>上のエラーが出たというステートメントは、何をしているところか
>分かりますよね？
>A列が「地区」で、地区の種類(リスト)を「空いている列」G列に
>フィルタオプションの指定で書き出しているところです。
>
>このステートメントは
>
>　　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
>1　　地区　　従業員NB　従業員名　申請金額　実金額
>2　　Ａ
>3　　Ａ
>4　　Ｂ
>5　　Ｃ
>6　　Ｂ
>7　　Ａ
>
>というような表を想定していますから、
>そういうレイアウトの表であれば、別にエラーは出ないと思います。
>●目的は「地区」の種類を書き出すこと
>そのとき、想定しているのは
>　(1) 元データ範囲は 「地区」の列
>　　　タイトル行は 1行目。
>　(2) 抽出先は 元表より 1列空けた先の G列
>ということで、
>
>>.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"),
>
>となっています。
>
>エラーとなるのは、表のレイアウトが変わって
>> Book1-1
>> Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
>> 空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額
>
>となっているからです。
>なので、
>元データ範囲を
>.Columns("A:A")
>でなく、 「地区」の列 → D列ですか？ そこのタイトル行から D列のデータ最終セル
>までの範囲に変更し、
>
>また、抽出先を
>> .Range("G1")
>でなく、 元表のデータ範囲の外、 たとえば AA列に変更すれば、
>　　.Range("AA1")
>そこのエラーは無くなるのではないですか？

ご説明ありがとうざいます。
ＡをＤに変えたところエラーは消えました。


>もっとも
>「地区」のユニークなリストは、もう分かっていて、
>> 1.私が処理する地区のみ（3地区）
>とあらかじめ決まっているのなら、抽出地区名のリスト作りに関しては
>フィルタオプションで抽出するまでもなく、
>AA列ならAA列に その3地区名を書き出しておいて、それらをとなりの
>AB列 [AB2]に地区名を順に書き出し
>
>　　AB列
>1　　地区
>2　　Ａ地区
>
>CriteriaRange:= を [AB1:AB2] として 別シートに抽出し印刷する
>
>というような流れになるのじゃありませんか？

3地区ある場合は

　　AB列
1　　地区
2　Ａ地区
3　Ｂ地区
4　Ｃ地区

と書き

CriteriaRange:= を [AB1:AB4]

とすれば良いのでしょうか？


>まず、この元表で別シートに抽出できることを確認し、
>サンプルコードの各行が どういう処理をしているのか確認しておけば、
>
>元表が変わっても、応用できるんじゃないかと思います。

そうですね。
内容を理解できていれば出来ますね…

ご丁寧にありがとうございます。
マクロ等初心者の私でもエラー等の内容が少しわかりました。
【73363】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/25(火) 20:54 -
引用なし
パスワード
   ▼UO3 さん：

こんばんは。


>当初のレイアウトのシートを作り、そこで実行して、処理内容が意図したものかどうか
>それを確認願います。その結果を踏まえて、じゃぁ、新しい要件なら、こういったコードという
>回答をするという順番にしませんか？

そうですね。あの説明だけでは内容すべて伝わらないですよね。
申し訳ありません。


まず、先日のサンプルコードの結果です。

Sub Sample()
　　Dim c As Range
　　Dim sh As Worksheet
　　
　　Application.ScreenUpdating = False
　　
　　Set sh = Workbooks("Book2").Sheets("Sheet1")
　　
　　With Workbooks("Book1").Sheets("Sheet1")
　　　　.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"), Unique:=True
　　　　.Range("H1").Value = .Range("G1").Value
　　　　For Each c In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
　　　　　　.Range("H2").Value = c.Value
　　　　　　sh.Cells.ClearContents
　　　　　　.Columns("A:E").AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　CriteriaRange:=.Range("H1:H2"), CopyToRange:=sh.Range("A1"), Unique:=False
　　　　　　sh.PrintOut
　　　　Next
　　　　.Range("G1").CurrentRegion.Clear
　　End With
　　　　
　　Application.ScreenUpdating = True
　　
End Sub

こちらを

Sub Sample()
　　Dim c As Range
　　Dim sh As Worksheet
　　
　　Application.ScreenUpdating = False
　　
　　Set sh = Workbooks("Book2").Sheets("Sheet1")
　　
　　With Workbooks("Book1").Sheets("Sheet1")
　　　　.Columns("D:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("AB1"),
　　　　　　　　 ^^^^^　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ^^^^^^
Unique:=True
　　　　.Range("AC1").Value = .Range("AB1").Value
　　　　　　　　^^^^　　　　　　　　 ^^^^^
　　　　For Each c In .Range("AB2", .Range("AB" & .Rows.Count).End(xlUp))
　　　　　　　　　　　　　　 ^^^^^　　　　　^^
　　　　　　.Range("AC2").Value = c.Value
　　　　　　　　　 ^^^^
　　　　　　sh.Cells.ClearContents
　　　　　　.Columns("D:E").AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　 ^^
　　　　　　　　CriteriaRange:=.Range("AC1:AC2"), CopyToRange:=sh.Range("D1"), Unique:=False
　　　　　　　　　　　　　　　　　　　 ^^^^^^^^　　　　　　　　　　　　 ^^^
　　　　　　sh.PrintOut
　　　　Next
　　　　.Range("AB1").CurrentRegion.Clear
　　　　　　　 ^^^^^
　　End With
　　　　
　　Application.ScreenUpdating = True
　　
End Sub

のように変更したところ

Book2の元々あった罫線以外（文字・数字等）が消えて2枚同じものが印刷されました。

Book2も見たところ同じように罫線以外（文字・数字等）が消えていました。

質問ですが、Ｈは何を指しているのでしょうか？

Ｇの次のため、ＡＣとしてしまいましたが、大丈夫でしょうか？

また、Ｅも同様何を指していますか？

Ｅについては良く分からないためそのままで登録しています。

厚かましく申し訳ありませんが、この行は何を実行しようとしているコードなのかを

説明して頂けると助かります。

お手数おかけし申し訳ありませんが、よろしくお願い致します。
【73364】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/26(水) 9:53 -
引用なし
パスワード
   ▼ニャンソ さん：

おはようございます

まず、お願いしたように「最初のレイアウトの簡単なBook1」を作成して、
私がアップしたコード「そのままで」試していただきたかったですね。
で、「感触」をつかんで、要件の理解が正しいのか間違っているのかを確認いただきたかったのです。
もっとも、後から提示された要件は、レイアウト以外にも、がらっとかわっていますので
「感触」もつかみづらいかもしれませんが。

ニャンソさんは【フィルターオプション】についてはご存じでしょうか？
あるリストから、抽出すべきタイトルと、そのタイトル列での抽出条件を与えて、データを抽出する機能です。
（エクセル上の操作で可能ですし、それをコードで実行することもできるものです）

で、旧レイアウトではリスト範囲がＡ〜Ｅ列。　なので、　少し離れた　Ｇ列とＨ列を作業列にして
Ｇ列には、Ａ列（地区）から【一意の値】を抽出し、そこで抽出された地区を１つずつ　Ｈ列の検索条件欄にいれて
地区の数だけフィルタリングを行い印刷するコードです。

少なくとも、改訂されたコードで対象にしているBook1のリストは　D:E ですので、
抽出されたとしても、この２列しか抽出されません。
（２枚同じものが印刷されたというところは、ちょっと不思議ですが）

また、アップしたコードは、Book2への抽出の前に Book2のセルの値をすべてクリアしています。
そこに、あらかじめ何かがセットされているという説明がなかったものですから。

さらに、そこには「罫線」があるのですか？
そうしますと、かりに罫線が１００行分セットされていたとして、抽出が１０行でも、からっぽの
９０行も印刷されますが、それでいいのｐですか？
また、仮に、抽出が３００行だったら罫線なしの行が２００行印刷されますが、それでいいのですか？

このような部分も、きちんと説明いただかないと、こちらからは、実際のシートが見えないわけですので
いくらコードをアップしても、使えないコードになる公算が大きいですね。
【73365】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/26(水) 9:54 -
引用なし
パスワード
   ▼ニャンソ さん：
よこから失礼。

とりあえず、ユニークな地区名を空いている列に書き出す部分ですが、

抽出転記したい元の表が、

> 1〜7行目は見出し？となっており、Ｂ8から表があります。

とありますから、

抽出したい「地区データの範囲」にたいしてAdvancedFilter をかける
わけですから、
その範囲を
> Workbooks("Book1").Sheets("Sheet1").Columns("D:D")
と列全体を指定するのはまずいです。
範囲のいちばん上のセルは [D7]を指定します。
範囲の一番下のセルは D列の最終データのあるセルを指定します。

　With Workbooks("Book1").Sheets("Sheet1")
　　　.Range("D7", .Cells(.Rows.Count, 4).End(xlUp)).AdvancedFilter _
　　　　 Action:=xlFilterCopy, _
　　　　 CopyToRange:=.Range("AB1"), _
　　　　 Unique:=True
　　・・・
　End With

のような感じでいけるかと思います。

で、このAB列の「地区名リスト」をそのままフィルタオプションの
CriteriaRange として使うと、「全データ」が抽出されてしまうので、
そのあとやってることは となりのAC列に 先頭[AC1]セルが 横の[AB1]
と同じ列見出しを書き込んでおいて(↓)
>　.Range("AC1").Value = .Range("AB1").Value

ループで [AC2]セルに AB列の2行目から最後の「地区名」データまで
１つづつ順に 「地区名」を代入して、 [AC1:AC2] をいつも Criteria
Range として 一回のAdvancedFilterにつき、 [AC2]にある一地区だけを
抽出する作業を実行しているのです。

前にも言いましたように、抽出したい地区名があらかじめ決まっているなら
[AC2]セルに順番に 欲しい「地区名」を書き入れて [AC1:AC2]をCriteria
Range としてAdvancedFilterをかけていけばよい（もちろんマクロコードで）
ので、今話題にしている 前段のAdvancedFilter (Unique:=True) は不要と
なります。
　　　　
【73366】Re:AdvancedFilterメソッドについて
お礼  ニャンソ  - 12/12/26(水) 19:46 -
引用なし
パスワード
   ▼UO3 さん：

こんばんは。

すみません！！
変えずにですね。
本日は作業ができなかったので、明日以降改めて行います。


>ニャンソさんは【フィルターオプション】についてはご存じでしょうか？
>あるリストから、抽出すべきタイトルと、そのタイトル列での抽出条件を与えて、データを抽出する機能です。
>（エクセル上の操作で可能ですし、それをコードで実行することもできるものです）

フィルタの詳細設定で行うものですよね？
普通のフィルタしか使用したことがないので、いまいち…


>で、旧レイアウトではリスト範囲がＡ〜Ｅ列。　なので、　少し離れた　Ｇ列とＨ列を作業列にして
>Ｇ列には、Ａ列（地区）から【一意の値】を抽出し、そこで抽出された地区を１つずつ　Ｈ列の検索条件欄にいれて
>地区の数だけフィルタリングを行い印刷するコードです。

　Ｈ列
1　Ａ地区
2　Ｂ地区
3　Ｃ地区

上記をフィルタオプションで設定し行うということですね。


>少なくとも、改訂されたコードで対象にしているBook1のリストは　D:E ですので、
>抽出されたとしても、この２列しか抽出されません。
>（２枚同じものが印刷されたというところは、ちょっと不思議ですが）

地区・グループ番号のみの抽出…

不思議というのはそのコードではありえないという事ですよね？


>また、アップしたコードは、Book2への抽出の前に Book2のセルの値をすべてクリアしています。
>そこに、あらかじめ何かがセットされているという説明がなかったものですから。
>
>さらに、そこには「罫線」があるのですか？
>そうしますと、かりに罫線が１００行分セットされていたとして、抽出が１０行でも、からっぽの
>９０行も印刷されますが、それでいいのｐですか？
>また、仮に、抽出が３００行だったら罫線なしの行が２００行印刷されますが、それでいいのですか？

罫線等も伝えなければならなかったのですね。
申し訳ありません。

大丈夫です。
大体罫線を全部使いますので…空白のまま印刷で大丈夫です。

>このような部分も、きちんと説明いただかないと、こちらからは、実際のシートが見えないわけですので
>いくらコードをアップしても、使えないコードになる公算が大きいですね。

申し訳ありません。

よろしくお願い致します。
【73367】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/26(水) 20:06 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>　With Workbooks("Book1").Sheets("Sheet1")
>　　　.Range("D7", .Cells(.Rows.Count, 4).End(xlUp)).AdvancedFilter _
>　　　　 Action:=xlFilterCopy, _
>　　　　 CopyToRange:=.Range("AB1"), _
>　　　　 Unique:=True
>　　・・・
>　End With
>
>のような感じでいけるかと思います。

　　　.Columns("D:D").AdvancedFilter
ここを

　　　.Range("D7", .Cells(.Rows.Count, 4).End(xlUp)).AdvancedFilter _

と変更するということですね。

　　　　
>で、このAB列の「地区名リスト」をそのままフィルタオプションの
>CriteriaRange として使うと、「全データ」が抽出されてしまうので、
>そのあとやってることは となりのAC列に 先頭[AC1]セルが 横の[AB1]
>と同じ列見出しを書き込んでおいて(↓)
>>　.Range("AC1").Value = .Range("AB1").Value

　CriteriaRange:=.Range("AC1:AC2"),

この部分を

.Range("AC1").Value = .Range("AB1").Value

に変更するということですか？

>前にも言いましたように、抽出したい地区名があらかじめ決まっているなら
>[AC2]セルに順番に 欲しい「地区名」を書き入れて [AC1:AC2]をCriteria
>Range としてAdvancedFilterをかけていけばよい（もちろんマクロコードで）
>ので、今話題にしている 前段のAdvancedFilter (Unique:=True) は不要と
>なります。

ＡＣ列
1　　地区
2　Ａ地区
3　Ｂ地区
4　Ｃ地区

上記をフィルタオプションで設定し行うということですよね？

よろしくお願いいたします。
【73368】Re:AdvancedFilterメソッドについて
質問  kanabun  - 12/12/26(水) 21:09 -
引用なし
パスワード
   ▼ニャンソ さん：

>>前にも言いましたように、抽出したい地区名があらかじめ決まっているなら
>>[AC2]セルに順番に 欲しい「地区名」を書き入れて [AC1:AC2]をCriteria
>>Range としてAdvancedFilterをかけていけばよい（もちろんマクロコードで）
>>ので、今話題にしている 前段のAdvancedFilter (Unique:=True) は不要と
>>なります。
>
>ＡＣ列
>1　地区
>2　Ａ地区
>3　Ｂ地区
>4　Ｃ地区
>
>上記をフィルタオプションで設定し行うということですよね？
>

いえ、そうではないんです。

一回目は
>ＡＣ列
>1　地区
>2　Ａ地区

で、Ａ地区データを抽出・転記→ 印刷

二回目は
>ＡＣ列
>1　地区
>2　Ｂ地区

で、Ｂ地区データを抽出・転記→ 印刷

3回目は
>ＡＣ列
>1　地区
>2　Ｃ地区

で、Ｃ地区データを抽出・転記→ 印刷

というくり返しなんですけど。
CriteriaRange は いつも [AC1:AC2] の↓セル固定です。
【73369】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/27(木) 12:33 -
引用なし
パスワード
   ▼ニャンソ さん：

こんにちは

かなりkanabunさんのものと重なっているんだろうとは思いますが。
（きっと）kanabunさんの構成と同じく、抽出ブックである Book2 側にニャンソさんが担当する地区を入力して実行してください。
かってながらBook1,Book2は、以下のようになっているということが前提です。

(Book1)

タイトル行が７行あるということですが、７行目には、ユニークな項目名がセットされていると認識しています。

(Book2)

・B〜F列が抽出・印刷領域。
・G列は空白列
・H列、H1 に　"地区" (Book1 の D7 と同じ文字列）、H2から下に、担当する地区コード（１つでも３つでも１００個でもＯＫ）

また、印刷は Book2の B〜F列のみを対象にしています。
kanabunさんのアドバイスにあったように「環境にやさしい」コードにしています。
最終的には
.Columns("B:F").PrintPreview
これを
.Columns("B:F").PrintOut
にしてください。

なお、このマクロは　Book1,Book2,Book3 とは別の独立したマクロブックに書きます。
実行時には　３つのブックがすべて開かれているということを前提にしています。

Sub Sample2()
　　Dim dicP As Object
　　Dim dicG As Object
　　Dim v As Variant
　　Dim c As Range
　　Dim sv3 As Variant
　　Dim wb As Workbook
　　Dim sh As Worksheet
　　Dim x As Long
　　Dim i As Long
　　Dim grp As Variant
　　
　　Application.ScreenUpdating = False
　　
　　Set wb = Workbooks("Book1.xls")
　　
　　Set dicP = CreateObject("Scripting.Dictionary")
　　Set dicG = CreateObject("Scripting.Dictionary")
　　
　　With Workbooks("Book3.xls").Sheets(1)
　　　　For Each c In .Range("D2", .Range("D" & .Rows.Count).End(xlUp))
　　　　　　dicP(c.Value) = c.Offset(, -1).Value
　　　　　　dicG(c.Offset(, -1).Value) = True
　　　　Next
　　End With
　　
　　With Workbooks("Book2.xls").Sheets(1)
　　　　sv3 = .Range("B7:F7").Value
　　　　.Range("I1").Value = wb.Sheets(1).Range("E7").Value
　　　　
　　　　For Each grp In dicG
　　　　　　.Range("B7:F7").Value = Array(wb.Sheets(1).Range("F7").Value, _
　　　　　　　　　　　　　　　　wb.Sheets(1).Range("G7").Value, _
　　　　　　　　　　　　　　　　wb.Sheets(1).Range("G7").Value, _
　　　　　　　　　　　　　　　　wb.Sheets(1).Range("I7").Value, _
　　　　　　　　　　　　　　　　wb.Sheets(1).Range("J7").Value)
　　　　　　.Range("H2", .Range("H" & .Rows.Count).End(xlUp)).Offset(, 1).Value = grp
　　　　　　Intersect(.Range("B1", .UsedRange), .Columns("B:F")).Offset(7).ClearContents
　　　　　　For x = 1 To 2
　　　　　　　　Set sh = wb.Sheets(x)
　　　　　　　　Call maintGrp(sh, True, dicP)
　　　　　　　　If x = 1 Then
　　　　　　　　　　i = 7
　　　　　　　　Else
　　　　　　　　　　i = .Range("B" & .Rows.Count).End(xlUp).Row + 1
　　　　　　　　　　.Range("B" & i & ":" & "F" & i).Value = .Range("B7:F7").Value
　　　　　　　　End If
　　　　　　　　sh.Range("B7", sh.Range("B" & sh.Rows.Count).End(xlUp)).Resize(, 9).AdvancedFilter _
　　　　　　　　　　Action:=xlFilterCopy, CriteriaRange:=.Range("H1").CurrentRegion, _
　　　　　　　　　　CopyToRange:=.Cells(i, "B").Resize(, 5), Unique:=False
　　　　　　　　If x <> 1 Then .Rows(i).Delete
　　　　　　　　Call maintGrp(sh, False)
　　　　　　Next
　　　　　　.Range("B7", .Range("B" & .Rows.Count).End(xlUp)).Resize(, 5).Sort _
　　　　　　　　Key1:=.Columns("B"), Order1:=xlAscending, Header:=xlYes
　　　　　　.Range("B7:F7").Value = sv3
　　　　　　.Columns("B:F").PrintPreview
　　　　Next
　　　　Intersect(.Range("B1", .UsedRange), .Columns("B:F")).Offset(7).ClearContents
　　　　.Columns("I").Clear
　　　　.Range("B7:F7").Value = sv3
　　End With
　　
　　Application.ScreenUpdating = True
　　
End Sub

Private Sub maintGrp(sh As Worksheet, wt As Boolean, Optional dicP As Object)
　　Dim c As Range
　　With sh.Range("F8", sh.Range("F" & sh.Rows.Count).End(xlUp))
　　　　If wt Then
　　　　　　For Each c In .Cells
　　　　　　　　c.Offset(, -1).Value = dicP(c.Value)
　　　　　　Next
　　　　Else
　　　　　　.Offset(, -1).ClearContents
　　　　End If
　　End With
End Sub
【73370】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/27(木) 21:49 -
引用なし
パスワード
   ▼kanabun さん：


こんばんは。

>というくり返しなんですけど。
>CriteriaRange は いつも [AC1:AC2] の↓セル固定です。

CriteriaRangeというのを使うと

一回目は
>ＡＣ列
>1　地区
>2　Ａ地区

になるという事…ですか？
【73371】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/27(木) 22:03 -
引用なし
パスワード
   ▼UO3 さん：

こんばんは。

まず、先日のコードの結果ですが、

地区ごとにそれぞれ正しく印刷できました。


>（きっと）kanabunさんの構成と同じく、抽出ブックである Book2 側にニャンソさんが担当する地区を入力して実行してください。

Book2
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ
空白・従業員NB・従業員名・申請日・申請金額・実金額

ここに手入力で地区を入力するということですか？


>kanabunさんのアドバイスにあったように「環境にやさしい」コードにしています。

どういうことでしょうか？


>Sub Sample2()
>　　Dim dicP As Object
>　　Dim dicG As Object
>　　Dim v As Variant
>　　Dim c As Range
>　　Dim sv3 As Variant
>　　Dim wb As Workbook
>　　Dim sh As Worksheet
>　　Dim x As Long
>　　Dim i As Long
>　　Dim grp As Variant
>　　
>　　Application.ScreenUpdating = False
>　　
>　　Set wb = Workbooks("Book1.xls")
>　　
>　　Set dicP = CreateObject("Scripting.Dictionary")
>　　Set dicG = CreateObject("Scripting.Dictionary")
>　　
>　　With Workbooks("Book3.xls").Sheets(1)

最後の行のBook3で「インデックスが範囲にありません」

と表示されます。

Book1.2.3全て開いており、名前も間違っていません。

何が間違っているのでしょうか…？

よろしくお願いいたします。
【73372】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/27(木) 22:10 -
引用なし
パスワード
   ▼ニャンソ さん：

>まず、先日のコードの結果ですが、
>
>地区ごとにそれぞれ正しく印刷できました。

一安心です。

>Book2
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ
>空白・従業員NB・従業員名・申請日・申請金額・実金額
>
>ここに手入力で地区を入力するということですか？

はい。H1 に　"地区"、H2から下に、ニャンソさんの担当する地区コードをいくつでも。
ここに記載された地区を抽出します。

>>kanabunさんのアドバイスにあったように「環境にやさしい」コードにしています。
>
>どういうことでしょうか？

いやぁ・・・
PrintOut とすると、印刷されてしまいますね。
まだ、テスト中ですから、やたら紙に印刷しないで　PrintPreview おして
プレビュー画面で印刷イメージを確認してもらえればと思ったものですから。
最終的には　PrintPrevie を PrintOut に変えてください。
>>　　With Workbooks("Book3.xls").Sheets(1)
>
>最後の行のBook3で「インデックスが範囲にありません」
>
>と表示されます。
>
>Book1.2.3全て開いており、名前も間違っていません。
>
>何が間違っているのでしょうか…？

Book3.xls というブック名ならこのエラーはでません。
どこか違っているのでしょうね。
Book3.xlsx とか？
【73373】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/27(木) 22:15 -
引用なし
パスワード
   ▼ニャンソ さん：

もし、Book3.xls で間違いないということでしたら
その状態で以下のコードを実行していただけませんか？
でてくるブック名を教えてください。

Sub Test()
　　Dim wb As Workbook
　　For Each wb In Workbooks
　　　　MsgBox wb.Name
　　Next
End Sub
【73374】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/28(金) 11:23 -
引用なし
パスワード
   ▼ニャンソ さん：
▼UO3 さん：

スレ主でもないのにすみません m(_ _)m

UO3 さんが最後に提示されたサンプルコード読んでみて
何だかよく分からなくなってきたので、どういうシートレイアウト
を想定するとトライできるのか、確認させて下さい m(_ _)m

まず元データのあるBook1 ですが、同じ表構成の2シートがあるわけですか？
>Book1-1
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額
>
>Book1-2
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額

(Q1.)↑の列番号
> Ｈ〜Ｘ・Ｙ・Ｘ
とありますが、最後の列番号は Ｚのまちがいですか？

>Book1-2・Book2の1〜7行目は見出し？となっており、Ｂ8から表があります。
(Q2.)Book1の各シートの↑の列見出しは 7行目に書いてあるんですよね？

>Book2
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ
>空白・従業員NB・従業員名・申請日・申請金額・実金額
>＊Book2の申請日は手書きのため空白
(Q3.)Book2.Sheets(1) はココへ出力して印刷するためのシートのよう
　　ですね？B列から印刷領域ですか？この列見出しは何行に書いてある
　　のでしょう？

>Book3
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ
>県名・地区・グループ番号・従業員番号・従業員名
>
>Book1は他部署より送られてきます。
>その中にグループ番号はないので、Book3よりコピペします。
>＊グループは12あります。
(Q4.)グループ番号は他のどの項目に紐づけられているのでしょうか？
　　Book1のデータの何をみると グループ番号が分かるのですか？
　　県名から連想されるグループ番号があるのですか？
　　それとも、地区 をみると グループ番号が分かるのですか？
　　あるいは 従業員番号 とか 従業員名 をみると分るのですか？


▼UO3 さん：
お世話になります
すみません、UO3 さんにしてはめずらしく、コードにコメントが無い
ので、よかったら、すこし教えてください：
>かってながらBook1,Book2は、以下のようになっているということが前提です。
>
>(Book1)
>
>７行目には、ユニークな項目名がセットされていると認識しています。
>
>(Book2)
>
>・B〜F列が抽出・印刷領域。
>・G列は空白列
>・H列、H1 に　"地区" (Book1 の D7 と同じ文字列）、
>　H2から下に、担当する地区コード（１つでも３つでも１００個でもＯＫ）
>
>また、印刷は Book2の B〜F列のみを対象にしています。

>
>Sub Sample2()
(Q5.)Book3のグループ番号をBook1のグループ番号列に（）どこでどのように
　　セットされてますか？


>　　With Workbooks("Book2.xls").Sheets(1)
>　　　　sv3 = .Range("B7:F7").Value
>　　　　.Range("I1").Value = wb.Sheets(1).Range("E7").Value
>　　　　
>　　　　For Each grp In dicG
>　　　　　　.Range("B7:F7").Value = Array(wb.Sheets(1).Range("F7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("G7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("G7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("I7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("J7").Value)
>　　　　　　.Range("H2", .Range("H" & .Rows.Count).End(xlUp)).Offset(, 1).Value = grp
(Q6.)↑このあたりが Book2 印刷用シートに対する処理のようですが、
　　トレース(Step実行)していくと、列見出しを書き換えてますでしょ？
　　で、シートレイアウトがちがうものだから、これ以降うまく実行できなく
　　なってしまい、元の列見出しに復帰できなくなっちゃったんです。
　　この辺でやっていることはどういったことなのでしょうか？


>　　　　 　sh.Range("B7", sh.Range("B" & sh.Rows.Count).End(xlUp)) _
>　　　　　　.Resize(, 9).AdvancedFilter _
>　　　　　　　　Action:=xlFilterCopy, _
>　　　　　　　　CriteriaRange:=.Range("H1").CurrentRegion, _
>　　　　　　　　CopyToRange:=.Cells(i, "B").Resize(, 5), _
>　　　　　　　　Unique:=False
>　　　　　　　　If x <> 1 Then .Rows(i).Delete
>　　　　　　　　Call maintGrp(sh, False)
(Q7.)↑Book1の（各シート？の B列から右）9列を AdvancedFilterを使って、
　　Boo2にへ抽出転記していますが、
　　抽出元Book1の 項目「申請金額」「実金額」はY列、Z列などでは
　　なかったですか？


#書いていて 少しわかってきた部分もありますが、
もしよろしければご説明願えればありがたいです。
　お手すきのときでかまいません m(_ _)m
【73375】Re:AdvancedFilterメソッドについて
質問  kanabun  - 12/12/28(金) 11:42 -
引用なし
パスワード
   あ、追加でもうひとつ、スミマセン。m(_ _)m

>Book2
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ
>空白・従業員NB・従業員名・申請日・申請金額・実金額
>＊Book2の申請日は手書きのため空白

(Q8.)Book2の印刷用シートは罫線があるんですよね？
　　AdvancedFilter で Action:=xlFilterCopy モードで転記すると、
　　書式も転記されてしまいませんか？
【73376】Re:AdvancedFilterメソッドについて
発言  UO3  - 12/12/28(金) 12:05 -
引用なし
パスワード
   ▼kanabunさん

いつもお世話様です。
とりあえず私が想定しているブック要件を。

・まず、Book1 には　同じレイアウトのシートが２枚あると想定しています。
　スレ主さんが Book1-1 と　Book1-2 という表現をしておられましたので。
　で、シート名がわからないので、 Sheets(1) と Sheets(2) として扱っています。
・でBook1の各シートは、7行目までヘッダー部分で、何かしらの項目がセットされている。
　でも、少なくとも、7行目には、説明のあった
　空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額
　こういう見出しがＡ列からＪ列にあるという前提です。（Ｋ列以降にもあるでしょうけど対象にしていません）
・で、Ｅ列のグループ番号は空白になっているんだろうと想定しています。
　ですから（スレ主さんに断わりもなく）、ここに Book3から従業員番号をキーにしてグループ番号を埋め込んで
　処理しています。で、処理後、また（こっそり）空白に戻しています。

　★この紐つけが、従業員番号でＯＫなのか、あるいは他の項目で紐つけるべきなのかは、スレ主さんに
　　確認するのを失念していました。もしかしたら違うかもしれませんね。

・Book2 も　上の７行目がヘッダー部分なんだろうと思っています。
　で、その中の７行目が
　空白・従業員NB・従業員名・申請日・申請金額・実金額
　このように Ａ列からＦ列に記載されているんだろうと思っています。
・フィルターを使いますので、ネックが
　１）Book1 のタイトルは　"従業員番号" 、一方、Book2 では "従業員NB" 。
　　　ここをスレ主さんにお願いして同じものにしてもらうのが本筋だったかもしれませんが
　　　横着に（？）このＡ〜Ｆ列の値を保存して、ここにBook1の該当の項目名をセット。
　　　（最後に復元しています）
　２）Book1 にない項目、"申請日" があります。このままではフィルタリングで失敗しますので
　　　コードでは、ここを　Book1　の G7 （従業員名）からセット。フィルタリング完了後に
　　　列データをクリアしています。

・ということで、Book1からのフィルタリングを２回行い、最初は７行目のタイトル行に対して
　２回目は、抽出最終データの下の行にタイトル行をセットして、そこから下に抽出した後で
　ダミーでセットしたタイトル行を削除しています。

・Book1 への　グループ番号のセットとリセットは
　Call maintGrp(sh, True, dicP)　と　Call maintGrp(sh, False)　で行っています。

・印刷時に、そのままBook2 を印刷しますと、私が勝手に置き換えたタイトルが７行目にある状態で
　印刷されますので、印刷前に（もうデータ抽出は終わっていますので）タイトル行を復元しています。

・なお、H列に、たとえばスレ主さんの担当地区が３つ（H2:H4) 記載されていたとすると、
　そのI列に、同じグループコードを
　.Range("H2", .Range("H" & .Rows.Count).End(xlUp)).Offset(, 1).Value = grp
　でセット。この場合、H1:I4 が検索条件欄になります。

★なお、申請金額・実金額　は　Ｙ列、Ｚ列なのかもしれません。
　ただ、私のコードでは、Ａ列から連続して説明のあった項目があると認識していますので、
　↑で書きましたように、項目は（空白も含めて）Ａ列からＪ列　としています。
【73377】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/28(金) 19:37 -
引用なし
パスワード
   ▼UO3 さん：
UO3 さん、分かりやすい解説ありがとうございます。

UO3さんの考えを参考にして、以下の (kanabun案) 考えてみました。
やはりいくつか想定があります。

使うBook は送られてきたデータブック、
印刷用Book, それと 標準モジュールに以下のマクロがあるマクロブック
の３つとします。
この3つを 変数では Book1, Book2, Book3 とします。

Book1の各シートは
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・までが 7行目にタイトルで
空白・通番・県名・地区・グループ番号・従業員番号・従業員名

　　　・Ｈ〜Ｘ・Ｙ・Ｚ 列が
　　　・過去申請金額・申請金額・実金額　← 7行目
となっているものとします。

Book3（マクロの書いてあるブック）には、
　「List」という名のシートと
　「Temp」という名のシートがあります。
　「Temp」シートは作業用一時シートで、Book1の必要な
　　　データ（既定の3地区データのみ）をここへコピーし、
　　　印刷用に加工するのに利用します。
　　　
　「List」シートには [A列]から
　　　 県名・地区・グループ名・従業員番号・従業員名
　　　が(データは2行目から)書いてあります。
　　また
　 「List」シートのG列には 抽出する「地区名」がリストされて
　　いるものとします。↓(これが CriteriaRange です)
　　　　　　　　　　　G列
　　　1　　　　　　　地区
　　　2　　　　　　　A地区
　　　3　　　　　　　B地区
　　　4　　　　　　　C地区

'---------------------------------------- 標準モジュール
Option Explicit

Private Book1 As Workbook '送られてきたデータブック
Private Book2 As Workbook '印刷シート
Private Book3 As Workbook 'グループ名一覧を含むマクロブック

Sub Try1()
　Set Book1 = Workbooks("Book1.xls")　'◆実際のBook名に変更
　Set Book2 = Workbooks("Book2.xls")　'◆実際のBook名に変更
　Set Book3 = ThisWorkbook

'まず Book1の2つのシートから 必要な3地区データのみ _
　Book3.[Temp]へ抽出転記します。
'>1. 私が処理する地区のみ（3地区）フィルタにて表示し別シートへコピペ
　Dim shtA As Worksheet
　Dim shtL As Worksheet
　Dim nSheet As Long
　Dim nRow As Long: nRow = 1
　Dim c As Range, cc As Range
　
　Set shtL = Book3.Worksheets("List") '◆実際のSheet名に変更
　Set c = shtL.Range("G1")
　Set c = Range(c, c.End(xlDown)) 'CriteriaRange(抽出したい地区名リスト)
　
　With Book3.Worksheets("Temp") '一時シートに必要データだけ転記
　　　.UsedRange.Clear
　　　
　　　For nSheet = 1 To 2
　　　　　Set shtA = Book1.Sheets(nSheet)
　　　　　'列見出しのコピー
　　　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
　　　　　.Cells(nRow, 2) = shtA.[C7].Value '県名
　　　　　.Cells(nRow, 3) = shtA.[D7].Value '地区★
　　　　　.Cells(nRow, 4) = shtA.[F7].Value '従業員番号-----+
　　　　　.Cells(nRow, 5) = shtA.[G7].Value '従業員氏名　　 |印刷項目
　　　　　.Cells(nRow, 6) = shtA.[Y7].Value '申請金額　　　 |
　　　　　.Cells(nRow, 7) = shtA.[Z7].Value '実金額　-------+
　　　　　With shtA
　　　　　　　Set cc = .Range("C7", .Cells(.Rows.Count, "C").End(xlUp))
　　　　　End With
　　　　　cc.Resize(, 24).AdvancedFilter _
　　　　　　　xlFilterCopy, _
　　　　　　　CriteriaRange:=c, _
　　　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6)
　　　　　If nSheet = 1 Then _
　　　　　　　nRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
　　　Next
　　　.Rows(nRow).Delete '2回目の列見出し行を削除
　　　
'>2.そこにBook3からグループ番号をコピペ
　　　'A列にグループ番号 埋め込み
　　　Dim i&, v, s$
　　　Dim dic As Object
　　　Set dic = CreateObject("Scripting.Dictionary")
　　　v = shtL.[A1].CurrentRegion.Value 'Group番号 ⇔ 従業員番号
　　　For i = 2 To UBound(v)
　　　　　dic(v(i, 4)) = v(i, 3)
　　　Next
　　　Set c = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
　　　v = c.Offset(, 2).Value '従業員番号データ
　　　ReDim grp(1 To UBound(v), 1 To 1)
　　　For i = 1 To UBound(v)
　　　　　If dic.Exists(v(i, 1)) Then
　　　　　　　grp(i, 1) = dic(v(i, 1))
　　　　　End If
　　　Next
　　　.[A2].Resize(UBound(v)).Value = grp
　　　
'>3.グループ番号・従業員番号順に並び変え
　　　With c.CurrentRegion
　　　　　.Sort Key1:=.Columns(1), Key2:=.Columns(4), _
　　　　　　　　Header:=xlYes
　　　End With
　　　
　　　.Columns(6).Insert '「申請日」列を挿入 空白
　　　'グループ番号1〜12まで 1番号ずつ Book2へ 値転記・印刷
　　　Set cc = c.CurrentRegion
　　　　'A:Group番号 D:従業員NB E:員名 F:申請日 G:申請金額　H:実金額
　　　For i = 1 To 12
'>4.グループ番号(i)のみ表示しBook2 [B8]へ値コピー
　　　　　cc.Columns(1).AutoFilter 1, i
　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then
　　　　　　　Book2.Sheets(1).[B8:F47].ClearContents '罫線範囲クリア
　　　　　　　Intersect(cc.Offset(1), cc.Columns("D:H")).Copy
　　　　　　　Book2.Sheets(1).[B8].PasteSpecial xlValues
'> 5.合計金額（申請金額・実金額それぞれ）を算出 (数式入力)
　　　　　　　'----------- 行が不明なので割愛 ------------
　　　　　　　'印刷します
　　　　　　　Book2.Sheets(1).PrintPreview '⇒ .PrintOutに
　　　　　End If
　　　　　cc.Columns(1).AutoFilter
　　　Next
　End With
　　　
End Sub

------------
グループ番号は何に紐づけられているか？ これは UO3 さん同様、
従業員番号がグループ化されているものと仮定しています。
なので、そうでないばあいは、コードを編集する必要があります。

できれば、ごく簡単な Book1-1 Book1-2 データで,
Step実行しながら、コードの1行が何をしているのか、ワークシート
画面の変化と見くらべながら、確認していくと理解が早まると思います。
【73378】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/28(金) 20:05 -
引用なし
パスワード
   (kanabun案) コードに 漏れがありました m(_ _)m

>　　　.Columns(6).Insert '「申請日」列を挿入 空白
>　　　'グループ番号1〜12まで 1番号ずつ Book2へ 値転記・印刷
の次に

　　　 .Cells(1, 6).Value = "申請日"

を追加してください。
【73379】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/28(金) 21:00 -
引用なし
パスワード
   ▼UO3 さん：

こんばんは。


>>Book2
>>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ
>>空白・従業員NB・従業員名・申請日・申請金額・実金額
>>
>>ここに手入力で地区を入力するということですか？
>
>はい。H1 に　"地区"、H2から下に、ニャンソさんの担当する地区コードをいくつでも。
>ここに記載された地区を抽出します。

ありがとうございます。

>いやぁ・・・
>PrintOut とすると、印刷されてしまいますね。
>まだ、テスト中ですから、やたら紙に印刷しないで　PrintPreview おして
>プレビュー画面で印刷イメージを確認してもらえればと思ったものですから。
>最終的には　PrintPrevie を PrintOut に変えてください。

そういうことですか…そうですね。
経費削減ですね。
ありがとうございます！！


>Book3.xls というブック名ならこのエラーはでません。
>どこか違っているのでしょうね。
>Book3.xlsx とか？

Book3.xlsxでやってみたのですが…

不安だったので、コピペして名前を変更しました。
【73380】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/28(金) 21:02 -
引用なし
パスワード
   ▼UO3 さん：


>もし、Book3.xls で間違いないということでしたら
>その状態で以下のコードを実行していただけませんか？
>でてくるブック名を教えてください。
>
>Sub Test()
>　　Dim wb As Workbook
>　　For Each wb In Workbooks
>　　　　MsgBox wb.Name
>　　Next
>End Sub

分かりました。
本日は出来なかったので、明日実行してみます。
【73381】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/28(金) 21:56 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>まず元データのあるBook1 ですが、同じ表構成の2シートがあるわけですか？
>>Book1-1
>>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
>>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額
>>
>>Book1-2
>>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｘ・Ｙ・Ｘ
>>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・申請金額・実金額

Book1-1が他部署より送られてきたデータになります。

元データにはグループ番号がないため、各事務所のにて挿入し作業します。

その際、自分の処理する地区のみ表示し挿入。

それを、コピーしSheet2に貼り付けます。

それがBook1-2となるのですが…伝わりますでしょうか？


>(Q1.)↑の列番号
>> Ｈ〜Ｘ・Ｙ・Ｘ
>とありますが、最後の列番号は Ｚのまちがいですか？

>>Book1-2・Book2の1〜7行目は見出し？となっており、Ｂ8から表があります。
>(Q2.)Book1の各シートの↑の列見出しは 7行目に書いてあるんですよね？

すみません。
間違えです。Ｚになります。

加えてBook1の元データですが、また変更するそうです…

明日にはサンプルデータが届くので確認後、お伝えいたします。

大変申し訳ありません。

>>Book3
>>Ａ・Ｂ・Ｃ・Ｄ・Ｅ
>>県名・地区・グループ番号・従業員番号・従業員名
>>
>>Book1は他部署より送られてきます。
>>その中にグループ番号はないので、Book3よりコピペします。
>>＊グループは12あります。
>(Q4.)グループ番号は他のどの項目に紐づけられているのでしょうか？
>　　Book1のデータの何をみると グループ番号が分かるのですか？
>　　県名から連想されるグループ番号があるのですか？
>　　それとも、地区 をみると グループ番号が分かるのですか？
>　　あるいは 従業員番号 とか 従業員名 をみると分るのですか？

今までのですと、従業員番号で行っていました。

が、変更後は変わるかもしれません。

こちらにつきましても、明日返答致します。

何度も変更となり、申し訳ありません。

よろしくお願い致します。
【73382】Re:AdvancedFilterメソッドについて
発言  ニャンソ  - 12/12/28(金) 22:00 -
引用なし
パスワード
   ▼UO3 さん：


kanabun さんへのレスにて載せましたが、Book1の元データの形式が少し変わります。

明日内容確認後お伝え致します。

お手数おかけし大変申し訳ありません。
【73383】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/29(土) 9:31 -
引用なし
パスワード
   ▼ニャンソ さん：

>間違えです。Ｚになります。

>今までのですと、従業員番号で行っていました。

了解です。

>加えてBook1の元データですが、また変更するそうです…

金額欄が Y,Z列とか、
Book2 のどの行に 印刷データを値貼り付けするのか？ とか、
そういうことは「変数」 なので、変わってもいいようにマクロの
ほうで対応しておけばいいですよ。

そういえば、送られてくるデータブックも当然 Book名が都度
変わりますね？
前回の (kanabun案) を少し変更して、Book1の名前をマクロコード
上でいちいち変更しなくてもすむように、マクロを実行するときは
送られてきたデータブックを前面にして（アクティブにして）スタート
すればすむようにしました。

> Book1-1が他部署より送られてきたデータになります。

なので、ここから マクロブックの「Temp」シートに 必要な3地区データ
を転送し、「Temp」シートにコピーされたデータについてだけグループ
番号の書き込みをすることにします。要は、並び替えのキーにグループ
番号が必要なだけなので、ってことです。

そのほかのレイアウト関係は 前回と同じ前提です。

'---------------------------------------- 標準モジュール
Option Explicit

Private Book1 As Workbook
Private Book2 As Workbook
Private Book3 As Workbook

Sub Try1()
　'◆送られてきたBookをアクティブにして実行してください
　Set Book1 = ActiveWorkbook '送られてきたデータブック
　Set Book2 = Workbooks("Book2.xls") '印刷シート
　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
　
　If Book1 Is Book2 Then
　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
　　　Exit Sub
　End If
　If Book1 Is Book3 Then
　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
　　　Exit Sub
　End If
　
　　
'>1.処理する地区のみ（3地区）フィルタにて表示し別シートへコピペ
'まず Book1の2つのシートから 必要な3地区データのみ AdvancedFilterにて _
　Book3.[Temp] へ抽出転記します｡
　
　Dim shtA As Worksheet 'Book1 のSheet1
　Dim shtP As Worksheet 'Book2の印刷用シート
　Dim shtL As Worksheet 'Book3の「List」Sheet
　Dim nSheet As Long
　Dim nRow As Long: nRow = 1
　Dim c As Range, cc As Range
　
　Set shtA = Book1.Sheets(1)
　Set shtL = Book3.Worksheets("List")
　Set c = shtL.Range("G1")
　Set c = Range(c, c.End(xlDown)) 'CriteriaRange(抽出したい地区名リスト)
　Set shtP = Book2.Sheets(1)
　
　With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
　　　.UsedRange.Clear
　　　'列見出しのコピー
　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
　　　.Cells(nRow, 2) = shtA.[C7].Value '県名
　　　.Cells(nRow, 3) = shtA.[D7].Value '地区★
　　　.Cells(nRow, 4) = shtA.[F7].Value '従業員番号-----+
　　　.Cells(nRow, 5) = shtA.[G7].Value '従業員氏名　　 |印刷項目
　　　.Cells(nRow, 6) = shtA.[Y7].Value '申請金額　　　 |
　　　.Cells(nRow, 7) = shtA.[Z7].Value '実金額　-------+
　　　With shtA
　　　　　Set cc = .Range("C7", .Cells(.Rows.Count, "C").End(xlUp))
　　　End With
　　　cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
　　　　　CriteriaRange:=c, _
　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出
　　　
'>2.そこに Book3[List] からグループ番号をコピペ
　　　'shtP A列にグループ番号 埋め込み
　　　Dim i&, v
　　　Dim dic As Object
　　　
　　　Set dic = CreateObject("Scripting.Dictionary")
　　　v = shtL.[A1].CurrentRegion.Value 'Group番号 ⇔ 従業員番号
　　　For i = 2 To UBound(v)
　　　　　dic(v(i, 4)) = v(i, 3)
　　　Next
　　　Set c = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
　　　v = c.Offset(, 2).Value '従業員番号データ
　　　ReDim grp(1 To UBound(v), 1 To 1)
　　　For i = 1 To UBound(v)
　　　　　If dic.Exists(v(i, 1)) Then
　　　　　　　grp(i, 1) = dic(v(i, 1))
　　　　　End If
　　　Next
　　　.[A2].Resize(UBound(v)).Value = grp
　　　
'>3.グループ番号・従業員番号順に並び変え
　　　With c.CurrentRegion
　　　　　.Sort Key1:=.Columns(1), Key2:=.Columns(4), _
　　　　　　　　Header:=xlYes
　　　End With
　　　
　　　.Columns(6).Insert '「申請日」列を挿入 空白
　　　Set cc = c.CurrentRegion.Resize(, 8) '8列
　　　'◆A:Group番号 D:従業員NB E:員名 F:申請日 G:申請金額　H:実金額
　　　　
' 4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
　　　For i = 1 To 12
　　　　　cc.Columns(1).AutoFilter 1, i
　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then
　　　　　　　shtP.[B8:F40].ClearContents '←表罫線範囲クリア
　　　　　　　Intersect(cc.Offset(1), cc.Columns("D:H")).Copy
　　　　　　　shtP.[B8].PasteSpecial xlValues
'>5.合計金額（申請金額・実金額それぞれ）を算出 (数式入力)
　　　　　　　'----------- 集計行が不明なので割愛 ------------
　　　　　　　'印刷します
　　　　　　　shtP.PrintPreview '⇒ 実用時には .PrintOut に変更
　　　　　End If
　　　　　cc.Columns(1).AutoFilter
　　　Next
　End With
　　　
End Sub

Book3(マクロブック)シート「Temp」
'>3.グループ番号・従業員番号順に並び変え
後の表はこんな感じになります。
-----------------------------------------------------------------
　　Ａ　　　Ｂ　　Ｃ　　　Ｄ　　　　　Ｅ　　Ｆ　　　Ｇ　　　Ｈ　　
-----------------------------------------------------------------
Group番号　県名　地区　従業員番号　　氏名　　　　申請金額　実金額
　　1　　 三重　C地区　　 005　　　　新井　　　　　　87　　　73
　　2　　 岐阜　B地区　　 015　　　　上野　　　　　　81　　　73
　　2　　 岐阜　B地区　　 017　　　　遠藤　　　　　　90　　　51
　　3　　 三重　C地区　　 020　　　　大貫　　　　　　87　　　36
　　3　　 愛知　A地区　　 024　　　　岡田　　　　　　63　　　100
　　4　　 愛知　A地区　　 030　　　　加藤　　　　　　73　　　44
　　4　　 三重　C地区　　 032　　　　神山　　　　　　91　　　32
　　5　　 愛知　A地区　　 034　　　　川島　　　　　　73　　　44
　　：　　
　　：
【73384】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/29(土) 9:40 -
引用なし
パスワード
   補足です。

>Book3(マクロブック)シート「Temp」
>'>3.グループ番号・従業員番号順に並び変え
>後の表はこんな感じになります。
>-----------------------------------------------------------------
>　　Ａ　　　Ｂ　　Ｃ　　　Ｄ　　　　　Ｅ　　Ｆ　　　Ｇ　　　Ｈ　　
>-----------------------------------------------------------------
>Group番号　県名　地区　従業員番号　　氏名　　　　申請金額　実金額
>　　1　　 三重　C地区　　 005　　　　新井　　　　　　87　　　73
>　　2　　 岐阜　B地区　　 015　　　　上野　　　　　　81　　　73
>　　2　　 岐阜　B地区　　 017　　　　遠藤　　　　　　90　　　51
>　　3　　 三重　C地区　　 020　　　　大貫　　　　　　87　　　36
>　　3　　 愛知　A地区　　 024　　　　岡田　　　　　　63　　　100
>　　4　　 愛知　A地区　　 030　　　　加藤　　　　　　73　　　44
>　　4　　 三重　C地区　　 032　　　　神山　　　　　　91　　　32
>　　5　　 愛知　A地区　　 034　　　　川島　　　　　　73　　　44
>　　：　　
>　　：

それに対し、
Book2 (印刷シート) のほうは

従業員NB　従業員名　申請日　申請金額　実金額

となって、項目名がいちぶ微妙に食い違ってますが、AdvancedFilterでは
なく、AutoFilter で 「Temp」シートのA列に フィルタをかけ手」抽出された
あるグループ番号のデータ部分だけ、D〜H列をコピペ（値貼り付け）していま
すから、項目の順番がちがっていなければ 元表と貼り付け側の タイトルが
ちがっていても問題ありません。
【73385】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/29(土) 20:11 -
引用なし
パスワード
   ▼UO3 さん：

こんばんは。

>もし、Book3.xls で間違いないということでしたら
>その状態で以下のコードを実行していただけませんか？
>でてくるブック名を教えてください。
>
>Sub Test()
>　　Dim wb As Workbook
>　　For Each wb In Workbooks
>　　　　MsgBox wb.Name
>　　Next
>End Sub

ブック名は「Book3.xlsx」で変更しています。

上記コードを実行したところやはり「Book3.xlsx」でした。
【73386】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 12/12/29(土) 21:23 -
引用なし
パスワード
   ▼kanabun さん：


こんばんは。


>金額欄が Y,Z列とか、
>Book2 のどの行に 印刷データを値貼り付けするのか？ とか、
>そういうことは「変数」 なので、変わってもいいようにマクロの
>ほうで対応しておけばいいですよ。

コード内容を理解し、自分で変更すれば実行出来るということですか？

Book1-1
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｗ・Ｘ・Ｙ・Ｚ
1.空白
2.見出し
3.空白
4〜7.空白・通番・県名・地区・責任者番号・従業員番号・従業員名・過去申請金額・・空白・申請金額・実金額
8からがデータ

Book1-2
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ〜Ｗ・Ｘ・Ｙ・Ｚ
1.空白
2.見出し
3.空白
4〜7.空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・・空白・申請金額・実金額
8からがデータ

Book2
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ・Ｉ・Ｊ・Ｋ
1.見出し
2〜5.説明文
6〜7.従業員番号・従業員名・申請日・申請金額・実金額・照合・処理日・処理した申請金額・処理した実金額・修正有無・確認日
8〜34.転記データ
35.ＡとＢ列結合で「合計」の文字
35のＤが申請金額の合計金額
35のＥが実金額の合計金額
36.空白
37〜39のＡ列Ｂ列結合.「金額差異理由
　　　　　　　　　（名前・差異理由を記入）」
37のＣ・Ｄ結合.空白
37のＥ〜Ｋ.結合・空白
38のＣ・Ｄ結合.空白
38のＥ〜Ｋ.結合・空白
39のＣ・Ｄ結合.空白
39のＥ〜Ｋ.結合・空白

Ｃ・Ｆ・Ｆには「/」が入っています。

転記する所はBook1-2
Ａ・Ｂ・Ｄ・Ｅ
従業員NB・従業員名・申請金額・実金額

Book3
Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ〜ＤＱ
地区・責任者番号・グループ番号・従業員番号・従業員名・その他データ

Book1-1・Book1-2・Book2については罫線ありです。

と変更となりました。

分かりにくく申し訳ありません。

不明な点があればおっしゃってください。


>そういえば、送られてくるデータブックも当然 Book名が都度
>変わりますね？
>前回の (kanabun案) を少し変更して、Book1の名前をマクロコード
>上でいちいち変更しなくてもすむように、マクロを実行するときは
>送られてきたデータブックを前面にして（アクティブにして）スタート
>すればすむようにしました。

変わりますね。
ありがとうございます！！

アクティブとはどう行えばよいのでしょうか？

>'---------------------------------------- 標準モジュール
>Option Explicit
>
>Private Book1 As Workbook
>Private Book2 As Workbook
>Private Book3 As Workbook
>
>Sub Try1()
>　'◆送られてきたBookをアクティブにして実行してください
>　Set Book1 = ActiveWorkbook '送られてきたデータブック
>　Set Book2 = Workbooks("Book2.xls") '印刷シート
>　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
>　
>　If Book1 Is Book2 Then
>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>　　　Exit Sub
>　End If
>　If Book1 Is Book3 Then
>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>　　　Exit Sub
>　End If

ここのコードでアクティブにされるのでしょうか？
それとも自分で行うのでしょうか？

>なので、ここから マクロブックの「Temp」シートに 必要な3地区データ
>を転送し、「Temp」シートにコピーされたデータについてだけグループ
>番号の書き込みをすることにします。要は、並び替えのキーにグループ
>番号が必要なだけなので、ってことです。

>(Q4.)グループ番号は他のどの項目に紐づけられているのでしょうか？
>　　Book1のデータの何をみると グループ番号が分かるのですか？
>　　県名から連想されるグループ番号があるのですか？
>　　それとも、地区 をみると グループ番号が分かるのですか？
>　　あるいは 従業員番号 とか 従業員名 をみると分るのですか？

紐付けですが、Book1-1に責任者番号が追加となり責任者番号・従業員番号順に並んでおります。

ですので、Book3を責任者番号・従業員番号に並び変えて転記…という流れになります。


>そのほかのレイアウト関係は 前回と同じ前提です。

---------------------------------------------------
細かく説明して頂きありがとうございます。
お忙しい中ありがとうございました。

学生以来で初心者なもので頭が混乱しております…

結果につきましては、月曜に出来れば行いたいと思います。
出来なければ休み明け（3日）以降となります。
年末年始と忙しいので出来るか不安ですが…すみません。

よろしくお願い致します。
【73387】Re:AdvancedFilterメソッドについて
発言  kanabun  - 12/12/29(土) 21:58 -
引用なし
パスワード
   ▼ニャンソ さん：こんばんは。

>>そういえば、送られてくるデータブックも当然 Book名が都度
>>変わりますね？
>>前回の (kanabun案) を少し変更して、Book1の名前をマクロコード
>>上でいちいち変更しなくてもすむように、マクロを実行するときは
>>送られてきたデータブックを前面にして（アクティブにして）スタート
>>すればすむようにしました。
>
>変わりますね。
>ありがとうございます！！
>
>アクティブとはどう行えばよいのでしょうか？

>>　'◆送られてきたBookをアクティブにして実行してください

そのBookをアクティブにするとは そのBookを（Windowを）最前面にもって
くるということです。
つまり、マクロはマクロブックに書いてあり、印刷用の Book2 も開いてい
るけれど、マクロを実行する直前には 送られてきたBook1相当をクリックして
画面の一番前にもってきてから、マクロを実行すれば Bookの名前を毎回
マクロコードで書き換えなくてよい、ということです。

Book1-1
と
Book1-2 については、先の ニャンソ さんの説明のとおりで、送られてきた
データは Book1のSheets(1) だけという理解でよろしいですね。
Book1-2 はあくまでこちらでグループ番号を補完したBook1-1 のコピーと
いう考えでよろしいのですね？

Book2の印刷レイアウトが少し変わりましたが、考え方の変更を迫るようなもの
ではないので、基本的に、

>処理手順

>1.私が処理する地区のみ（3地区）フィルタにて表示し、別シートへコピペ
>2.そこにBook3からグループ番号をコピペ
>3.グループ番号・従業員番号順に並び変え
>4.グループ番号1のみ表示しBook2へコピペ
>5.合計金額（申請金額・実金額それぞれ）を算出
>6.Book2を印刷
>7.Book2の内容を削除
>4〜7を12回同じシートに行う

の手順どおりで（列番号とか 行番号とか 範囲とか 抽出項目とかは 変わり
ますが）、Sub Try1b() で基本的には 行けると思います。
変更後のシートレイアウト用の修正コードを提示しても、考え方は
Sub Try1b() の ママ なので↓

　1. Book1-1 ⇒ AdvancedFilterで3地区のみ 別シートにコピー
　2. Book3から グループ番号を補完
　3. グループ番号・従業員番号順に並び変え
　4. グループ番号 1〜 12 別に Book2 に値コピーして 印刷

まず、Sub Try1b() が想定しているような BookとSheetのレイアウトで
実行検証できる最低限のサンプルデータをつくり、そこで、ステップ実行
なりしながら、コードの各行がしていることを理解することから始める
のが、「急がば回れ」かと思います。
【73388】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/2(水) 21:17 -
引用なし
パスワード
   ▼kanabun さん：

明けましておめでとうございます。

結果が遅くなり申し訳ありません。

>そのBookをアクティブにするとは そのBookを（Windowを）最前面にもって
>くるということです。
>つまり、マクロはマクロブックに書いてあり、印刷用の Book2 も開いてい
>るけれど、マクロを実行する直前には 送られてきたBook1相当をクリックして
>画面の一番前にもってきてから、マクロを実行すれば Bookの名前を毎回
>マクロコードで書き換えなくてよい、ということです。

ありがとうございます。

>Book1-1
>と
>Book1-2 については、先の ニャンソ さんの説明のとおりで、送られてきた
>データは Book1のSheets(1) だけという理解でよろしいですね。
>Book1-2 はあくまでこちらでグループ番号を補完したBook1-1 のコピーと
>いう考えでよろしいのですね？

そうです。
Book1-1はグループ番号がないので…


>Book2の印刷レイアウトが少し変わりましたが、考え方の変更を迫るようなもの
>ではないので…

良かったです。

>まず、Sub Try1b() が想定しているような BookとSheetのレイアウトで
>実行検証できる最低限のサンプルデータをつくり、そこで、ステップ実行
>なりしながら、コードの各行がしていることを理解することから始める
>のが、「急がば回れ」かと思います。

分かりました。
少しずつ頑張ります。


結果ですが、
Sub Try1()の

Set shtL = Book3.Worksheets("List")

と

With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記

のところが、インデックスがないと出てしまいます。

名前をコピペしたので間違いがないはずなのですが…


cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
　　　　　CriteriaRange:=c, _
　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出

またこちらでは、

「抽出した範囲にはフィールド名がないかまたは、無効なフィールド名です」
と表示されました。


今さらで申し訳ないのですが、「Workbook」・「 Worksheet」・「Sheets」・「Worksheets」の

違いは何ですか？

よろしくお願い致します。
【73389】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/2(水) 21:26 -
引用なし
パスワード
   ▼UO3 さん：

あけましておめでとうございます。


Sub Sample2()の結果です。

Book3の名前については後日実行したところ何故かエラーは出ませんでした…


>　　　　　　.Range("B7:F7").Value = Array(wb.Sheets(1).Range("F7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("G7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("G7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("I7").Value, _
>　　　　　　　　　　　　　　　　wb.Sheets(1).Range("J7").Value)
>　　　　　　.Range("H2", .Range("H" & .Rows.Count).End(xlUp)).Offset(, 1).Value = grp
>　　　　　　Intersect(.Range("B1", .UsedRange), .Columns("B:F")).Offset(7).ClearContents

一番最後の行で「結合されたセルの一部を変更できません」

とエラーが出ました。

何故でしょうか？

よろしくお願い致します。
【73390】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/3(木) 0:26 -
引用なし
パスワード
   ▼ニャンソ さん：
>▼kanabun さん：
>
>明けましておめでとうございます。
スラマッ ターフン・バルー(インドネシア語で
　　新年おめでとうございます m(_ _)m)

>>まず、Sub Try1b() が想定しているような BookとSheetのレイアウトで
>>実行検証できる最低限のサンプルデータをつくり、そこで、ステップ実行
>>なりしながら、コードの各行がしていることを理解することから始める
>>のが、「急がば回れ」かと思います。
>
>分かりました。
>少しずつ頑張ります。
>
>
>結果ですが、
>Sub Try1()の
>
>Set shtL = Book3.Worksheets("List")
>
>と
>
>With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
>
>のところが、インデックスがないと出てしまいます。
>
>名前をコピペしたので間違いがないはずなのですが…

インデックスがない、とは そういう名前のシートがない、という意味だと
思います。マクロを書いたBookに 「List」という名前のシート、「Temp」
という名前のシートが必要です。

それぞれのシート・レイアウトは以下のようなのを想定しています。
Book3 「List」シート
　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　　　　Ｇ
　　　　　　　グループ　従業員　　　　　　　　　　
県名　　地区　　番号　　番号　従業員名　　　地区
愛知　　A地区　　1　　　005　　新井　　　　A地区
岐阜　　B地区　　1　　　006　　荒川　　　　B地区
三重　　C地区　　2　　　015　　上野　　　　C地区
静岡　　D地区　　2　　　017　　遠藤　　　　
福井　　E地区　　3　　　020　　大貫　　　　
東京　　A地区　　6　　　038　　君島　　
東京　　B地区　　6　　　041　　小池　　
東京　　C地区　　6　　　050　　佐々木　　
東京　　D地区　　6　　　051　　佐藤　　
東京　　E地区　　6　　　057　　鈴木　　
東京　　F地区　　6　　　058　　須藤　　
東京　　G地区　　6　　　062　　高久　　
東京　　X地区　　6　　　065　　高橋　　


Book3 「Temp」シート Try1() 実行後
　　Ａ　　　Ｂ　　　Ｃ　　Ｄ　　　Ｅ　　　　　Ｇ　　Ｈ　
　　　　　　　　　　　　 従業員　従業員　　　申請　　実　
Group番号　県名　　地区　番号　　氏名　　　　金額　　金額
　　1　　　三重　　C地区　005　　新井　　　　87　　73
　　2　　　岐阜　　B地区　015　　上野　　　　81　　73
　　2　　　岐阜　　B地区　017　　遠藤　　　　90　　51
　　3　　　三重　　C地区　020　　大貫　　　　87　　36
　　3　　　愛知　　A地区　024　　岡田　　　　63　　100
　　4　　　愛知　　A地区　030　　加藤　　　　73　　44
　　4　　　三重　　C地区　032　　神山　　　　91　　32
　　5　　　愛知　　A地区　034　　川島　　　　73　　44


>
>cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
>　　　　　CriteriaRange:=c, _
>　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出
>
>またこちらでは、
>
>「抽出した範囲にはフィールド名がないかまたは、無効なフィールド名です」
>と表示されました。

変数 cc というのは、送られてきた Book1 の抽出範囲の[C7]からデータ最終行
までの範囲です。
cc.Resize(, 24) というのは このC列範囲を列方向に24列分拡張した表範囲
のことです。

これは

>Book1 Sheet1
>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ　　　　〜　　　　　　　Ｘ・Ｙ・Ｚ
>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・>申請金額・実金額

というレイアウトを仮定しています。
これも7行目に 上記のような項目名が（文字列は違っていてもかまいませんが、
列の役割が違っていては困ります。）書いてあり、Book1 から必要な地区名だけ
（←これは Book3の「List」シートのＧ列に書いてあります）Book3の「Temp」
シートに抽出コピーしようとしています。

>　With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
>　　　.UsedRange.Clear
>　　　'列見出しのコピー
>　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
>　　　.Cells(nRow, 2) = shtA.[C7].Value '県名
>　　　.Cells(nRow, 3) = shtA.[D7].Value '地区★
>　　　.Cells(nRow, 4) = shtA.[F7].Value '従業員番号-----+
>　　　.Cells(nRow, 5) = shtA.[G7].Value '従業員氏名　　 |印刷項目
>　　　.Cells(nRow, 6) = shtA.[Y7].Value '申請金額　　　 |
>　　　.Cells(nRow, 7) = shtA.[Z7].Value '実金額　-------+
>　　　With shtA
>　　　　　Set cc = .Range("C7", .Cells(.Rows.Count, "C").End(xlUp))
>　　　End With
>　　　cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
>　　　　　CriteriaRange:=c, _
>　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出

Book3の「Temp」シートは白紙の状態のされ（ .UsedRange.Clear ）
そこに必要な項目をBook1の7行目から転記し、
AdvancedFilter(フィルタオプションの設定)で 必要な地区名データだけを
いま「Temp」シートに転記した項目名の列だけ Book3「Temp」に抽出して
います。
AdvancedFilterでは 項目名が重要です。そして、項目名の順番は抽出先
シートで 元シートの順番と違っていても問題ありません。
●Book3「Temp」の抽出データの列並び順は 印刷時の並び順に合わせておき
ます。
もちろん、グループ番号で行方向に並び替えてから、グループ番号順に印刷
しますから、印刷に必要な項目以外に それら並び替えなど表の加工に必要
なデータも書き出しておき、

> ◆A:Group番号 D:従業員NB E:員名 F:申請日 G:申請金額　H:実金額

印刷範囲は このうちの cc.Columns("D:H") だけにしているわけです。


>
>今さらで申し訳ないのですが、「Workbook」・「 Worksheet」・「Sheets」・「Worksheets」の
>違いは何ですか？

「Workbook」はブック、他は シートです。

「Sheets」は Worksheet と Chart があります（ワークシートと グラフシート
の両方が含まれます）

「Worksheets」は 「Worksheet」（ワークシート）の集合(Collection)のこと
です。セルの集合(Collection)が Cells, Workbookの集合が Workbooks
ワークシートの集合が Worksheets です。
集合内の個々のメンバーのことをItemプロパティで指定します。
Worksheets.Item("Sheet1") といったら、
Worksheets Collection(集合) 内の 「Sheet1」という名前のシートのことです。
通常Worksheets.Item("Sheet1") は Worksheets("Sheet1") と省略して書きます。
Cells(1,1) というのも Cells(つまり ワークシートのなかのすべてのセルの集合
) の中の .Item(1, 1) 1行1列目にある単一セル[A1]のことを .Item を省略して
Cells(1,1) と書いているんです。
Book3 のすべてのワークシートの集合 Worksheets のなかに メンバー"List" や
メンバー"Temp" がいないと、「インデックスが有効範囲にありません」という
エラーが出ます。
Worksheets.Item("List") でエラーなら、そういうItem がないということです。
【73391】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/3(木) 20:39 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。

>>明けましておめでとうございます。
>スラマッ ターフン・バルー(インドネシア語で
>　　新年おめでとうございます m(_ _)m)

勉強になります！！


>インデックスがない、とは そういう名前のシートがない、という意味だと
>思います。マクロを書いたBookに 「List」という名前のシート、「Temp」
>という名前のシートが必要です。
>
>それぞれのシート・レイアウトは以下のようなのを想定しています。
>Book3 「List」シート
>　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　　　　Ｇ
>　　　　　　　グループ　従業員　　　　　　　　　　
>県名　　地区　　番号　　番号　従業員名　　　地区
>愛知　　A地区　　1　　　005　　新井　　　　A地区
>岐阜　　B地区　　1　　　006　　荒川　　　　B地区
>三重　　C地区　　2　　　015　　上野　　　　C地区
>静岡　　D地区　　2　　　017　　遠藤　　　　
>福井　　E地区　　3　　　020　　大貫　　　　
>東京　　A地区　　6　　　038　　君島　　
>東京　　B地区　　6　　　041　　小池　　
>東京　　C地区　　6　　　050　　佐々木　　
>東京　　D地区　　6　　　051　　佐藤　　
>東京　　E地区　　6　　　057　　鈴木　　
>東京　　F地区　　6　　　058　　須藤　　
>東京　　G地区　　6　　　062　　高久　　
>東京　　X地区　　6　　　065　　高橋　　
>
>
>Book3 「Temp」シート Try1() 実行後
>　　Ａ　　　Ｂ　　　Ｃ　　Ｄ　　　Ｅ　　　　　Ｇ　　Ｈ　
>　　　　　　　　　　　　 従業員　従業員　　　申請　　実　
>Group番号　県名　　地区　番号　　氏名　　　　金額　　金額
>　　1　　　三重　　C地区　005　　新井　　　　87　　73
>　　2　　　岐阜　　B地区　015　　上野　　　　81　　73
>　　2　　　岐阜　　B地区　017　　遠藤　　　　90　　51
>　　3　　　三重　　C地区　020　　大貫　　　　87　　36
>　　3　　　愛知　　A地区　024　　岡田　　　　63　　100
>　　4　　　愛知　　A地区　030　　加藤　　　　73　　44
>　　4　　　三重　　C地区　032　　神山　　　　91　　32
>　　5　　　愛知　　A地区　034　　川島　　　　73　　44


申し訳ありません。
勘違いしておりました。

Book3に「List」、Book2に「Temp」があるのかと…

Book3に「List」と「Temp」があるのですね。


>変数 cc というのは、送られてきた Book1 の抽出範囲の[C7]からデータ最終行
>までの範囲です。
>cc.Resize(, 24) というのは このC列範囲を列方向に24列分拡張した表範囲
>のことです。
>
>これは
>
>>Book1 Sheet1
>>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ　　　　〜　　　　　　　Ｘ・Ｙ・Ｚ
>>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・>申請金額・実金額
>
>というレイアウトを仮定しています。
>これも7行目に 上記のような項目名が（文字列は違っていてもかまいませんが、
>列の役割が違っていては困ります。）書いてあり、Book1 から必要な地区名だけ
>（←これは Book3の「List」シートのＧ列に書いてあります）Book3の「Temp」
>シートに抽出コピーしようとしています。


項目の列は4〜7となっているからダメということですかね？

4行目に項目名があったり、5行目にあったりだとエラーが出てしまう…？


>>　With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
>>　　　.UsedRange.Clear
>>　　　'列見出しのコピー
>>　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
>>　　　.Cells(nRow, 2) = shtA.[C7].Value '県名
>>　　　.Cells(nRow, 3) = shtA.[D7].Value '地区★
>>　　　.Cells(nRow, 4) = shtA.[F7].Value '従業員番号-----+
>>　　　.Cells(nRow, 5) = shtA.[G7].Value '従業員氏名　　 |印刷項目
>>　　　.Cells(nRow, 6) = shtA.[Y7].Value '申請金額　　　 |
>>　　　.Cells(nRow, 7) = shtA.[Z7].Value '実金額　-------+
>>　　　With shtA
>>　　　　　Set cc = .Range("C7", .Cells(.Rows.Count, "C").End(xlUp))
>>　　　End With
>>　　　cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
>>　　　　　CriteriaRange:=c, _
>>　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出
>
>Book3の「Temp」シートは白紙の状態のされ（ .UsedRange.Clear ）
>そこに必要な項目をBook1の7行目から転記し、
>AdvancedFilter(フィルタオプションの設定)で 必要な地区名データだけを
>いま「Temp」シートに転記した項目名の列だけ Book3「Temp」に抽出して
>います。
>AdvancedFilterでは 項目名が重要です。そして、項目名の順番は抽出先
>シートで 元シートの順番と違っていても問題ありません。
>●Book3「Temp」の抽出データの列並び順は 印刷時の並び順に合わせておき
>ます。
>もちろん、グループ番号で行方向に並び替えてから、グループ番号順に印刷
>しますから、印刷に必要な項目以外に それら並び替えなど表の加工に必要
>なデータも書き出しておき、
>
>> ◆A:Group番号 D:従業員NB E:員名 F:申請日 G:申請金額　H:実金額
>
>印刷範囲は このうちの cc.Columns("D:H") だけにしているわけです。

ありがとうございます。


>「Workbook」はブック、他は シートです。
>
>「Sheets」は Worksheet と Chart があります（ワークシートと グラフシート
>の両方が含まれます）
>
>「Worksheets」は 「Worksheet」（ワークシート）の集合(Collection)のこと
>です。セルの集合(Collection)が Cells, Workbookの集合が Workbooks
>ワークシートの集合が Worksheets です。
>集合内の個々のメンバーのことをItemプロパティで指定します。
>Worksheets.Item("Sheet1") といったら、
>Worksheets Collection(集合) 内の 「Sheet1」という名前のシートのことです。
>通常Worksheets.Item("Sheet1") は Worksheets("Sheet1") と省略して書きます。
>Cells(1,1) というのも Cells(つまり ワークシートのなかのすべてのセルの集合
>) の中の .Item(1, 1) 1行1列目にある単一セル[A1]のことを .Item を省略して
>Cells(1,1) と書いているんです。
>Book3 のすべてのワークシートの集合 Worksheets のなかに メンバー"List" や
>メンバー"Temp" がいないと、「インデックスが有効範囲にありません」という
>エラーが出ます。
>Worksheets.Item("List") でエラーなら、そういうItem がないということです。

とても分かりやすい説明ありがとうございます。
勉強になります。
無知ですみません。

よろしくお願い致します。
【73392】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/3(木) 23:11 -
引用なし
パスワード
   ▼ニャンソ さん：

>>それぞれのシート・レイアウトは以下のようなのを想定しています。

>>>Book1 Sheet1
>>>Ａ・Ｂ・Ｃ・Ｄ・Ｅ・Ｆ・Ｇ・Ｈ　　　　〜　　　　　　　Ｘ・Ｙ・Ｚ
>>>空白・通番・県名・地区・グループ番号・従業員番号・従業員名・過去申請金額・>申請金額・実金額
>>
>>というレイアウトを仮定しています。
>>これも7行目に 上記のような項目名が（文字列は違っていてもかまいませんが、
>>列の役割が違っていては困ります。）書いてあり、Book1 から必要な地区名だけ
>>（←これは Book3の「List」シートのＧ列に書いてあります）Book3の「Temp」
>>シートに抽出コピーしようとしています。
>
>
>項目の列は4〜7となっているからダメということですかね？
>
>4行目に項目名があったり、5行目にあったりだとエラーが出てしまう…？

そんなちゃらんぽらんはダメです。どこか1行に統一してください。

ht tp://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm

にフィルタオプションの詳しい解説とサンプル表がありますので、
ここで、
まず、手作業での操作方法と、
つぎに、マクロの書き方とを練習してください。

こういうのは、
　習うより慣れろです。
【73396】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/4(金) 20:59 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>そんなちゃらんぽらんはダメです。どこか1行に統一してください。


申し訳ありません。
4行目で統一しました。


ありがとうございます。
勉強したいと思います。

コードの結果ですが、やはり

Set shtL = Book3.Worksheets("List")


のところが、インデックスがないと出てしまいます。

何故でしょうか？


また、「送られてきたBookをアクティブにして実行」

とエラーが出ます。

送られてきたBook1で実行しているのですが…

何が間違っているのでしょうか？

申し訳ありませんが、よろしくお願い致します。
【73409】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/5(土) 19:04 -
引用なし
パスワード
   ▼ニャンソ さん：

>申し訳ありません。
>4行目で統一しました。

そしたら、
> With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
>　　　.UsedRange.Clear
>　　　'列見出しのコピー
>　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
>　　　.Cells(nRow, 2) = shtA.[C7].Value '県名
>　　　.Cells(nRow, 3) = shtA.[D7].Value '地区★
>　　　.Cells(nRow, 4) = shtA.[F7].Value '従業員番号-----+
>　　　.Cells(nRow, 5) = shtA.[G7].Value '従業員氏名　　 |印刷項目
>　　　.Cells(nRow, 6) = shtA.[Y7].Value '申請金額　　　 |
>　　　.Cells(nRow, 7) = shtA.[Z7].Value '実金額　-------+

ここの shtA (送られてきた元データのあるシート) の7行目という行番号を
shtA.[C4].Value
のように 4 に変えないといけないですね。

>コードの結果ですが、やはり
>
>Set shtL = Book3.Worksheets("List")
>
>
>のところが、インデックスがないと出てしまいます。
>
>何故でしょうか？

マクロの書いてあるブックに List という名のシートはあるんですか？
そしてそこには こんなリストがあるんですか？

マクロブックの 「List」シート

>　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　　　　Ｇ
>　　　　　　　グループ　従業員　　　　　　　　　　
>県名　　地区　　番号　　番号　従業員名　　　地区
>愛知　　A地区　　1　　　005　　新井　　　　A地区
>岐阜　　B地区　　1　　　006　　荒川　　　　B地区
>三重　　C地区　　2　　　015　　上野　　　　C地区
>静岡　　D地区　　2　　　017　　遠藤　　　　
>福井　　E地区　　3　　　020　　大貫　　　　
>東京　　A地区　　6　　　038　　君島　　
>東京　　B地区　　6　　　041　　小池　　
>東京　　C地区　　6　　　050　　佐々木　　
>東京　　D地区　　6　　　051　　佐藤　　
>東京　　E地区　　6　　　057　　鈴木　　
>東京　　F地区　　6　　　058　　須藤　　
>東京　　G地区　　6　　　062　　高久　　
>東京　　X地区　　6　　　065　　高橋　

>
>また、「送られてきたBookをアクティブにして実行」
>
>とエラーが出ます。
>
>送られてきたBook1で実行しているのですが…

>Sub Try1()
>　'◆送られてきたBookをアクティブにして実行してください
>　Set Book1 = ActiveWorkbook '送られてきたデータブック
>　Set Book2 = Workbooks("Book2.xls") '印刷シート
>　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
>　
>　If Book1 Is Book2 Then
>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>　　　Exit Sub
>　End If
>　If Book1 Is Book3 Then
>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>　　　Exit Sub
>　End If

ここのことですね？

>　'◆送られてきたBookをアクティブにして実行してください

この文章の意味を分かってもらえないと... ほかにどう表現したらいいのか...
すみません、語彙が少なくて...
要するに Book1 あたるBook を プログラム Sub Try1() を走らせる直前に
クリックして（こうすることによって、Book1 が最前面になります）
マクロスタートということなんですが。。。

あと、送られてきたBook ですが、
Sub Try1() で想定しているレイアウトになってないような気がするので、
Book3(マクロブック) の「List」シートに対応するデータ（ダミーデータ）
を自作されて、それで検証してもいいですね。
【73428】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/7(月) 21:05 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。

>> With Book3.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
>>　　　.UsedRange.Clear
>>　　　'列見出しのコピー
>>　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
>>　　　.Cells(nRow, 2) = shtA.[C7].Value '県名
>>　　　.Cells(nRow, 3) = shtA.[D7].Value '地区★
>>　　　.Cells(nRow, 4) = shtA.[F7].Value '従業員番号-----+
>>　　　.Cells(nRow, 5) = shtA.[G7].Value '従業員氏名　　 |印刷項目
>>　　　.Cells(nRow, 6) = shtA.[Y7].Value '申請金額　　　 |
>>　　　.Cells(nRow, 7) = shtA.[Z7].Value '実金額　-------+
>
>ここの shtA (送られてきた元データのあるシート) の7行目という行番号を
>shtA.[C4].Value
>のように 4 に変えないといけないですね。

分かりました。
ありがとうございます。


>マクロの書いてあるブックに List という名のシートはあるんですか？
>そしてそこには こんなリストがあるんですか？
>
>マクロブックの 「List」シート
>
>>　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　　　　Ｇ
>>　　　　　　　グループ　従業員　　　　　　　　　　
>>県名　　地区　　番号　　番号　従業員名　　　地区
>>愛知　　A地区　　1　　　005　　新井　　　　A地区
>>岐阜　　B地区　　1　　　006　　荒川　　　　B地区
>>三重　　C地区　　2　　　015　　上野　　　　C地区
>>静岡　　D地区　　2　　　017　　遠藤　　　　
>>福井　　E地区　　3　　　020　　大貫　　　　
>>東京　　A地区　　6　　　038　　君島　　
>>東京　　B地区　　6　　　041　　小池　　
>>東京　　C地区　　6　　　050　　佐々木　　
>>東京　　D地区　　6　　　051　　佐藤　　
>>東京　　E地区　　6　　　057　　鈴木　　
>>東京　　F地区　　6　　　058　　須藤　　
>>東京　　G地区　　6　　　062　　高久　　
>>東京　　X地区　　6　　　065　　高橋　

そうですね。
Book3に「List」

地区・責任者番号・グループ番号・従業員番号・従業員名・その他データ

これを

地区・責任者番号・グループ番号・従業員番号・従業員名・地区

「Temp」は空白で作成しました。


>>Sub Try1()
>>　'◆送られてきたBookをアクティブにして実行してください
>>　Set Book1 = ActiveWorkbook '送られてきたデータブック
>>　Set Book2 = Workbooks("Book2.xls") '印刷シート
>>　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
>>　
>>　If Book1 Is Book2 Then
>>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>>　　　Exit Sub
>>　End If
>>　If Book1 Is Book3 Then
>>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>>　　　Exit Sub
>>　End If
>
>ここのことですね？

そうです。

>>　'◆送られてきたBookをアクティブにして実行してください
>
>この文章の意味を分かってもらえないと... ほかにどう表現したらいいのか...
>すみません、語彙が少なくて...
>要するに Book1 あたるBook を プログラム Sub Try1() を走らせる直前に
>クリックして（こうすることによって、Book1 が最前面になります）
>マクロスタートということなんですが。。。

Book1をアクティブにするとの事でしたが、
Book3をアクティブにすると「送られてきたBookを前面にして実行」
というエラーは出ませんでした…

>あと、送られてきたBook ですが、
>Sub Try1() で想定しているレイアウトになってないような気がするので、
>Book3(マクロブック) の「List」シートに対応するデータ（ダミーデータ）
>を自作されて、それで検証してもいいですね。

地区・責任者番号・グループ番号・従業員番号・従業員名・地区

ではなく

>>　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　　　　Ｇ
>>　　　　　　　グループ　従業員　　　　　　　　　　
>>県名　　地区　　番号　　番号　従業員名　　　地区

に変更し作成してみます。

よろしくお願い致します。
【73429】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/7(月) 23:27 -
引用なし
パスワード
   ▼ニャンソ さん：こんばんは。

ちょっと、話の経緯がとぎれてしまって、
この↓ 発言に関してだけなのですが...

>>>Sub Try1()
>>>　'◆送られてきたBookをアクティブにして実行してください
>>>　Set Book1 = ActiveWorkbook '送られてきたデータブック
>>>　Set Book2 = Workbooks("Book2.xls") '印刷シート
>>>　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
>>>　
>>>　If Book1 Is Book2 Then
>>>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>>>　　　Exit Sub
>>>　End If
>>>　If Book1 Is Book3 Then
>>>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>>>　　　Exit Sub
>>>　End If
>
>Book1をアクティブにするとの事でしたが、
>Book3をアクティブにすると「送られてきたBookを前面にして実行」
>というエラーは出ませんでした…

... 不思議ですねェ ...

>>>　Set Book1 = ActiveWorkbook '送られてきたデータブック

ここで ActiveWorkbook というのは いまアクティブなBook のことです。
それを Book1 というコード上の変数にセットしています。


>>>　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)

ここで、ThisWorkbook というのは マクロが書いてあるこのBook のことです。
同時に、 ThisWorkbook は 「List」と「Temp」というシートをもっています。


>>>　If Book1 Is Book3 Then
>>>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>>>　　　Exit Sub
>>>　End If
の部分は、 Book1 (= ActiveWorkbook、最前面にあるBook) が Book3
(＝マクロブック) だったら、マクロブックが最前面になっている、という
ことなので、送られてきたBookをアクティブにして、このマクロを実行して
下さい。
ということを言っているのですが。。。


最終的な目的ですが、

送られてきた Book1 から 必要なデータを抽出して Book2 上で印刷したい

ということですよね。

Book1(送られてきたBook) から 印刷用Book2 に AdvancedFilter で印刷に
必要なデータだけ抽出転記できればいいのですが、印刷用Book2 のシートは
罫線とかフォーマットがセットしてあるので、

Book3 の「Temp」というシート上に Book1 から （AdvancedFilterを使って）
必要な情報だけ抽出コピーして、

「Temp」シートの表に Book3 の「List」シートから、Book1 にはないグルー
プ番号とかを Book1にある項目に紐づけてTenpの表を補足し、並び替え、
加工された「Temp」シートの表を ●●別にAutoFilter かけて、Book2にコピー
し、●● の種類分だけ 印刷を繰り返す。
（なぜ Autofilterかというと Book2 には 罫線とか書式設定してあるので、
Book3のTempの表に Autofilter かけて 抽出範囲を 値のみコピーするため）

と、大まかに言って、こういう処理内容でしたよね？

再度、処理内容を大雑把にまとめると、
1. Book1 から Book3のTempシートに AdvancedFilterで 必要な地区だけ
　必要な項目だけ 転記する

2. Book3のTempシート に Book3のListより グループ番号？とか、並び替えに
　必要な情報を補足してやり、

3.Boo3のTempシートを 順番に あるグループ？だけ Autofilterで値Copyして
　　印刷に回す（Book2で）

と、上記 3 Step を分けて考えてもいいと思います。

まず、Book1(送られてきたデータシート)から AdvancedFilter で Book3の
Temp シートに 抽出する(1)の部分だけでも、コードで実行できるようにして
みませんか？
【73451】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/11(金) 20:54 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。

回答が遅くなり申し訳ありません。


>>>>　Set Book1 = ActiveWorkbook '送られてきたデータブック
>
>ここで ActiveWorkbook というのは いまアクティブなBook のことです。
>それを Book1 というコード上の変数にセットしています。
>
>
>>>>　Set Book3 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
>
>ここで、ThisWorkbook というのは マクロが書いてあるこのBook のことです。
>同時に、 ThisWorkbook は 「List」と「Temp」というシートをもっています。
>
>
>>>>　If Book1 Is Book3 Then
>>>>　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
>>>>　　　Exit Sub
>>>>　End If
>の部分は、 Book1 (= ActiveWorkbook、最前面にあるBook) が Book3
>(＝マクロブック) だったら、マクロブックが最前面になっている、という
>ことなので、送られてきたBookをアクティブにして、このマクロを実行して
>下さい。
>ということを言っているのですが。。。

ええっと…もう一度コードを初めから貼りつけて実行したところ
正しく出来ました…
何故出来なかったのかは、分かりません。
申し訳ありません。

>最終的な目的ですが、
>
>送られてきた Book1 から 必要なデータを抽出して Book2 上で印刷したい
>
>ということですよね。

そうです。

>Book1(送られてきたBook) から 印刷用Book2 に AdvancedFilter で印刷に
>必要なデータだけ抽出転記できればいいのですが、印刷用Book2 のシートは
>罫線とかフォーマットがセットしてあるので、
>
>Book3 の「Temp」というシート上に Book1 から （AdvancedFilterを使って）
>必要な情報だけ抽出コピーして、
>
>「Temp」シートの表に Book3 の「List」シートから、Book1 にはないグルー
>プ番号とかを Book1にある項目に紐づけてTenpの表を補足し、並び替え、
>加工された「Temp」シートの表を ●●別にAutoFilter かけて、Book2にコピー
>し、●● の種類分だけ 印刷を繰り返す。
>（なぜ Autofilterかというと Book2 には 罫線とか書式設定してあるので、
>Book3のTempの表に Autofilter かけて 抽出範囲を 値のみコピーするため）
>
>と、大まかに言って、こういう処理内容でしたよね？

はい、間違いありません。


>再度、処理内容を大雑把にまとめると、
>1. Book1 から Book3のTempシートに AdvancedFilterで 必要な地区だけ
>　必要な項目だけ 転記する
>
>2. Book3のTempシート に Book3のListより グループ番号？とか、並び替えに
>　必要な情報を補足してやり、
>
>3.Boo3のTempシートを 順番に あるグループ？だけ Autofilterで値Copyして
>　　印刷に回す（Book2で）
>
>と、上記 3 Step を分けて考えてもいいと思います。
>
>まず、Book1(送られてきたデータシート)から AdvancedFilter で Book3の
>Temp シートに 抽出する(1)の部分だけでも、コードで実行できるようにして
>みませんか？
>

本日実行しました。
結果としましては

>1. Book1 から Book3のTempシートに AdvancedFilterで 必要な地区だけ
>　必要な項目だけ 転記する
>2. Book3のTempシート に Book3のListより グループ番号？とか、並び替えに
>　必要な情報を補足してやり、

が正しく出来ました。

Book3「Temp」に

Ａ列：グループ番号・県名・地区・従業員番号・従業員名・空白・申請金額・実金額
Ｂ列以降はデータ

がグループ1〜12まで番号順に転記されていました。

よろしくお願い致します。
【73452】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/11(金) 23:09 -
引用なし
パスワード
   ▼ニャンソ さん：こんばんは〜

>本日実行しました。
>結果としましては
>
>>1. Book1 から Book3のTempシートに AdvancedFilterで 必要な地区だけ
>>　必要な項目だけ 転記する
>>2. Book3のTempシート に Book3のListより グループ番号？とか、並び替えに
>>　必要な情報を補足してやり、
>
>が正しく出来ました。
>
>Book3「Temp」に
>
>Ａ列：グループ番号・県名・地区・従業員番号・従業員名・空白・申請金額・実金額
>Ｂ列以降はデータ
>
>がグループ1〜12まで番号順に転記されていました。

それはそれは ♪♪♪
ひとまず、よかったと申し上げます。

それで、次に、 こちらが 知りたいのは

Step 1. と Step 2. は どのようなコードを書いて成功されたのでしょうか？

ということと、

>3.Boo3のTempシートを 順番に あるグループ？だけ Autofilterで値Copyして
>　　印刷に回す（Book2で）

↑このStep がまだ 成功していないのは、何が障害になっているのでしょうか？

ということです。
よろしければ、以上2点、教えてください。
【73477】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/14(月) 20:07 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>それで、次に、 こちらが 知りたいのは
>
>Step 1. と Step 2. は どのようなコードを書いて成功されたのでしょうか？

Option Explicit

Private Book4 As Workbook
Private Book5 As Workbook
Private Book6 As Workbook

Sub Try1()
　'◆送られてきたBookをアクティブにして実行してください
　Set Book4 = ActiveWorkbook '送られてきたデータブック
　Set Book5 = Workbooks("Book5.xlsx") '印刷シート
　Set Book6 = ThisWorkbook　 'このマクロBook(Group名一覧を含む)
　
　If Book4 Is Book5 Then
　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
　　　Exit Sub
　End If
　If Book4 Is Book6 Then
　　　MsgBox "送られてきたBookを前面にして実行", vbCritical
　　　Exit Sub
　End If
　
　　
'>1.処理する地区のみ（3地区）フィルタにて表示し別シートへコピペ
'まず Book1の2つのシートから 必要な3地区データのみ AdvancedFilterにて _
　Book3.[Temp] へ抽出転記します｡
　
　Dim shtA As Worksheet 'Book1 のSheet1
　Dim shtP As Worksheet 'Book2の印刷用シート
　Dim shtL As Worksheet 'Book3の「List」Sheet
　Dim nSheet As Long
　Dim nRow As Long: nRow = 1
　Dim c As Range, cc As Range
　
　Set shtA = Book4.Sheets(1)
　Set shtL = Book6.Worksheets("List")
　Set c = shtL.Range("DT1")
　Set c = Range(c, c.End(xlDown)) 'CriteriaRange(抽出したい地区名リスト)
　Set shtP = Book5.Sheets(1)
　
　With Book6.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
　　　.UsedRange.Clear
　　　'列見出しのコピー
　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
　　　.Cells(nRow, 2) = shtA.[C4].Value '県名
　　　.Cells(nRow, 3) = shtA.[D4].Value '地区★
　　　.Cells(nRow, 4) = shtA.[F4].Value '従業員番号-----+
　　　.Cells(nRow, 5) = shtA.[G4].Value '従業員氏名　　 |印刷項目
　　　.Cells(nRow, 6) = shtA.[Y4].Value '申請金額　　　 |
　　　.Cells(nRow, 7) = shtA.[Z4].Value '実金額　-------+
　　　With shtA
　　　　　Set cc = .Range("C4", .Cells(.Rows.Count, "C").End(xlUp))
　　　End With
　　　cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
　　　　　CriteriaRange:=c, _
　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出
　　　
'>2.そこに Book3[List] からグループ番号をコピペ
　　　'shtP A列にグループ番号 埋め込み
　　　Dim i&, v
　　　Dim dic As Object
　　　
　　　Set dic = CreateObject("Scripting.Dictionary")
　　　v = shtL.[A1].CurrentRegion.Value 'Group番号 ⇔ 従業員番号
　　　For i = 2 To UBound(v)
　　　　　dic(v(i, 4)) = v(i, 3)
　　　Next
　　　Set c = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
　　　v = c.Offset(, 2).Value '従業員番号データ
　　　ReDim grp(1 To UBound(v), 1 To 1)
　　　For i = 1 To UBound(v)
　　　　　If dic.Exists(v(i, 1)) Then
　　　　　　　grp(i, 1) = dic(v(i, 1))
　　　　　End If
　　　Next
　　　.[A2].Resize(UBound(v)).Value = grp
　　　
'>3.グループ番号・従業員番号順に並び変え
　　　With c.CurrentRegion
　　　　　.Sort Key1:=.Columns(1), Key2:=.Columns(4), _
　　　　　　　　Header:=xlYes
　　　End With
　　　
　　　.Columns(6).Insert '「申請日」列を挿入 空白
　　　Set cc = c.CurrentRegion.Resize(, 8) '8列
　　　'◆A:Group番号 D:従業員NB E:員名 F:申請日 G:申請金額　H:実金額
　　　　
' 4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
　　　For i = 1 To 12
　　　　　cc.Columns(1).AutoFilter 1, i
　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then
　　　　　　　shtP.[B8:F40].ClearContents '←表罫線範囲クリア
　　　　　　　Intersect(cc.Offset(1), cc.Columns("D:H")).Copy
　　　　　　　shtP.[B8].PasteSpecial xlValues
'>5.合計金額（申請金額・実金額それぞれ）を算出 (数式入力)
　　　　　　　'----------- 集計行が不明なので割愛 ------------
　　　　　　　'印刷します
　　　　　　　shtP.PrintOut '⇒ 実用時には .PrintOut に変更
　　　　　End If
　　　　　cc.Columns(1).AutoFilter
　　　Next
　End With
　　　
End Sub

kanabunさんに考えて頂いたコードを

Book1=Book4
Book2=Book5
Book3=Book6
G1=DT1
Book4の見出し行が「4」

に変更しました。

上記コードでStep 1. と Step 2.出来ました。

>>3.Boo3のTempシートを 順番に あるグループ？だけ Autofilterで値Copyして
>>　　印刷に回す（Book2で）
>
>↑このStep がまだ 成功していないのは、何が障害になっているのでしょうか？

'>5.合計金額（申請金額・実金額それぞれ）を算出 (数式入力)
　　　　　　　'----------- 集計行が不明なので割愛 ------------
　　　　　　　'印刷します
　　　　　　　shtP.PrintPreview '⇒ 実用時には .PrintOut に変更

のところを

　　　　　　　shtP.PrintOut '⇒ 実用時には .PrintOut に変更

と変更したのですが、印刷されず…
Step 2.で実行が完了してしまいます。

特にエラー等出ないので、何故なのか良く分かりません。

よろしくお願い致します。
【73488】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/15(火) 9:39 -
引用なし
パスワード
   ▼ニャンソ さん：こんにちは〜〜

>>それで、次に、 こちらが 知りたいのは
>>
>>Step 1. と Step 2. は どのようなコードを書いて成功されたのでしょうか？

返信ありがとうございます。

コードを詳細には読んでないのと、検証用にダミーのサンプルデータを作って
ないので、コメントできませんが、

>
>Option Explicit
>
>Private Book4 As Workbook
>Private Book5 As Workbook
>Private Book6 As Workbook
>

>　With Book6.Worksheets("Temp") 'Temp Sheetに必要データだけ転記
>　　　.UsedRange.Clear
>　　　'列見出しのコピー
>　　　.Cells(nRow, 1) = "Group番号" 'あとで Book3より引用
>　　　.Cells(nRow, 2) = shtA.[C4].Value '県名
>　　　.Cells(nRow, 3) = shtA.[D4].Value '地区★
>　　　.Cells(nRow, 4) = shtA.[F4].Value '従業員番号-----+
>　　　.Cells(nRow, 5) = shtA.[G4].Value '従業員氏名　　 |印刷項目
>　　　.Cells(nRow, 6) = shtA.[Y4].Value '申請金額　　　 |
>　　　.Cells(nRow, 7) = shtA.[Z4].Value '実金額　-------+
>　　　With shtA
>　　　　　Set cc = .Range("C4", .Cells(.Rows.Count, "C").End(xlUp))
>　　　End With
>　　　cc.Resize(, 24).AdvancedFilter xlFilterCopy, _
>　　　　　CriteriaRange:=c, _
>　　　　　CopyToRange:=.Cells(nRow, 2).Resize(, 6) '別シートに抽出

>kanabunさんに考えて頂いたコードを
>
>Book1=Book4
>Book2=Book5
>Book3=Book6
>G1=DT1
>Book4の見出し行が「4」
>
>に変更しました。

のように、ご自分で変数とセル位置を入れ替えておられるのは、コードの
理解のために、とっても良い方法だと思います。


>'>5.合計金額（申請金額・実金額それぞれ）を算出 (数式入力)
>　　　　　　　'----------- 集計行が不明なので割愛 ------------
>　　　　　　　'印刷します
>　　　　　　　shtP.PrintPreview '⇒ 実用時には .PrintOut に変更
>
>のところを
>
>　　　　　　　shtP.PrintOut '⇒ 実用時には .PrintOut に変更
>
>と変更したのですが、印刷されず…
>Step 2.で実行が完了してしまいます。
>
>特にエラー等出ないので、何故なのか良く分かりません。

むむ、、、
これはですねぇ、エラーが出ないということは、
グループ番号(1〜12) で Autofilterかけて 1行も抽出されていないからじゃ
ないですか？

コードを追ってくと、

>'>3.グループ番号・従業員番号順に並び変え
>　　　With c.CurrentRegion
>　　　　　.Sort Key1:=.Columns(1), Key2:=.Columns(4), _
>　　　　　　　　Header:=xlYes
>　　　End With
　　　
>　　　.Columns(6).Insert '「申請日」列を挿入 空白
>　　　Set cc = c.CurrentRegion.Resize(, 8) '8列
>　　　'◆A:Group番号 D:従業員NB E:員名 F:申請日 G:申請金額　H:実金額
　　　　
ここまででBook6(マクロの書いてあるBook)の[Temp]シートに ↑の列番号に
必要なデータが集積されることになっています。
A列はグループ番号です。数値で 1〜12 の範囲が書き込まれているはずです。
ここまでは OK ですか？

いや、たぶん、大丈夫ではないんでしょう。
グループ番号がA列に書き込まれていなかったり、書き込まれていても 1〜12
までの「数値」でなかったりすると、

>' 4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
>　　　For i = 1 To 12
>　　　　　cc.Columns(1).AutoFilter 1, i

　　　　◆↑ ここで、A列に 1〜12 の数値でAutofilterをかけています。
これが
A列にグループ番号が書き込まれていなかったり、書き込まれていても 1〜12
までの「数値」でなかったりすると、何も抽出されず、
↓次の行のIf分で 「可視行が見出し行を加えて2行以上あれば 印刷する」

>　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then

という判断をしています。これは、もともと、いつもグループ番号1〜12 に
対応するデータがあるとは限らないので、あるグループ番号がなければ、
印刷をスキップするためです。いちども PrintPreview されないということ
は、1〜12までの番号が [Temp]シートのA列にひとつもない、ということだか
ら、ではないでしょうか？
（他にも原因は考えられるかもしれませんが、コードを読んでいて真っ先に
思いついたのは 上記のAutofilterでキーとなるA列のデータのことでした）

なお、
>　　　　　　　shtP.PrintPreview '⇒ 実用時には .PrintOut に変更
のところを .PrintOut にするのは ちゃんと動くようになって（印刷したい
内容がプレヴューできるようになって）からでいいですよ。(^^)
紙がもったいない。

マクロブックの[Temp]シートのA列に書き込むデータは
同じマクロブックの[List]シートに書いてあるグループ番号リスト(対応表)
で、グループ番号は従業員番号(？)と対応している --- こういう想定をして
いましたが、この前提は いまでも有効ですか？

そのあたり、もういちど、[Temp]シート [List]シートを見ながら、チェック
お願いします。

#（私用で、2日間、掲示板にアクセスできません m(_ _)m）
【73508】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/16(水) 21:17 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>いや、たぶん、大丈夫ではないんでしょう。
>グループ番号がA列に書き込まれていなかったり、書き込まれていても 1〜12
>までの「数値」でなかったりすると、
>
>>' 4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
>>　　　For i = 1 To 12
>>　　　　　cc.Columns(1).AutoFilter 1, i
>
>　　　　◆↑ ここで、A列に 1〜12 の数値でAutofilterをかけています。
>これが
>A列にグループ番号が書き込まれていなかったり、書き込まれていても 1〜12
>までの「数値」でなかったりすると、何も抽出されず、
>↓次の行のIf分で 「可視行が見出し行を加えて2行以上あれば 印刷する」
>
>>　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then
>
>という判断をしています。これは、もともと、いつもグループ番号1〜12 に
>対応するデータがあるとは限らないので、あるグループ番号がなければ、
>印刷をスキップするためです。いちども PrintPreview されないということ
>は、1〜12までの番号が [Temp]シートのA列にひとつもない、ということだか
>ら、ではないでしょうか？
>（他にも原因は考えられるかもしれませんが、コードを読んでいて真っ先に
>思いついたのは 上記のAutofilterでキーとなるA列のデータのことでした）

>>' 4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
>>　　　For i = 1 To 12
>>　　　　　cc.Columns(1).AutoFilter 1, i
>
>　　　　◆↑ ここで、A列に 1〜12 の数値でAutofilterをかけています。

サンプルのデータでは、12グループではなく5が抜けた11でした。
申し訳りません。
そのため
　　　For i = 1 To 12
　　　For i = 1 To 11
に変更しました。
　　
>なお、
>>　　　　　　　shtP.PrintPreview '⇒ 実用時には .PrintOut に変更
>のところを .PrintOut にするのは ちゃんと動くようになって（印刷したい
>内容がプレヴューできるようになって）からでいいですよ。(^^)
>紙がもったいない。

了解です。

>マクロブックの[Temp]シートのA列に書き込むデータは
>同じマクロブックの[List]シートに書いてあるグループ番号リスト(対応表)
>で、グループ番号は従業員番号(？)と対応している --- こういう想定をして
>いましたが、この前提は いまでも有効ですか？
>
>そのあたり、もういちど、[Temp]シート [List]シートを見ながら、チェック
>お願いします。

そうですね、グループ番号は従業員番号に対応しています。

それと…

4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
　　　For i = 1 To 12
　　　　　cc.Columns(1).AutoFilter 1, i
　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then
　　　　　　　shtP.[B8:F40].ClearContents '←表罫線範囲クリア
　　　　　　　Intersect(cc.Offset(1), cc.Columns("D:H")).Copy
　　　　　　　shtP.[B8].PasteSpecial xlValues
の所を

　　　　　　　shtP.[B8:F35].Value=""'←表罫線範囲クリア
と変更しました。
理由としましては、[B8:F40]だと

35.ＡとＢ列結合で「合計」の文字
37〜39のＡ列Ｂ列結合.「金額差異理由
　　　　　　　　　（名前・差異理由を記入）」

上記の文字が消えてしまう為です。
また、「.ClearContents」のままですと「結合されたセルの一部を変更できません」
とエラーが出てしまいました。
ネット検索すると「.Value=""」でエラーが出ないと載っていたので…
良く理由は分かりませんが（汗
何かおかしな点があればおっしゃっていただければと思います。

その結果、プレビューまで行くことができました！！

そこで質問ですが、プレビューされると印刷範囲が4枚となってしまいます。
1枚で表示させたいのですが、コードでするのはどうすれば良いでしょうか？
（ネットで調べましたが良く分からず…）
A1からK39で表示させたいです。

お忙しい中大変申し訳ありませんが、よろしくお願い致します。
【73511】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/16(水) 22:51 -
引用なし
パスワード
   ▼ニャンソ さん：

>こんばんは。

こんばんは。さっそくですが、


>>　　　　◆↑ ここで、A列に 1〜12 の数値でAutofilterをかけています。
>
>サンプルのデータでは、12グループではなく5が抜けた11でした。
>申し訳りません。
>そのため
>　　　For i = 1 To 12
>　　　For i = 1 To 11
>に変更しました。

5が抜けているから、11番まで、という理屈がわかりません。
>　　　For i = 1 To 11
ですと、12番は印刷されないですよ？

そこは、
>　　　For i = 1 To 12
のままで問題ないような気がします。


>それと…
>
> 4.グループ番号(1〜12)別に 印刷シート[B8]へ 値のみ転記・印刷する
>　　　For i = 1 To 12
>　　　　　cc.Columns(1).AutoFilter 1, i
>　　　　　If cc.Columns(1).SpecialCells(xlVisible).Count > 1 Then
>　　　　　　　shtP.[B8:F40].ClearContents '←表罫線範囲クリア

>の所を
>
>　　　　　　　shtP.[B8:F35].Value=""'←表罫線範囲クリア
>と変更しました。
>理由としましては、[B8:F40]だと
>
>35.ＡとＢ列結合で「合計」の文字
>37〜39のＡ列Ｂ列結合.「金額差異理由
>　　　　　　　　　（名前・差異理由を記入）」
>
>上記の文字が消えてしまう為です。

shtP.[B8:F40] という範囲はこちらがかってに想像した範囲なので、
そちらで 正規の範囲に直していただいて結構です。

ただ、
>「.ClearContents」のままですと「結合されたセルの一部を変更できません」
>とエラーが出てしまいました。
40行目と41行目で結合されていないなら、ClearContents で 結合セルのエラー
は出ないような気がします。

>
>その結果、プレビューまで行くことができました！！
>
そうですか？！ まずはよかったですね♪

>そこで質問ですが、プレビューされると印刷範囲が4枚となってしまいます。
>1枚で表示させたいのですが、コードでするのはどうすれば良いでしょうか？
>A1からK39で表示させたいです。

印刷シートはいつも固定された1枚なのですから、印刷プレヴュー画面で
「Page Setup」メニュ−から 「この範囲をよこ(1)ページ たて(1)ページに
印刷する」と手動で設定しておけばいいのでは？
（わざわざ それを毎回印刷する前にやる必要はないと思いますが？）
意味が違ってたら、教えてください。
【73521】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/17(木) 20:46 -
引用なし
パスワード
   ▼kanabun さん：

こんばんは。


>5が抜けているから、11番まで、という理屈がわかりません。
>>　　　For i = 1 To 11
>ですと、12番は印刷されないですよ？
>
>そこは、
>>　　　For i = 1 To 12
>のままで問題ないような気がします。

確かに12番はﾌﾟﾚﾋﾞｭｰされませんでした…
元に戻しました。
すみません。


>ただ、
>>「.ClearContents」のままですと「結合されたセルの一部を変更できません」
>>とエラーが出てしまいました。
>40行目と41行目で結合されていないなら、ClearContents で 結合セルのエラー
>は出ないような気がします。

40行目と41行目で結合はしてません。
39行以下は何も記入がないです。

>印刷シートはいつも固定された1枚なのですから、印刷プレヴュー画面で
>「Page Setup」メニュ−から 「この範囲をよこ(1)ページ たて(1)ページに
>印刷する」と手動で設定しておけばいいのでは？
>（わざわざ それを毎回印刷する前にやる必要はないと思いますが？）
>意味が違ってたら、教えてください。

あっいや、ただ開く度に変えるのが面倒だなと思ったからです。
ですので、特に重要視はしてません…

修正等行いましたが、コードは今のところ私が思っている通りに実行しています。
あとは合計のみです。

よろしくお願い致します。
【73522】Re:AdvancedFilterメソッドについて
発言  kanabun  - 13/1/17(木) 21:58 -
引用なし
パスワード
   ▼ニャンソ さん：


>修正等行いましたが、コードは今のところ私が思っている通りに実行しています。
>あとは合計のみです。
>
>よろしくお願い致します。

>> 8〜34.転記データ
>> 35.ＡとＢ列結合で「合計」の文字
>> 35 のDが申請金額の合計金額
>> 35 のEが実金額の合計金額

でしたよね？
だったら、手動で

>> 35 のDが申請金額の合計金額
>> 35 のEが実金額の合計金額
数式を書いておいて、

8〜34行目

まで（データ行だけ）クリアすることにすればいいのでは？
つまり、

　　　　　shtP.[B8:F34].ClearContents '←値のみクリア
とか、

　　　　　shtP.[B8:F34].Value = ""'←表罫線範囲クリア

とか？
【73525】Re:AdvancedFilterメソッドについて
回答  ニャンソ  - 13/1/17(木) 22:47 -
引用なし
パスワード
   ▼kanabun さん：

>>> 8〜34.転記データ
>>> 35.ＡとＢ列結合で「合計」の文字
>>> 35 のDが申請金額の合計金額
>>> 35 のEが実金額の合計金額
>
>でしたよね？
>だったら、手動で
>
>>> 35 のDが申請金額の合計金額
>>> 35 のEが実金額の合計金額
>数式を書いておいて、
>
>8〜34行目
>
>まで（データ行だけ）クリアすることにすればいいのでは？
>つまり、
>
>　　　　　shtP.[B8:F34].ClearContents '←値のみクリア
>とか、
>
>　　　　　shtP.[B8:F34].Value = ""'←表罫線範囲クリア
>
>とか？

了解しました。

お忙しい中色々ありがとうございました。

また何かありましたら、よろしくお願い致します。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1559 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free