複数のシートを１つのシートにコピーしたい

質問

VBA初心者です。よろしくお願いします。
【状況】
１つのワークブックの中に『まとめ』という名前のシート、それ以外にコピーしたいシートが複数あります。
コピーしたくないシートもあります。
まとめを一旦クリアし、コピーしたいシートだけをコピーして貼り付けたいです。
まとめシートもコピーしたいシートも、データの入っているセルは"B5"から始まり、M列までの任意の行までデータが入っています。（B5：M？）

【やりたいこと】
コピーしたいシートのみ、データの入っている範囲をコピーして、まとめシートに貼り付けたい。

最終行を取得するVBAはいろいろなサイトを参考に書くことができたのですが、そこから"B5"までの範囲を指定してコピーをするという方法がいまいち分かりません。
参考にしたサイトはA1～特定の範囲のみ選択というものばかりで・・・。

稚拙ながら途中まで書いたコードです。

(1)Sub B5から最終セルの選択とコピー()

(2)Range("B65536").End(xlUp).Offset(0, 11).Select

(3)Range("B5", ※).Copy

(4)End Sub

※に(2)で取得した最終セルを代入したいのですが方法が分からず・・・。
ここでまず躓いてしまっています。

どうかお知恵を貸して下さい。よろしくお願いします。

回答No.4

ken-nosuke · ベストアンサー

Ａｎｏ．２の続きです

最終行のセルをselect出来れば、「ActiveCell.Row」でそのセルの行番号が得られます。これをCStr関数で文字列にし、「”B5:Mn”」の文字列を作れば、Range関数の引数にできます。

n = ActiveCell.Row
Range("B5:M"+CStr(n)).copy

これで、B5からMの最終行までの範囲をコピーできます。

お礼

Sub B5から最終セルの選択とコピー()

Dim n As Variant

Range("B65536").End(xlUp).Offset(0, 11).Select

n = ActiveCell.Row

Range("B5:M" + CStr(n)).Copy

End Sub

コピーをするところまではできましたので、ここからまた自力で頑張ってみます！
他のご回答頂きました皆さまも、ありがとうございました。

補足

ありがとうございます！理解できました。

回答No.5

tom04

No.1です。
補足に
＞まとめシートのB4～M4まで項目行が入っていますので、貼り付け先としてはB5~M5以下の行となります。

とありましたので、「まとめ」SheetのB5セル以降に貼り付けるようにしてみました。

Sub コピー2()
Dim k As Long, endRow As Long, wS As Worksheet
Set wS = Worksheets("まとめ")
endRow = wS.Cells(Rows.Count, "B").End(xlUp).Row
If endRow > 4 Then
Range(wS.Cells(5, "B"), wS.Cells(endRow, "M")).ClearContents
End If
For k = 1 To Worksheets.Count
If Worksheets(k).Name <> "まとめ" Then
endRow = Worksheets(k).Cells(Rows.Count, "B").End(xlUp).Row
If endRow > 4 Then
Range(Worksheets(k).Cells(5, "B"), Worksheets(k).Cells(endRow, "M")).Copy _
wS.Cells(Rows.Count, "B").End(xlUp).Offset(1)
End If
End If
Next k
End Sub

こんなんではどうでしょうか？m（_ _）m

お礼

既に〆てしまっていたのに、ご回答頂きありがたい限りです。
試しに頂いたマクロを試しに走らせてみたところ、ぴったりコピペできていました。
あとは特定のシートだけコピーしないようにできれば完璧です！！
がんばって完成させます。
本当にありがとうございました。

回答No.3

keithin

まとめシートの４行目に項目名が羅列済みである所から開始する

sub macro1()
　dim a as variant, ax as variant
　dim w as worksheet
　dim LastRow as long

’コピーしたいシート，したくないシートの具体的な説明がありませんので
　a = array("Sheet1", "Sheet3", "Sheet5")　’コピーしたいシート名一覧
　worksheets("まとめ").select
　range("B5:B" & range("B65536").end(xlup).row).entirerow.delete

　for each ax in a
　　lastrow = worksheets(ax).range("B65536").end(xlup).row
　　worksheets(ax).range("B5:M" & lastrow).copy destination:=range("B65536").end(xlup).offset(1)
　next
end sub

お礼

ありがとうございます。
思っていたマクロに一番近かったので、参考に求めているマクロが完成させられるよう頑張ります。

補足

ご回答ありがとうございます。
シート名をコピーしたいものに変更・追加して書いて頂いたマクロを走らせてみたのですが
まとめシートではなく、別のシート（コピーしたいシート）が消えてそこに全てコピペされていました。

回答No.2

ken-nosuke

質問内容を下記と解釈し回答します。
１）コピーしたい最終行はわかっている
２）コピーする範囲の指定　Rang("B5：Mｎ”）の　ｎの指定の仕方がわからない

回答

ｎ＝最終行とすると

Rang(”B5:M"＋CSｔｒ（n））

ｎ＝１０００　とすると上記式は、Rang(”B5:M1000"）と同じになります。
ＣＳｔｒは、数字を文字列に変換する関数です。

お礼

続きまで教えて頂いてありがとうございます！

補足

ご回答ありがとうございます。
最終行の在り処は
Range("B65536").End(xlUp).Offset(0, 11).Select
で選択することはできます。
Selectしたものを定義？格納？しなければいけないと思うのですが、その方法が分からない状態です。

Range("B5", 選択したセル).Copy

にするのはどうすれば良いでしょうか。
ＣＳｔｒ＝数字を文字列に変換というのがよくわかりません・・・。

回答No.1

tom04

こんにちは！
「まとめ」Sheetのどこに貼りつけるか？の指定がないので
A列に貼り付けとしてみました。

Alt＋F11キー　→　めにゅー　→　挿入　→　標準モジュール　に下のコードをコピー＆ペーストして
マクロを実行してみてください。

Sub まとめSheetにコピー()
Dim k As Long, endRow As Long, wS As Worksheet
Set wS = Worksheets("まとめ")
wS.Cells.ClearContents
For k = 1 To Worksheets.Count
If Worksheets(k).Name <> "まとめ" Then
endRow = Worksheets(k).Cells(Rows.Count, "B").End(xlUp).Row
Range(Worksheets(k).Cells(5, "B"), Worksheets(k).Cells(endRow, "M")).Copy _
wS.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
Next k
End Sub

※　「まとめ」Sheetに項目行があるのが一般的だと思いますが、
それも一旦クリアにして貼り付けていますので、2行目以降にデータが貼り付きます。m（_ _）m

お礼

ご回答ありがとうございました。
少しずつ解析しながら完成させていきたいと思います！

補足

情報が不足しており申し訳ありません。
まとめシートのB4～M4まで項目行が入っていますので、貼り付け先としてはB5~M5以下の行となります。