[[20150820090944]]　『VBAでオートフィルターで抽出したデータのみ別シ＝x（Ja2136Ka8）　ページの最後に飛ぶ

『VBAでオートフィルターで抽出したデータのみ別シートへコピペ』（Ja2136Ka8）

おはようございます。

仕事で少し困っておりますので助けてください。

3万件ほどのデータがあり、さまざまな項目が入力されて
います。
G列の項目を2つ仮にAAとBBという値のもののみオートフィルターで
抽出して、（それだけでも5000件以上ある）
それを別のシートにコピーして貼り付けたいのですが、
可視セルのみでなく、すべてのセルがくっついて転記されて
しまいます。

これを何とか防いで可視セルのみコピーできるコードをご教示
ください。

詳細は以下の通りです。

sheet1
A B C D E F G H I J K L M
220JJ D ○○○　××　△△　☆☆☆　AA #### ### #### #### #### ###

という感じで並んでいるものを
G列のAAとBBのものをオートフィルターで抽出して
C列より右側の値をすべて
シート２のC列以降に貼り付けたいのです。

sheet2
A　　　B　　C 　D　　　E　　　F　　G　　H　　I J K L M N
B&C D

取り出すデータは日々少し変動があるので一番下の段を取得しないと
いけないので？位置の取得がむずかしいのでしょうか？
私は何も考えずにオートフィルターで抽出した状態のものを
C列以降をシート２に貼り付けたらいいと思ったのですが、
全部のデータがきてしまいました。

簡単なことなのかもしれませんが、
列とかの指定をどうしたらいいのかわからなくて
申し訳ないのですが、ご教示ください。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 どのような操作をしたんでしょうか？

 オートフィルターで抽出した後、C列～最後の列まで選んで（つまり行はすべて）Ctlr/c
 SHeet2で貼り付け先セルを選んで Ctrl/v。

 これで抽出されたものだけが、コピペされますよ？

 それと、別シートへの抜出ということなら　オートフィルターより、フィルター詳細設定（フィルターオプション）が
 より適しているかもしれませんね。

（β） 2015/08/20(木) 09:46

 右クリックやリボンからのコピーだと、フィルタのかかったとびとびのセルではなく、
 かくれているセルもすべて選択してしまいます
 なぜなら、かくれているだけでデータは存在しているからです

 1.フィルタをかけたあと、範囲を選択します
 2.リボンのホームタブの一番右、検索と選択をクリック
 3.条件を選択してジャンプ
 4.可視セルにチェックしてOK
 5.これで、見えているセルだけの選択となりますのでいつもどおりのコピペ作業をどうぞ
 6.マクロの記録をとり、整理してコードを取得

 この『条件を選択してジャンプ』は使い道が色々とありますので覚えておいて損はないです
 ただ今回の場合は、βさん回答のフィルター詳細設定のがいいですねぇ…
 私ならCtrl+cとCtrl+vでやってしまいますが…

（さいき） 2015/08/20(木) 10:13

β様・さいき様

さっそくのお返事ありがとうございます。
β様、フィルターの詳細設定、思いつかなかったです。
これも自動記録にしてしまえばよさげですね。

さいき様、条件を選択してジャンプというのは知らなかったので
驚きました。
自動記録でうまくいくと思います。

VBAというよりも、基本的なエクセルの使い方をいまいちわかって
いなかったようで、今回勉強になりました。
どうもありがとうございました。

自動記録、うまくいきました。
ふと思ったのですが、抽出データが可変である時は
これ、使えませんよね？
そういう場合はどうしたらいいのでしょうか？
最初のrange("?????")の位置を一番下から数えて
エクセルアップで取得すればよいでしょうか？

（Ja2136Ka8） 2015/08/20(木) 13:47

 1行目がタイトル行で、A列から始まったリストであれば Range("A1").CurrentRegion がリスト領域になります。

 リスト内に完全な空白行や空白列があれば具合悪いですが。
 その場合、このシートのリスト領域の外に何もなければ（摘要欄といったもの）
 ActiveSHeet.UsedRange でも領域が取得できます。

（β） 2015/08/20(木) 14:45

 もちろん、基準列があれば（たとえばA列）

 Range("A1", Range("A" & Rows.Count).End(xlUp)).Columns("A:N")　といったこともできますね。

（β） 2015/08/20(木) 14:57

β様

ありがとうございます。
それで全部選択できるのですね？
ちょっと今他の仕事をしておりまして、
すぐに試すことができないので
1時間ほどのちに試してみます。

不具合あればまた投稿させていただきますので
よろしくお願いいたします。

（Ja2136Ka8） 2015/08/20(木) 15:10

β様

先ほどのセル範囲を選択してコピーするコードを書いてみました
自動記録のものを参考にして作ったのですが、
エラー9インデックスが有効範囲にありませんと出ます。
何か間違っているのでしょうか？

Sub 転記()

    Range("C1", Range("C" & Rows.Count).End(xlUp)).Columns("C:N").Select
    Selection.Copy

    Application.CutCopyMode = False
    Selection.Copy

    Sheets("AM  DATA AA_BB(2)").Select
    Range("C1").Select
    ActiveSheet.Paste

End Sub

自動記録で作成したコードには
　　Application.CutCopyMode = False

    Selection.Copy
が何行にも渡って記入されておりましたが、
選択範囲を指定してるので不要かと思い、削除しました。
何か間違っておりますでしょうか？
ちなみに、今回自動記録で得られましたセルの上端は
C6243でした。
いつも同じ位置でしたら問題はないのですが、
どうやったらこれを可変の位置に対応できるように
変更できるのでしょうか？
お忙しいところ恐れ入りますが、
ご教示いただけたら幸いです。

（Ja2136Ka8） 2015/08/20(木) 16:38

 う～ん・・・・・

 アップしたパターンの中で、一番、「難解なもの」を選びましたね。
 この領域指定は間違っています。そのほかのコードも、いろいろ注文がありますが
 それらはさておき、エラーになるということだけについてコメントします。

 少なくとも xl2010 で、このコードを、このまま実行しても、エラーにはなりません。

 ただし、シート名が　AM  DATA AA_BB(2) というシートが存在することが前提です。

 ずいぶんと、かわったというか、指定しにくいシート名ですねぇ。

 コードの中の、このシート名は 17桁になっています。 AM の後ろに　半角スペースが 2 つありますね。
 たとえば実際のシート名が　AM　DATA AA_BB(2)　これは AM の後ろが全角スペース 1つなんですが、
 こんなシート名だと、シートがないですよ！と、インデックスエラーになります。

 領域指定の件は、まず、このシート名が解決してからコメントします。

（β） 2015/08/20(木) 17:29

β様

ああ、そのシート名は、実際のものではありません。
実際のシート名を出しますと、何かの拍子に社名等わかっては
困りますので、似たような名前を書いてみました。
実際にはAMの後に2文字入っているのですが、
それを外して書いてみました。
それとDATAの後のAA_BBダミーです。
（2）は実際のシートで失敗したときのために
バックアップをしているのです。

なので、実際のシート名には全角スペースなどはございません。
それで解決しますでしょうか？
また
範囲の指定の仕方が間違っているのですね。

そこも合わせてまたご教示頂けますようお願いいたします。

（Ja2136Ka8） 2015/08/20(木) 17:43

 >>それで解決しますでしょうか？

 いえ、解決しません。領域指定も含めてコード自体は間違っていますが、少なくともエラーになるかならないかというポイントでいえば
 実行されたコードで指定しているシート名が実際にあればエラーになりませんし、なければエラーになります。

 ちゃんと（実際の）コードで書かれたシートは存在するのにエラーになるということなら、そこを追及する必要があります。（考えられませんが）

 実行したコードに書かれたシート名と実際のシート名が違っていた　ということなら、そう報告いただければ
 前に進むことはできますが。

（β） 2015/08/20(木) 17:56

 前準備として、コピー元シートをアクティブにしておいて、以下のコードを実行してください。

 ２つの領域を表示します。このいずれかが（あるいは、いずれも）、そちらで考えている領域と同じなら
 現在のコードで使っている難しい方式ではなく、（β） 2015/08/20(木) 14:45でアップした　CurrentRegionかUsedRangeを使ってください。

 もし、このいずれもが、希望する領域ではなかった場合は、そちらのコードを正しくなおしたものを使わざるを得ないですが。

 それと、ついでに、ほんとに、指定したシートがあるのかどうかチェックしています。
 コード内の　○○○○　を、そちらのコードで使ったシート名にかえて実行してください。
 シートの有無を表示します。

 Sub Test()
    Dim sh As Worksheet
    MsgBox "CurrenRegion:" & Range("A1").CurrentRegion.Address
    MsgBox "UsedRange:" & ActiveSheet.UsedRange.Address

    On Error Resume Next
    Set sh = Sheets("○○○○")
    On Error GoTo 0

    If sh Is Nothing Then
        MsgBox "シートはないですよぉ！！"
    Else
        MsgBox "シートは、ありますねぇ・・・"
    End If

 End Sub

（β） 2015/08/20(木) 19:34

β様

すみません、お返事ありがとうございます。
帰宅しているので、いま検証できなくて申し訳ないです。
システムの関係上か、セキュリティが固く、USBで
持ち帰れないので明日朝検証してみますね。

（Ja2136Ka-8） 2015/08/20(木) 22:55

β様

おはようございます。
先ほど試してみましたが、デバック出ました。
以下の通りです。

実行時エラー1004
rangeクラスのcurrentregionプロパティを
取得できません。
と出ました。

何かが間違っているのでしょうが、原因がわかりません。
とりあえず、ご報告致しますのでよろしくお願い致します。

（Ja2136Ka8） 2015/08/21(金) 10:54

 >>先ほど試してみましたが、デバック出ました。

 （β） 2015/08/20(木) 19:34 でアップしたコードをそのまま貼り付けて実行してエラーですか？
 それは、ありえませんが？

 実際のシートレイアウトは、どのようなものなんでしょうか？

（β） 2015/08/21(金) 11:30

β様

少し時間が経ってしまって申し訳ございません
そのまま貼り付けましたが、エラーとなりました。
シートの構成が悪いのでしょうか？
実はAM　DATAのシートをコピーして２枚作って
あるのです。
そして今回はそのコピーした（2）について検索したのですが
それが悪かったのでしょうか？
同じものが２枚あるから判別できなかったのでしょうか？
オリジナルのシートに限定して（つまり不要なものについては
捨ててしまって、）再度試したほうがよろしいでしょうか？

（Ja2136Ka8） 2015/08/21(金) 14:16

 よくわかりませんねぇ。

 エラーは、MsgBox "CurrenRegion:" & Range("A1").CurrentRegion.Address　ででたんですよね？
 ここでは、どのシートということは特定していません。アクティブシートを見に行きます。
 で、それが、どんなシートであっても、

 実行時エラー1004 rangeクラスのcurrentregionプロパティを取得できません。

 というエラーにはならないはずなんですが？？

 （β） 2015/08/20(木) 19:34 のコードを「新規ブック」の標準モジュールに貼りつけて実行すると
 どうなりますか？

（β） 2015/08/21(金) 15:13

 ところで　（Ja2136Ka8） 2015/08/20(木) 16:38　で報告があったインデックスエラーですが
 Sheets("AM  DATA AA_BB(2)").Select
 エラーになった時に動かした実際のコードの、このシート名と同じ名前のシートが
 実行時に表示されていたブック内に、本当に存在するんですかと質問していますが、その回答はいかがですか？

 追加で。

 >>実際のシートレイアウトは、どのようなものなんでしょうか？

 という質問もしていますが、これにも回答をもらっていません。

（β） 2015/08/21(金) 15:18

β様

やはりコピーしたシートを捨てても同じ現象です。
実行時エラー1004
rangeクラスのcurrentregionプロパティを取得できませんとなります。
変ですね？

それと、シート構成ですが、５枚の構成になっています。
５枚のうち、３枚目に当該のAMシート、５枚目がAA_BBのシートになっております。

また、ほかのファイルにリンクしているので、そちらのファイルを先にオープンしてから
このブックを開けるというプロセスがないと計算がうまくいかないそうです。
３万件ほどのデータが入っているのでとても重くてなかなか動かないのですが、それが
何か関係あるのでしょうか？

ちょっと困ってしまいました。

（Ja2136Ka8） 2015/08/21(金) 17:16

 >>それと、シート構成ですが、５枚の構成になっています。

 そうではなく、テスト実行した時のアクティブシートのレイアウトを質問しています。

 >>.やはりコピーしたシートを捨てても同じ現象です。

 新規ブックではどうなりますか？

 >>ほかのファイルにリンクしているので、そちらのファイルを先にオープンしてから 
 >>このブックを開けるというプロセスがないと計算がうまくいかないそうです。 
 >>３万件ほどのデータが入っているのでとても重くてなかなか動かないのですが、それが 
 >>何か関係あるのでしょうか？

 具体的に、どこに、どんなリンク式が入っているのかを教えてもらわないと、一般論では
 適切なコメントができません。

 とにかく、こちらが質問したことへの回答と、お願いした処理と、その結果の連絡は、必ずお願いしますね。
 そうでなければ、前に進めませんので。

（β） 2015/08/21(金) 17:26

 ■ >３枚目に当該のAMシート
 　このシート内容すべてを新しいブックのSheet1に値貼付けし、他のブックは閉じてください

 　　※やり方※
 　　　>３枚目に当該のAMシート のセルA1の左上(行数表示1の上 かつ 列数表示Aの左)の四角を
 　　　　右クリックしてコピーし、新しいブックのSheet1のA1に値貼付け

 ■（β） 2015/08/20(木) 19:34 のコード

 Sub Test()

    MsgBox "CurrenRegion:" & Range("A1").CurrentRegion.Address
    MsgBox "UsedRange:" & ActiveSheet.UsedRange.Address

  End Sub

 これを、さきほど貼り付けたシートタブを右クリック→コードの表示で出てきたところに貼り付け、F5を押下してください

まだエラーが出ますか？

（新規ブックで実行してみて） 2015/08/21(金) 17:53

β様

おはようございます。
休日だったので、自宅ではファイルを開けれなくて失礼致しました。

おっしゃるように新しいブックに貼り付けて、上記のコードをコピペして
実行。
currentregion$A1:$o$7244
usedrange:$A1:$o$1039923

というメッセージが出ました。
これは範囲を調べているのでしょうか？
よくわかりませんが、とにかくあまりにデータが重いために
なかなかコピーして貼り付けることすらも時間がかかってしまいます。

（Ja2136Ka8） 2015/08/24(月) 09:33

 本来は　（Ja2136Ka8） 2015/08/20(木) 16:38　の、インデックスエラーの原因を突き止めなければいけないんですが
 それは、ちょっと後回しにして。

 アップされたコードでは領域指定の記述方法そのものが間違っているので、もう少し簡単な記述方法ができるかできないか
 それを確かめてもらいました。

 >>currentregion$A1:$o$7244 
 >>usedrange:$A1:$o$1039923

 （Ja2136Ka8）さんが相手に使用としているリストの領域が、どちらかと一致していますか？
 それとも、いずれとも一致しないのですか？

 (どちらかと一致しているとすれば、CurrentRegionのほうだと思いますが）

 もう１つ。7245行目は空白行なんですね。でもその下、1039923行目まで何かしら、セルが使われているんですが
 ここには何が入っているんですか？

　
（β） 2015/08/24(月) 16:41

 インデックスエラーも後回しにはせず同時並行でいきましょうかね。

 エラーがでたコードは　Sheets("AM  DATA AA_BB(2)").Select　ですね？
 （それ以外には、（Ja2136Ka8） 2015/08/20(木) 16:38 のコードでインデックスエラーがでるコードはありませんので）

 で、本当に実行したコードでは　"AM  DATA AA_BB(2)"　の部分は、違うシート名になっているけど
 そのシートは【確かに存在する】。なのになぜエラー？？　ということですね？

 実行した際に、別のブックが、同じエクセル区画で開かれていて、それが前面にでていたということはないですか？

（β） 2015/08/24(月) 17:09

 追加で。

 （β） 2015/08/21(金) 17:26 で、実行してエラーになった時の具体的なシートレイアウトを質問しています。
 これに対する回答もよろしく。

 >>よくわかりませんが、とにかくあまりにデータが重いために 
 >>なかなかコピーして貼り付けることすらも時間がかかってしまいます。

 データが膨大なので時間がかかるのはしょうがないですね。
 通常では考えにくいことが発生しているわけですから、その原因を１つずつ、つぶしていく必要があります。
 もし、↑の　（Ja2136Ka8）さんのコメントが　「クレーム」であり、「いいかげんにしてよ！」ということであれば
 βとしては、「あっというまに簡単に解決する妙薬」は持ち合わせていませんので、撤退しましょうか？

（β） 2015/08/25(火) 07:01

コメント返信：

[ 一覧(最新更新順) ]

[[20150820090944]] 『VBAでオートフィルターで抽出したデータのみ別シ＝x（Ja2136Ka8） ページの最後に飛ぶ

[[20150820090944]]　『VBAでオートフィルターで抽出したデータのみ別シ＝x（Ja2136Ka8）　ページの最後に飛ぶ