エクセルVBAで行のコピー貼り付けについて

質問

初心者、勉強中でエクセル2007です。
Ａ１行からＫ４０行までの表があります。
これを下にコピーをしながら増やしていってるのですが、マクロでしようと思い下記のとおり
考えました。
selecion.row.Offset(39, -1).Select　ここでオブジェクトが必要ですと出ます。
それからその下の？とを色々ぐぐってみますがどうしてもわかりません。
それと２００７ですので65536行ではないのですが、MaxRow = Cells(Rows.Count, 1).End(xlUp).Row
だと動かないみたいですので下記としています。
よろしくご教授お願いします。

Sub Gcopy()
MaxRow = Range("B65536").End(xlUp).Offset(-39, -1).Select　データの入ってる最終行を取得
Selecion.row.Offset(39, -1).Select　選択された行から上に３９行移動し選択
？　　　　　　　　　　　　　　　　　　　　下へ３９行まで選択
　　MaxRow = Range("B65536").End(xlUp).Offset(1, -1)　最終行を取得
ActiveSheet.Paste　貼り付け
End Sub

回答No.4

keithin · ベストアンサー

んーと？　
RowsとRowがごっちゃになっているようですが，結局ご自分でマクロを作成できたので，ご質問は解決でいいんですね。

整理:
rowsは「行単位のセル範囲」というモノ(Rangeオブジェクト)を返します。

sub macro7()
range("A3:B10").rows.select
end sub

rowsを使って「３行目から10行目までの行範囲（セル範囲）」という「モノ」が取得できたので，それをselectしたり，countで個数（この場合は「行数」）を数えたりできます。

rowは「セル.row」のようにして，指定のセルの行番号という数字（プロパティ）を返します。

sub macro8()
msgbox activecell.row
end sub

rowを使って「今のセルの行番号」を取得します。これはただの数字でしかないので，たとえばselectしたりとかは全然お門違いです。

お礼

そうです、質問は解決しました。
それで最後に２～３質問よろしいでしょうか？
それとも一旦ここはしめた方が良いでしょうか？
もしここでよろしいのでしたら

１．変数を使ってますが使ったほうがよいのでしょうか？
使わない方法があるのでしょうか？

２．
Sub macro5()
Dim lastrow As Long
lastrow = 10
lastrow = Cells(Cells.Rows.Count, 2).End(xup).Row.Copy
End Sub
no.1の解答で教えて頂いたこれ、オブジェクト定義エラーとなるのですが？

宜しくお願いします。

補足

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

回答No.3

keithin

＞cellsで調べるとCellsは（行番号,列番号)しか出てきません。
　：
＞意味を教えて頂けませんでしょうか？
sub macro1()
range("B3").select
end sub
sub macro2()
cells(4, 4).select
end sub
sub macro3()
cells(5,"E").select
end sub
などのように書いて動作を確認し，そのような書きぶりができるんだなと納得してください。

＞３８行下がるときはこういう書き方をするのですか？

違います。
sub macro4()
dim lastrow as long
lastrow = 44
range(cells(lastrow, "B"), cells(lastrow - 38, "B")).select
end sub

などのように書いて動作を確認し，どのセルをどうやって指定しているのか理解してください。

sub macro5()
dim lastrow as long
lastrow = 55
cells(lastrow - 38, "B").select
end sub
などのように書いて動作を確認し，どこが選ばれるのか見ながら「どうしてそうなるのか」理解してください。

＞結局は適材適所です。こういうことですね。

勿論その通り，当然のことですね。「ヤリタイ事」に応じたマクロの書きぶりを選択します。
エクセルには「何が出来るのか」を知っておくことで，手持ちのカードも増えていきます。

＞これはなぜ、最終行に１足すのがＡ列なんでしょうか？

それはあなたがご質問に掲示された，アナタが書いたマクロがやっていることです。

参照：
　MaxRow = Range("B65536").End(xlUp).Offset(1, -1)　最終行を取得

このマクロは間違っていますが，やりたかったことは
１．B65536セルからCtrl+↑でB列の最下端のセルにジャンプする
２．そのセルから，下に一行下がり，左に１列移動したセルを取得する
３．（間違い箇所）そのセルの行番号を取得し，maxrowに入れたかった

つまりアナタが書いたマクロでやりたかったのは，B列最終行のセルから更に１行下がった「空き行の行番号」を取得したかったのです。
従ってワタシの書いたマクロではlastrowに＋１して，一行下の行を指定しています。

お礼

お返事ありがとうございます。
１．これは最終行を４４行と変数で指定してB列の最終行から上へ３８行上がったところまでをrangeで範囲指定するっていう事ですね。理解できました。
sub macro4()
dim lastrow as long
lastrow = 44
range(cells(lastrow, "B"), cells(lastrow - 38, "B")).select
end sub

２．これは５５行を変数で指定して３８行上がったところのセルを指定ってことですね、これはつまりoffsetと同じってことですね。offsetを使うより簡単に出来るってことですね。これもがってんしました
sub macro5()
dim lastrow as long
lastrow = 55
cells(lastrow - 38, "B").select
end sub
これはrangeとcellの違いを良く認識できました

３．それでこれはB列の最終行のセルから上へ３８行上がったB列までの行のrange範囲をコピー、最終行から１行下がったA列セルにdestination貼り付けということなんですね。
いままで、VBAのサイト見てきましたが、これほどよくわかる例を挙げていただいたことはないです。
range(cells(lastrow, "B"), cells(lastrow - 38, "B")).entirerow.copy _
　　destination:=cells(lastrow + 1, "A")

４．ただこれがやはり良くわかりません
　lastrow = cells(cells.rows.count, "B").end(xlup).row
最終行に変数を代入はわかりますがb列の最終rows行のcount数を数えるですか？

５．アプリケーション定義オブジェクト定義エラーとなるのですが？
Sub macro4()
Dim lastrow As Long
lastrow = 40
lastrow = cells(cells.rows.count, 2).end(xup).row

６．つまりアナタが書いたマクロでやりたかったのは，B列最終行のセルから更に１行下がった「空き行の行番号」を取得したかったのです。
従ってワタシの書いたマクロではlastrowに＋１して，一行下の行を指定しています。

そうですね、すいません、そうでした。

７．普通はNO.2の人が考えたみたいに長くなるんでしょうが、keithinはプロなんでしょうね。
僕らが見ても簡潔にまとめられてますよね、それでちなみに後学のために伺うのですが、僕が最初に考えたのを修正すると
Sub Gcopy()
MaxRow = range("B65536").End(xlUp).Offset(-39, -1).Select
Selecion.Row.Offset(39, -1).EntireRow.Copy
MaxRow = range("B65536").End(xlUp).Offset(1, -1)
ActiveSheet.Paste 貼り付け
End Sub
感じとしてはこのようになるんでしょうけどSelecion.Row.Offset(39, -1).EntireRow.Copy
がまず違いますね、この構文を使用するとしたらどこをどうしたら動くのでしょうか？

最後に以上宜しくお願いします。

補足

keithinさん
ちなみにご覧のとおり１行～４０行目まであるＡ社の請求書と４１行目～８０行目まであるＢ社の請求書を聞いてこらせてどちらかを入力最終行の下へペーストをどうせ動かないだろうなあと思いながら見よう見まねでやってみましたらななな何と動くではありませんか、もう嬉しくて嬉しくてこれもkeithinさんのお陰です。ほんとうにここにいらしたら一杯おごってあげたいくらいです。

Sub Skcopy()
Dim lastrow As Long
Dim Skc As Integer
On Error Resume Next
Skc = InputBox("どちらをコピー？１．Ａ社 　２．Ｂ社")

If Skc = 1 Then
lastrow = Cells(Cells.Rows.Count, "b").End(xlUp).Row
Range("1:40").Copy _
Destination:=Cells(lastrow + 1, "A")

ElseIf Skc = 2 Then
lastrow = Cells(Cells.Rows.Count, "b").End(xlUp).Row
Range("41:80").Copy _
Destination:=Cells(lastrow + 1, "A")

Else
MsgBox "1か2を入力して！"

End If
End Sub

回答No.2

tom04

こんにちは！
外していたらごめんなさい。

A1～K40セル（40行目は気にせず、最終行まで）のデータを2行ずつ同じものを表示すれば良い訳ですよね？
そういうコトだとしての一例です。

やり方
（1）最終行からデータの1行下に行を挿入
（2）挿入行の1行上のA～K列をコピーとしています。

Sub test()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
Rows(i + 1).Insert
Range(Cells(i, 1), Cells(i, 11)).Copy
Cells(i + 1, 1).Select
ActiveSheet.Paste
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
End Sub

こんな感じではどうでしょうか？

※　コードの書き方は人それぞれですが、、
行の挿入・削除は最後の行から行っていかないと、元の行番号が変わってしまう。
という考え方でやっています。

参考になれば良いのですが、
的外れならごめんなさいね。m(__)m

お礼

解答大変ありがとうございます。
やってみましたら、ActiveSheet.Pasteで結合されたセルの一部を変更することは出来ません。と出ます。１７行目と１８行目を結合してるからでしょうか？
宜しくお願いします。

補足

tom04さん、セルを結合している部分があるので先の方がやっていた方法でやってみましたが
Destination:=Cells(i + 1, 1)でも
Destination:=activesheetでも駄目です。構文エラーとなります。
宜しくご指導お願いします。

Sub test()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
Rows(i + 1).Insert
range(Cells(i, 1), Cells(i, 11)).Copy
Destination:=Cells(i + 1, 1)
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
End Sub

回答No.1

keithin

（考え方の例）
何をコピーしたいのか：
　Ｂ列の最下端のセルを基点にして
　上に３９行分遡った行範囲

どこに貼り付けたいのか：
　Ｂ列の最下端のセルを基点にして
　一行下の行のＡ列のセルに

作成例：
sub macro1()
　dim lastrow as long
　lastrow = cells(cells.rows.count, "B").end(xlup).row

　range(cells(lastrow, "B"), cells(lastrow - 38, "B")).entirerow.copy _
　　destination:=cells(lastrow + 1, "A")
end sub

＃参考
こんな書き方もあります：
range("B" & lastrow).offset(-38, 0).resize(39,1).entirerow.copy _

＃参考
別にExcel2007以降でも，実際のデータが6万行も無いなら，程々に下のセルからCtrl+↑することで最下行のセルを十分検出できます。
lastrow = range("B10000").end(xlup).row
でも
lastrow = range("B65536").end(xlup).row
lastrow = cells(cells.rows.count, 2).end(xup).row
でも，実質的には関係ないということです。

お礼

解答ありがとうございます。
出来ました。しかし、いままで調べてきた方法と全然違うので”えー、こんな解答になるの？”って感じなんですが少し質問させて下さい。

やはり変数を使わないとだめなんですね、そうではないかと思ってましたが

cellsで調べるとCellsは（行番号,列番号)しか出てきません。
cells(cells.rows.count, "B")はB列を・・？
意味を教えて頂けませんでしょうか？

range(cells(lastrow, "B"), cells(lastrow - 38, "B")).entirerow.copy _
３８行下がるときはこういう書き方をするのですか？
EntireRowは行全体を選択するんですね。

それからDestinationは
ちなみに、「Destination:=の方が優れている！」と言うわけじゃないです。Destination:=の方は速いけど全ての情報をまるごとコピペしかできない、???.Copyと???.PasteSpecialの方は遅いけど書式だけコピペしたり、値だけコピペなど多機能というメリット・デメリットがあるので、結局は適材適所です。こういうことですね。

destination:=cells(lastrow + 1, "A")
これはなぜ、最終行に１足すのがＡ列なんでしょうか？

以上宜しくお願いします。