[[20140713233635]] 『マクロのコピー範囲(任意)のしかた』(KAO) ページの最後に飛ぶ

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

 

『マクロのコピー範囲(任意)のしかた』(KAO)

お世話になります。

「マクロの記録」を行う時のコピー範囲の操作についてお尋ねします。

説明)
・ブック1とブック2をを開いた状態で、ブック2のデータを処理します
・ブック2は作業の都度、内容(対象)が変わります

作業)
・ブック1のA2-G2の内容ををブック2のA-G列にコピーしたい
・コピー範囲はブック2にあるデータ範囲までとする
 例えば、データ範囲がH2-N100の100行であればA2-G100にコピー

この時、コピー範囲の指定の方法がわかりません。

取りあえず、例えばA2-G10と適当に範囲指定して記録を終了し、
後からマクロを編集するのでも構いませんが、その記述がわかりません。

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

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 Range("A2:G2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1).Select
 とか。

 >データ範囲がH2-N100の100行であればA2-G100にコピー
 意味が分からないので、ひょっとして、
 Range("A2").CurrentRegion.Select
 のたぐい?
 BJ

BJ様、ありがとうございます。
(ちょうど説明不足を補おうとしてたところでした _ _; )

「コピー先のデータ量に応じてコピー範囲を対応させたい」
のですが、「マクロの記録」中の操作では不可能なんですね?

いただいた回答は、記録したマクロを修正する案と察しますが、
具体的に下記例のどこを変更すればいいでしょうか?
(マクロ学習中で実践がまだまだなもので...)

下記例は、BOOK 2 にA-G列を挿入し、BOOK 1 のA2-G2セルを
BOOK 2 のA2-G2にコピー、それをA3-G10までコピーする作業を
「マクロの記録」したものです。

Sub TEST()
'
' TEST Macro
' マクロ記録日 : 2014/7/14 ユーザー名 : shougi
'

'

    Windows("BOOK 2.xls").Activate		
    Columns("A:G").Select		
    Selection.Insert Shift:=xlToRight		
    Range("A2").Select		
    Windows("BOOK 1.xls").Activate		
    Range("A2:G2").Select		
    Selection.Copy		
    Windows("BOOK 2.xls").Activate		
    ActiveSheet.Paste		
    Range("A2:G2").Select		
    Application.CutCopyMode = False		
    Selection.Copy		
    Range("A3:G10").Select		
    ActiveSheet.Paste		
    Range("A2").Select		
    Windows("BOOK 1.xls").Activate		
    Range("A2").Select		
End Sub	

よろしくお願いします。

(KAO) 2014/07/14(月) 10:41


いろいろ試してみましたが、下記でうまくいくようです。
(正解かどうか心もとないですが...)

Sub TEST2()
'
' TEST2 Macro
'

    Windows("BOOK 2.xls").Activate
    Columns("A:G").Select
    Selection.Insert Shift:=xlToRight
    Range("A2").Select
    Windows("BOOK 1.xls").Activate
    Range("A2:G2").Select
    Selection.Copy
    Windows("BOOK 2.xls").Activate
    Dim 最終行 As Integer
    最終行 = Range("H65536").End(xlUp).Row
    Range("A2:G" & 最終行).Select
    ActiveSheet.Paste
    Range("A2").Select
    Windows("BOOK 1.xls").Activate
    Range("A2").Select
End Sub

ありがとうございました。(でいいのでしょうか? ・・; )
(KAO) 2014/07/14(月) 15:19


 拡張子が「xls」になっているので良いとは思いますが
 今後の事も考えると、
 >最終行 = Range("H65536").End(xlUp).Row
 この行の、セル番地を考え直しておいた方が良いかもしれません。

 BOOK2 の H65536セルをアクティブにして、Ctrl + ↑ を押してみて下さい。
 アクティブセルが、H列の入力がある最後のセルに移動すると思います。
 「Range("H65536").End(xlUp)」は、そのセルの事です。
 この後に「.Row」がついていますが、これは そのセルの行番号になります。
 「最終行 =」も合わせると、そのセルの行番号を 変数:最終行 に代入する

 と言う意味です。

 シートの行数は Rows.Count で取得できますので
 最終行 = Range("H" & Rows.Count).End(xlUp).Row
 としておけば、将来 65536以降もデータが入る様な事になった時も
 変更せずに使えると思います。

 ちなみに
 >「コピー先のデータ量に応じてコピー範囲を対応させたい」 
 >のですが、「マクロの記録」中の操作では不可能なんですね?

 貼り付ける前までに 貼り付けるべき範囲が選択出来ていたら良いですよね。
 H:N列に隙間なくデータが入っているなら
  1.H2セルをアクティブに
  2.Ctrl + Shift + →
  3.Ctrl + Shift + ↓
  4.コピー
  5.A2セルをアクティブに
  6.貼り付け
 で、不要なデータも入りますが 範囲も選択できると思います。
  
(HANA) 2014/07/14(月) 16:19

(HANA)様、ありがとうございます。

 >最終行 = Range("H" & Rows.Count).End(xlUp).Row

勉強になりました、採用させていただきます。
ご指摘の箇所は、ネットで見つけて意味もわからず
コピペして試したものです..(反省 _ _; )

「マクロ記録」中の操作についても参考になります。
「Ctrl + Shift + →」と「Ctrl + Shift + ↓」は、
空白行が含まれる場合を見越して「Ctrl + Shift + :」
でもいいんですよね?
(これも正確に違いを理解できてるかアヤシイですが)

(KAO) 2014/07/14(月) 17:21


 入力済みの列が7列しかなく、2行目から始まっているなら
 >「Ctrl + Shift + :」 
 で良いと思います。

 データを貼り付けたい範囲が選択したい!!
 の執念で考えてみると良いと思います。
 ほかにもいろいろあると思いますので。

 でも、せっかくなので 記録コードは参考程度にして
 修正して使えるようになると良いと思います。

 たとえば
    Windows("BOOK 2.xls").Activate
    Columns("A:G").Select
    Selection.Insert Shift:=xlToRight
 の3行目の Selection・・・選択範囲・・・は
 Columns("A:G") ですよね。

 すると下の2行は
    Columns("A:G").Insert Shift:=xlToRight
 の1行になります。

 記録を取った時に、アクティブになっていたシートが両方とも
 処理対象のシートだったので、コード内にシートを選択する部分がないですが
 BOOK 2.xlsの方は、別のシートがアクティブになっていることはないのでしょうか?
  
(HANA) 2014/07/14(月) 22:29

ありがとうございます。

 >記録コードは参考程度にして修正して使えるようになると
 >良いと思います(略)..

→そうなんです! めざしているのはそこなんですが、
 わたしにとってはなかなかに"険しい道"で..−−;

 >コード内にシートを選択する部分がないですがBOOK 2.xlsの方は、
 >別のシートがアクティブになっていることはないのでしょうか?

→今回の処理は、BOOK 1、BOOK 2 各1シートのみ対象です。
 ただ、続けて別の処理で各ブックの別シートへの操作はあります。
 
「アクティブ」の意味が?なもので、“的外れ”な回答ですか?
おそらくご質問の主旨が理解できておりません..お許しを _ _;

よろしくお願いします。

(KAO) 2014/07/15(火) 09:56


 たとえば、BOOK2に Sheet1 と Sheet2 が有ったとして
 今回の、「列を挿入して、A:Gに貼付」と言う作業を Sheet1 に行う必要がある。
 しかし、BOOOK2を表示させた時、Sheet2が見える状態だったら
 Sheet2に対して処理をしてしまう。

 って事ですが、意識はありますか?
 もちろん、その方が都合が良い場合もあると思いますが。。。
 今回の場合はどうなんでしょう?
  
(HANA) 2014/07/15(火) 11:04

ありがとうございます。

 >...(略)って事ですが、意識はありますか?

↑は同じブック内のシートを複数開くってことですよね?
ググッてみてそんなことができるなんて初めて知りました!(笑)

 >もちろん、その方が都合が良い場合もあると思いますが。。。
 >今回の場合はどうなんでしょう?

う〜ん、知ってしまったら今後はあり得ますね、これは。
ということは、「コード内にシートを選択する部分が」必要に
なるってことですよね?

これは「マクロ記録」を修正して..なんでしょうけど..
できる気が..が、トライしてみます。

できなければ手をあげますので^^;、よろしくお願いします。

(KAO) 2014/07/15(火) 13:11


どうやら"早合点"してたようです..(汗)
BOOK 2 を複数シート開いた状態だと「インデックス・エラー」に
なりますねぇ。
よく見ると、複数シートを開いた場合は拡張子(.xls)の後に
「:1」「:2」と添え字?が付きますが、これが原因でしょうか?

BOOK 2 の sheet2 を選択する場合は、
「Windows("BOOK 2.xls").Activate」の後に
「Sheets("Sheet2").Select」を挿入してうまくいきました。
(正解でしょうか?)

これで同ブックのシートを複数開いている場合に対処できると
思ったのですが..
やはり"自力"ではまだまだのようです..(涙)

複数シートを開いている場合の対処のしかたを教えていただけますか?

よろしくお願いします。

(KAO) 2014/07/15(火) 14:58


 >↑は同じブック内のシートを複数開くってことですよね?
 違います。

 アクティブシート ってわかりますか。。。?
 シートタブが白色になっているシートの事ですが。
  
(HANA) 2014/07/15(火) 15:11

こんにちは。

> Windows("BOOK 2.xls").Activate
実はこれがマクロの記録の弱点(のひとつ)なのです。
KAOさんはBOOK 2.xlsというブックをアクティブにしたつもり。
でもエクセル君は、BOOK 2.xlsというウィンドウがアクティブにされたと認識。

通常はどちらの書き方でも変わらないのですが、例外的に、一つのブックで複数のウィンドウが
開かれているとき(「:1」「:2」と添え字?が付いているとき)には
Windows("BOOK 2.xls").Activate はエラーになります。
そんな名前のウィンドウはないから。

ウィンドウ名が変わってもブック名は変わりませんから、
代わりに、Workbooks("BOOK 2.xls").Activate と書いておくのが無難です。

( 佳 ) 2014/07/15(火) 20:46


(佳)様
うまくいきましたっ!、ありがとうございます。

(BJ)様、(HANA)様 も貴重な時間を割いていただき
ありがとうございます。

丁寧に解説をしていただき、また一歩前に進めた気がします。

これからもよろしくお願いします。

(KAO) 2014/07/16(水) 09:22


 今回言っているのは
 「アクティブシート」
 の話だったのですが?
  
(HANA) 2014/07/16(水) 10:13

失礼しました、"早合点"の方で終わってしまいました(笑)

アクティブシート=「現在選択されているシート」 のことですね?
で、これは

 >「Windows("BOOK 2.xls").Activate」の後に 
 >「Sheets("Sheet2").Select」を挿入してうまくい...

ったつもりでいるのですが、ひょっとしてまた"勘違い"ですかね?
すごく不安になってきました..^^;
(KAO) 2014/07/16(水) 12:02


 アクティブシートがSheet2でなかった時も、Sheet2に対して処理をさせたいなら 
 > 「Windows("BOOK 2.xls").Activate」の後に 
 >「Sheets("Sheet2").Select」を挿入してうまくいきました。 
 > (正解でしょうか?) 
 これを入れて正解です。

 たた、このすぐ後ろに
 >これで同ブックのシートを複数開いている場合に対処できると 
 >思ったのですが.. 
 と続いていたので、ウィンドウを二つ開いている場合の対処の過程の話を
 されているのだと思っていました。

 済みません。

 Windows→Workbooksの変更も踏まえると
 最初の3行は
    Workbooks("BOOK 2.xls").Activate
    Sheets("Sheet2").Select
    Columns("A:G").Select
    Selection.Insert Shift:=xlToRight
 の4行になりますが、これは一行にできます。

  下の2行はすでに書いた通り
   Columns("A:G").Insert Shift:=xlToRight
  です。

 どうなるかわかりますか?
  
(HANA) 2014/07/17(木) 12:47

(HANA)様、ありがとうございます。

ざくっと思いつくのを試してみましたが、うまくいきません(涙)

やはりVBAに素養がない者がやれることは高が知れていますね..

ただ、これができないと前に進めないような予感?(笑)がしますので、
「SOS」を出すまでしばし猶予いただけませんか?

あまり「時間」を言い訳にはしたくないのですが、この連休中は身動きできなくて..

週明け、仕事中にチャレンジさせてください^^;

よろしくお願いします。_ _;
(KAO) 2014/07/19(土) 21:54


 >やはりVBAに素養がない者がやれることは高が知れていますね.. 
 VBAの素養は、やっているうちに身に付くものだと思います。
 まだやり始めたところであれば、いろいろな事がわからないのは当然のことだと思いますよ。

 じっくり考えてみて下さい。

 誰かにいきなり「A:G列を挿入してください。」って言われたら
 「どのシートの?」「どのブックの?」ってなりますよね?

 Columns("A:G").Insert が、エクセルに「A:G列を挿入してください。」という指示です。
 VBAのお約束で、「どのブックの、どのシートの」と特に書いていない場合
 「アクティブブックの、アクティブシート」とみなして処理されます。

 今回は、Workbooks("BOOK 2.xls") の Sheets("Sheet2") ですね。

 >「マクロ記録」を修正して
 の第一歩は、記録でできたコードから 「Activate」や「SelectーSelection」を
 なくす所だと思います。
 ですから、まずはそれらの文字を削除して 一行にしてみて下さい。
 その際、必要な「.」まで消してしまわない様に気を付けて下さいね。
  
(HANA) 2014/07/20(日) 00:36

(HANA)様

 Workbooks("BOOK 2.xls").Sheets("Sheet2").Columns("A:G").Insert Shift:=xlToRight

で動作しましたが、合ってますか?

"直感"でやってみた後にネットで「VBA 構文」で検索してみて
大丈夫かな? と思っているのですが...
たかが「.」、されど「.」って感じです ^^;

「マクロ記録」から"冗長"な部分を修正するクセを早く身に付けようと
思ったしだいです。

ありがとうございます。

(KAO) 2014/07/22(火) 13:45


 はい。それでOKです。

 これで
  Sub TEST2() 
1|    Windows("BOOK 2.xls").Activate
2|    Columns("A:G").Select
3|    Selection.Insert Shift:=xlToRight
4|    Range("A2").Select
5|    Windows("BOOK 1.xls").Activate
6|    Range("A2:G2").Select
7|    Selection.Copy
8|    Windows("BOOK 2.xls").Activate
9|    Dim 最終行 As Integer
10|    最終行 = Range("H65536").End(xlUp).Row
11|    Range("A2:G" & 最終行).Select
12|    ActiveSheet.Paste
13|    Range("A2").Select
14|    Windows("BOOK 1.xls").Activate
15|    Range("A2").Select
  End Sub
 のコードの1〜3の部分が1行になりました。
 4行目は A2セルを選択したかったわけではなく勝手に記録されたものなので、いりません。
 5行目は 元のブックをアクティブにするのですが、1〜3を一行にまとめたことで
      アクティブブックが変わりませんので、不要です。
 6・7行目は 単純に Select〜Selection なので、わかると思います。
 問題は、12行目に ActiveSheet.Paste となっている所です。
 11・12行目を単純に
   Workbooks("BOOK 2.xls").Sheets("Sheet2").Range("A2:G" & 最終行).Paste
 にしたのでは、エラーになってしまいます。

 たとえば、A2:G2セルをコピーして、H2:N10 に貼りつけるのは
   Range("A2:G2").Copy Range("H2:N10")
 と書けますので、これを利用して下さい。

 6・7・11・12が一行になるので、9・10は6より前に済ませておいてください。

 アクティブブック・アクティブシートを動かさなくなるので
 「どのブックのどのシート」と書かなければ マクロ釦があるシートの事になります。

 逆に、BOOK2のセルに関しては「Workbooks("BOOK 2.xls").Sheets("Sheet2").」を
 忘れず付けて下さい。

 ここまで来ると、完成に後一歩まで近づくと思います。

 途中で止まったら、どこまで出来たか教えてもらえたらと思います。
  
(HANA) 2014/07/22(火) 15:29

下記までは、動作確認できました。
Sub TEST()
'
    Workbooks("BOOK 2.xls").Sheets("Sheet2").Columns("A:G").Insert Shift:=xlToRight
    Range("A2:G2").Copy
    Workbooks("BOOK 2.xls").Activate
    Dim 最終行 As Integer
    最終行 = Range("H" & Rows.Count).End(xlUp).Row
    Sheets("Sheet2").Range("A2:G" & 最終行).Select
    ActiveSheet.Paste
    Range("A2").Select
    Windows("BOOK 1.xls").Activate
    Range("A2").Select

End Sub

これで、4,5行目(ご指摘の9,10)を2行目の前にもってくると、正常結果が得られません。
(BOOK 2のA1-G2に「BOOK 1のA2-G2」のデータが入ります)

この時点で今日はギブアップで..(笑)

明日は時間がとれなさそうですが、まだあきらめてはいません。

途中報告ということにしてください。 ^^;

(KAO) 2014/07/22(火) 19:02


 マクロの実行はどのようにやっていますか?

 VBEの今回のコードのどこかにカーソルを置いた状態で
 [F8]を押してみて下さい。
 コードを一行ずつ実行できます。

 変数の中身は、変数名にポインタを近づけると確認できます。
 また、ローカルウィンドウを表示させても確認できます。

 >これで、4,5行目(ご指摘の9,10)を2行目の前にもってくると、正常結果が得られません。 
 最終行 = Range("H" & Rows.Count).End(xlUp).Row
 のまま、移動しただけですか?
 でしたら、「アクティブブックのアクティブシート」のH列の最終行を取得してしまいますが。。。

 どのブックのどのシートの事なのか、注意してみて下さい。
  
(HANA) 2014/07/22(火) 22:23

下記で動作確認できましたが、合ってるんですかね?
Sub TEST()
'
' TEST Macro
'
    Workbooks("BOOK 2.xls").Sheets("Sheet2").Columns("A:G").Insert Shift:=xlToRight
    Dim 最終行 As Integer
    最終行 = Workbooks("BOOK 2.xls").Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Row
    Range("A2:G2").Copy Workbooks("BOOK 2.xls").Sheets("Sheet2").Range("A2:G" & 最終行)
    Range("A2").Select

End Sub

うまく表現できませんが、「マクロ記録」の"冗長"なところを削減するには、いかに BOOK 2 への"移動?"というか
BOOK 1 と BOOK 2 の"行き来"をなくすかを考えればいいのですね?
(そんな単純なものではないですか? ^^;)

なんだか、処理速度も速くなったように感じましたが、気のせいでしょうか?

まだ、手を加えるところあるでしょうか?

(KAO) 2014/07/23(水) 19:12


 >(そんな単純なものではないですか? ^^;) 
 いや、その単純な所が第一歩だと思います。
 Book間の往来だけでなく、セルの選択・画面への表示 なんかも
 処理速度に影響してきますので、なるべくなくす方向で
 考えてみると良いと思います。

 >まだ、手を加えるところあるでしょうか? 
 VBAには、「Withステートメント」と言うのがあります。
 たとえば、
  Sheet2のA1セルに、アクティブシートのB3セルの値
  Sheet2のB1セルに、アクティブシートのA1セルの値
  Sheet2のC1セルに、アクティブシートのC2セルの値
 を書き込むコードは
    Sheets("Shee2").Range("A1").Value = Range("B3").Value
    Sheets("Shee2").Range("B1").Value = Range("A1").Value
    Sheets("Shee2").Range("C1").Value = Range("C2").Value
 こんなコードになります。

 Sheets("Sheet2")が3回出てきますが、Withステートメントを使って
    With Sheets("Shee2")
        .Range("A1").Value = Range("B3").Value
        .Range("B1").Value = Range("A1").Value
        .Range("C1").Value = Range("C2").Value
    End With
 こんな風にも書くことが出来ます。

 With 〜 End With の間にある「.」で始まるものの前には
 With の後に続くものが省略されていると思って下さい。

 今回のコードでは「Workbooks("BOOK 2.xls").Sheets("Sheet2")」が数回出てきますので
 これをまとめてしまうと、コードがすっきりするのではないかと思います。

 今後、ブック名やシート名が変わった時に 一か所の変更で済む様になりますし。
  
(HANA) 2014/07/24(木) 08:22


 エクセルはかなりバカなので、Withステートメントを使う場合は
 こう。
 BJ

    With Sheets("Shee2")
        .Range("A1").Value = ActiveSheet.Range("B3").Value
        .Range("B1").Value = ActiveSheet.Range("A1").Value
        .Range("C1").Value = ActiveSheet.Range("C2").Value
    End With


ありがとうございます。 返事遅れてすみません。
下記で試しましたが合ってるんでしょうか?
Sub TEST2()

  With Workbooks("BOOK 2.xls").Sheets("Sheet2")	
    .Columns("A:G").Insert Shift:=xlToRight	
    Dim 最終行 As Integer	
    最終行 = .Range("H" & Rows.Count).End(xlUp).Row	
    Range("A2:G2").Copy .Range("A2:G" & 最終行)	
    Range("A2").Select	
  End With	

End Sub

BJ様の >エクセルはかなりバカなので..のところが、よくのみこめません。

上記では、問題ありということでしょうか?

(KAO) 2014/07/28(月) 19:08


    ActiveSheet.Range("A2:G2").Copy .Range("A2:G" & 最終行)	
    ActiveSheet.Range("A2").Select
 Withステートメントの中ではあいまいにしないで明確にしておいた方が良いよ。
 BJ

 >下記で試しましたが合ってるんでしょうか? 
 私はそれで良いと思います。
 今後、BJさんが書いておられるような不具合が出るかもしれませんので
 明確にしておいた方が良いかもしれませんが。。。

 些末事項として、
 同じ名前の変数を重複して宣言すると「もう宣言してあるから駄目だよ」と言われてしまいます。
 変数の宣言は、上の方にまとめて書かれているものが多い様に思います。

 また、最終行をH列なんて中途半端な感じの列で取得していますが
 その前に、A:G列を挿入しているからですよね?
 挿入前にチェックするなら、A列で済むと思います。

 それから、コピーに関しては 値のみの転記でよければ
        .Range("A1").Value = ActiveSheet.Range("B3").Value
 といった書き方ができます。
 セルのコピーだと、書式などいろいろな情報もくっつけて作業することになりますので
 処理スピードが変わるかもしれません。
  
(HANA) 2014/07/29(火) 10:17

BJ様、HANA様、ありがとうございます。

 >Withステートメントの中ではあいまいにしないで明確にしておいた方が良い..
⇒今後、意識していきます。
 >変数の宣言は、上の方にまとめて書かれているものが多い..
⇒わかりました。
 >また、最終行をH列なんて中途半端..挿入前にチェックするなら、A列で済む..
⇒With行のすぐ後にDimの2行をもってきてうまくいきました。
 >コピーに関しては 値のみの転記でよければ
        .Range("A1").Value = ActiveSheet.Range("B3").Value
 といった書き方ができ..処理スピードが変わるかも
⇒覚えておきます。(今回のは数式が対象です)

いやぁ〜、"手取り足取り"でマンツーマン指導をいただけて感謝感謝です。
最初の「マクロ記録」の行数が半減以下になるなんて、思いもしませんでした。
これを機会に、これまでの「マクロ記録」を見直したいと思います。

"壁"にぶち当たった時には、またよろしくお願いします。

(KAO) 2014/07/29(火) 12:28


 今さらですが。
 Withステートメントの中ではあいまいにしないで明確にしておいた方が良いよ。
 って書いたけど、なんでだったけかなぁとしばらく考えていた。
 で、いままでのトラブった時の質問やら経験を思い出していたら、
 With Sheets(xxxx)
 といった。
 With シート参照
 の場合の形の時だった。
 で、他シート参照のWithステートメント内のアクティブシート対象の場合は、
 明確にActiveSheetと付けるようになりました。

 フォームのコントロールなど他の場合はつけてないです。
 With Me.controls("TextBox" & i)
      Range("A" & i).value = i
 end with
 BJ

 ↑
 間違ってますね。
 すみません。
 Range("A" & i).value = .value
 とかでした。
 BJ 7/30 10:28


BJ様、ありがとうございます。

 >で、他シート参照のWithステートメント内のアクティブシート対象の場合は、
 >明確にActiveSheetと付けるようになりました..

これから、意識して実行するようにしようと思います..

(KAO) 2014/07/30(水) 08:51


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.