【Excel VBA】データ貼り付けの開始位置について

質問

Excel2003を使用しています。

先日、こちらでアドバイスをいただきながら、下記のようなマクロを作りました。内容はあるセルの値と同じ名前のシートへデータをコピーするというものです。

Sheet1に貼り付け元のデータが表形式であり、必要なデータのみ該当のシートへコピーします。マクロ実行後は、別の新しいデータをSheet1へコピペして、またマクロを実行するのですが、その際、データの貼り付け開始位置を前回マクロを実行して貼り付けられたデータから2行空けたいのですが、可能でしょうか？

________________________________________________________________________________________________________________________________

Sub test3()
Dim n As Long
Dim i As Long
Dim j As Long

　Worksheets("Sheet1").Activate
　　For n = 4 To Cells(Rows.Count, 2).End(xlUp).Row
　　　If Cells(n, 3).Value <> "" Then
　　　　With Worksheets(CStr(Cells(n, 3).Value))
　　　　　　i = .Cells(Rows.Count, 3).End(xlUp).Row + 1
　　　　　　Cells(n, 2).Copy .Cells(i, 2)
　　　　　　Cells(n, 7).Resize(, 2).Copy .Cells(i, 4)
　　　　　　Cells(n, 11).Copy .Cells(i, 3)
　　　　End With
　　　End If
　　　If Cells(n, 13).Value <> "" Then
　　　　With Worksheets(CStr(Cells(n, 13).Value))
　　　　　　j = .Cells(Rows.Count, 3).End(xlUp).Row + 1
　　　　　　Cells(n, 12).Copy .Cells(j, 2)
　　　　　　Cells(n, 17).Copy .Cells(j, 4)
　　　　　　Cells(n, 18).Copy .Cells(j, 6)
　　　　　　Cells(n, 11).Copy .Cells(j, 3)
　　　　End With
　　　End If
　　Next n
End Sub

回答No.2

Wendy02 · ベストアンサー

こんにちは。

この前の続きですね。その内容は、なかなか難しいです。出されたコードを見れば、ああそうか、と思うかもしれませんが、それに思いつくというのは、いろいろ手を出していないと思いつきませんね。

私も経験があるのですが、せっかく出来たのに、新たな要求で、混迷を深めてしまいます。こういうところが、マクロで一番難しいところだと思います。自分の実力とか関係なしに、ワザを要求されるわけで、これが、本当の「開発」ですね。

そういう私も、一通り出来るようになったと思って挑戦して、さっぱり分からなくなったことがあります。それに、タイムリミットがあるでしょうからね。

こういうときは、なりふり構わず、格好なんかどうでもよく、なんとかコードが通ってほしいと思うだけになります。たとえば、こんな風に、フラグを置いたらいかがですか？
試してみてください。

i > 2, j > 2 --2行が初期値だったと思いますが、違うようでしたら、変更してください。
初期値のとき　i = 2, j = 2 は、フラグだけが変更されます。

-------------------------------------------------------------
Sub test3R()
Dim n As Long
Dim i As Long
Dim j As Long
ReDim flgs(Worksheets.Count) As Boolean

　Worksheets("Sheet1").Activate
　　For n = 4 To Cells(Rows.Count, 2).End(xlUp).Row
　　　If Cells(n, 3).Value <> "" Then
　　　　With Worksheets(CStr(Cells(n, 3).Value))
　　　　　　i = .Cells(Rows.Count, 3).End(xlUp).Row + 1
　　　　　　If i > 2 And flgs(.Index - 1) = False Then
　　　　　　　 i = i + 2
　　　　　　　 flgs(.Index - 1) = True
　　　　　　ElseIf i = 2 Then
　　　　　　　 flgs(.Index - 1) = True
　　　　　　End If

　　　　　　Cells(n, 2).Copy .Cells(i, 2)
　　　　　　Cells(n, 7).Resize(, 2).Copy .Cells(i, 4)
　　　　　　Cells(n, 11).Copy .Cells(i, 3)
　　　　End With
　　　End If
　　　If Cells(n, 13).Value <> "" Then
　　　　With Worksheets(CStr(Cells(n, 13).Value))
　　　　　　j = .Cells(Rows.Count, 3).End(xlUp).Row + 1
　　　　　　If j > 2 And flgs(.Index - 1) = False Then
　　　　　　　 j = j + 2
　　　　　　　 flgs(.Index - 1) = True
　　　　　　ElseIf j = 2 Then
　　　　　　　 flgs(.Index - 1) = True
　　　　　　End If

　　　　　　Cells(n, 12).Copy .Cells(j, 2)
　　　　　　Cells(n, 17).Copy .Cells(j, 4)
　　　　　　Cells(n, 18).Copy .Cells(j, 6)
　　　　　　Cells(n, 11).Copy .Cells(j, 3)
　　　　End With
　　　End If
　　Next n
End Sub

お礼

Wendy02 さん、こんにちは。
回答ありがとうございます。

早速、教えていただいた方法で試してみたところ、うまくいきました！
質問する前に、自分なりにいろいろと試してみましたが、思うようになりませんでした(^_^;)
データ貼り付け開始位置をアクティブセルからにしたらどうか、とか、いっそのこと2行挿入してから、とか…。

ひとつ教えていただきたいのですが、コード内の“ flgs(.Index - 1) = False ”は、どういうことを表しているのでしょうか？なんとなくわかるような気もするのですが、きちんと理解しておきたいので、よろしければ教えていただけると嬉しいです。

回答No.4

imogasi

Wendy０２さんの後なので、書きにくいですが、私も＃１のご回答と同じ意見を持ちました。
幸いエクセルのシートに貼り付けたものは、保存して再度開いても消えずに残っています（当たり前）。ですから開いた状態で現状の最終行を捉え
Cells(Rows.Count, 2).End(xlUp).Rowなどで
（通常は直下から次のデータを貼り付けるのが多いですが、２行空けて（行的に＋３）そこから貼り付ければよいだけ、ではないですか。
＃１のお礼を読んでも質問する意味・必要が良くわかりません。
ーー
同じプログラムを実行するのですが、
初回のみは最終行に＋３行から開始するのは困りますね。それは、初期処理における例外として対処すればよい。こういうことは（、対処する必要があるのは）通常いつも起こることです。

お礼

>＃１のお礼を読んでも質問する意味・必要が良くわかりません。

質問をする本人にとって、意味があることなのか、必要があることなのかということであって、そのこと自体をどうこう言われるのは不愉快です。質問する前に自分なりにいろいろと試しても思うようにいかなかったから、質問しているのです。こういうことを書き込まれると、他に回答してくださっている方に対しても失礼だと思うのですが。。。

>Wendy０２さんの後なので、書きにくいですが、私も＃１のご回答と同じ意見を持ちました。

Wendy02 さんからいただいた回答とそれに対するお礼をご覧になっているのであれば、この質問が解決済みであることはおわかりになるかと思います。それに対して、追加でアドバイスをいただけるのなら、ありがたいことですが、そうではないのなら、このような書き込みは不要ではないでしょうか。
私が質問を締め切っていないのもいけなかったのかもしれませんが、私は問題解決後、1週間～10日で締め切るようにしていますので…。

質問文がわかりにくいのは申し訳ないのですが、仕事で使用しますので、全てを質問文に書いてしまうわけにもいかないのです。でも、Wendy02 さんのように、質問内容を理解してくださり、それに対する回答をしてくださる方もいらっしゃいます。
imogasi さんのような方からすれば、このような質問はくだらないのかもしれませんが、そうお思いになったのなら、そのことを回答に書くのではなく、回答自体をしなければよいだけだと思います。

これまでも、imogasi さんの書き込みに対して、何度か不愉快な思いをしてきましたので、失礼を承知で書かせていただきました。

回答No.3

Wendy02

こんばんは。

>コード内の“ flgs(.Index - 1) = False ”は、どういうことを表しているのでしょうか？

本当は、flg1, flg2 とか、シートの数だけフラグを作ってやれば分かりやすいのですが、こちらではシートの数が分からないので、配列変数にしました。

flg1 とか、ひとつ作って、ローカルウィンドウでみると、動きが分かりやすくなると思います。

マクロが実行前は、False で、マクロで実行すれば、データの貼り付け位置に関わらず、True になるようにしてあげます。行が初期行では、単に、True だけで、行は追加しません。初期行ではない場合は、True になるときだけ、+2　を足してあげます。

こういう書き方がベストかどうかは分かりませんが、1回だけの場合というようなものは、フラグを取ってあげるとよいです。なお、開けているときは、ずっとフラグがTrue なら、プロシージャレベルの変数においてあげるとよいのです。

お礼

Wendy02 さん、おはようございます。
再度の回答ありがとうございます。

>本当は、flg1, flg2 とか、シートの数だけフラグを作ってやれば分かりやすいのですが、こちらではシートの数が分からないので、配列変数にしました。

シートの数は40近くあります。シートの数が多い場合も、教えていただいたように『配列変数』にするといいのでしょうか…。どうも私は関数にしろマクロにしろ“配列”が苦手なのですが、丁寧に説明していただいたおかげで理解できました。

これで、ほぼ完成です。お世話になりました！
今年も残りわずかとなりましたが、良いお年をお迎えください(^^)

回答No.1

merlionXX

.End(xlUp).Row + 1 で、すぐ下の行に貼り付けらるのですから、+3としてみたらどうですか？

お礼

アドバイスありがとうございます。

質問文がわかりにくかったようで申し訳ないのですが、データ貼り付け開始位置のみを前回マクロを実行して貼り付けられたデータから2行空けたいのです。1回のマクロ実行で、各シートへ貼り付けられるデータは1行ではないので、+3とすると、1回のマクロ実行内で、その後に続くデータも全て2行空きで貼り付けられてしまいます。

(マクロ実行1回目)
あいうえお
かきくけこ
さしすせそ
　　　↑
　　　↓
(マクロ実行2回目)
たちつてと
なにぬねの
　　　↑
　　　↓
(マクロ実行3回目)
はひふへほ
　　　：
　　　：

という具合に、矢印の部分のみ2行空けたいのです。