エクセルマクロで上書きして貼り付けの方法は

質問

エクセルのマクロでコピーして貼り付けする場合、貼り付けするセルに何か入力されているとその次の行に飛んで貼り付けしてしまいます。

何か入力されていても上書きで貼り付けする方法を教えてください。次のマクロを参考にお願いします。
Sub 全体名簿へ2組()
'
' 全体名簿へ2組 Macro
'

'
開始 = Range("r3").Value
終了 = Range("s3").Value

Range("B" & 開始 + 1 & ":n" & 終了 + 1).Select

Selection.Copy
Windows("全体名簿.xlsm").Activate
Sheets("★名簿").Select
Range("B65536").End(xlUp).Offset(1).Select

ActiveSheet.Paste
End Sub
このActiveSheet.Pasteを変えるとできるのかその方法がわかりません。

また，貼り付けする前に選んだセル以降の行をクリアーにしてから、貼り付けすればできますがその範囲の指定の仕方はどうすればいいのでしょうか。マクロの文も教えていただきたいです。

回答No.4

xls88 · ベストアンサー

まだ開いているのでもう一例載せます。

Dim rngp As Range
Dim 開始
Dim 終了

With Workbooks("コピー元ブック.xlsm").Sheets("コピー元シート")
開始 = .Range("r3").value
終了 = .Range("s3").value
End With

Workbooks("全体名簿.xlsm").Activate
Sheets("★名簿").Select
Set rngp = Application.InputBox _
("貼付け先セルを選択！", "貼付け先", Type:=8)
Range(rngp, "N" & Range("B" & Rows.Count).End(xlUp).Row).ClearContents

With Workbooks("コピー元ブック.xlsm").Sheets("コピー元シート")
.Range("B" & 開始 + 1 & ":H" & 終了 + 1).Copy rngp
End With

補足

またまたありがとうございます。Set rngp = Application.InputBox _
("貼付け先セルを選択！", "貼付け先", Type:=8)の文は大変参考になりました。またまた質問ですが次の文では現在正常に動きます。Set rngp = Application.InputBox _
("貼付け先セルを選択！", "貼付け先", Type:=8)をActiveSheet.Pasteの前に入れるとActiveSheet.Pasteのエラーがでてしまいます。
Sub 全体名簿へ2組()
'
' 全体名簿へ2組 Macro
'

'
開始 = Range("r3").Value
終了 = Range("s3").Value

Range("B" & 開始 + 1 & ":n" & 終了 + 1).Select

Selection.Copy
Windows("全体名簿.xlsm").Activate
Sheets("★生徒名簿").Select
Range("B65536").End(xlUp).Offset(1).Select
If MsgBox("Ｂ列で前のクラスの生徒の次のセルを選択していますか？選択するセルの前が空欄の場合いはそのセルにスペースを入力してください。", vbYesNo) = vbYes Then

ActiveSheet.Paste
Else: Worksheets("★生徒名簿").Activate

End If

End Sub
対処方法ありますか。

回答No.5

xls88

≫回答番号：No.4 この回答への補足

＞ActiveSheet.Pasteのエラーがでてしまいます。
回答番号：No.3と同じ内容です。
Excelのコピーモードが終了しています。
クリップボードの貼り付けるべきデータが失われるので貼り付けが失敗します。
先にも書きましたが、現状、試されたコードをそのまま提示して頂くと、良いのですが、、、

＞対処方法ありますか。
1
CopyメソッドでDestinationを使う事です。

2
Activate とか Select を出来るだけしないことです。
その為にはブック名、シート名を省略せずに明記しなければなりません。

Range("B65536").End(xlUp).Offset(1)
上記のような書き方は、Rangeの上位オブジェクトである、ブック名、シート名が省略されています。
この行を実行時に、アクティブワークブックのアクティブシートという意味になります。

≪余談≫
.Offset(1)が復活するのですか？
.ClearContentsは要らないのですか？
因みに、.ClearContentsしなくても.Pasteは可能だと思います。

同じような内容をたくさん投稿されていますが混乱されていませんか？
他投稿の内容を混ぜ込まないようにしてください。
まずは、本質問の解決を目指し、疑問を解いていってください。
回答番号：No.3で、出来たという報告がありましたがまだ不満足ですか？
回答番号：No.4で、別解を載せましたが、これに対する評価報告はないですか？

補足

お世話になります。いろいろと混乱させてしまいご迷惑をおかけしています。まずは今の状態は次のようになっております。しかし，貼り付けるセルの前に空欄があると一つずれてしまうのが難点です。しかし，ＮＯ５でお答えいただいた「Set rngp = Application.InputBox _("貼付け先セルを選択！", "貼付け先", Type:=8)」でメッセージを出して，セルを選択した方が気に入りました。次のマクロに取り入れたかったのですが貼り付けがうまくいかず困っています。作ろうとしているブックは全体の名簿を作成し１組から３組にコピーして分けデータを入力し，また，全体名簿に合体しその後クラス替えを行いまた，新１組から３組に分けデータを入力してもらいます。それを５回繰り返します。そのために，全体名簿のコピーを分けるために１組，２組，３組と名前を変えてもデータを移動して合体できるようにしたいのです。次の文を参考にし「Set rngp = Application.InputBox _("貼付け先セルを選択！", "貼付け先", Type:=8)」が入り，コピー元のフィルの名前を入れずに貼り付ける方法がありましたらお願いいたします。
Sub 全体名簿へ2組()
'
' 全体名簿へ2組 Macro
'

'
開始 = Range("r3").Value
終了 = Range("s3").Value

Range("B" & 開始 + 1 & ":n" & 終了 + 1).Select

Selection.Copy
Windows("全体名簿.xlsm").Activate
Sheets("★生徒名簿").Select
Range("B65536").End(xlUp).Offset(1).Select
If MsgBox("Ｂ列で前のクラスの生徒の次のセルを選択していますか？選択するセルの前が空欄の場合いはそのセルにスペースを入力してください。", vbYesNo) = vbYes Then

ActiveSheet.Paste
Else: Worksheets("★生徒名簿").Activate

End If

回答No.3

xls88

＞回答番号：No.2 この回答への補足
多分、コピーモードが終了しています。
クリップボードの貼り付けるべきデータが失われるので貼り付けが失敗します。
現状のコードを提示して頂く良く解るのですがクリアする位置が違っているのでは？

Windows("全体名簿.xlsm").Activate
Sheets("★名簿").Select
Range("B" & ActiveCell.Row, "n" & Range("B65536").End(xlUp).Row).ClearContents

Windows("コピー元ブック.xlsm").Activate
Sheets("コピー元シート").Select
開始 = Range("r3").value
終了 = Range("s3").value
Range("B" & 開始 + 1 & ":H" & 終了 + 1).Select
Selection.Copy

Windows("全体名簿.xlsm").Activate
Sheets("★名簿").Select
ActiveSheet.Paste

＞回答番号：No.1 この回答への補足
駄目だしだけでは次に進めません。
期待した結果とどう違うのか説明が必要です。

補足

できました。ＮＯ１の回答では補足入力の意味がわからず文章の途中でになってしまいました。懇切丁寧に面倒を見ていただきありがとうございました。今度は合体した名簿を次の年にクラス，名前，その他のデータを１組から３組までに分けるマクロを作成します。また，わからない場合はお世話になります。ありがとうございました。

回答No.2

xls88

新規データを追加していくのではないのですか？

＞貼り付けする前に選んだセル以降の行をクリアーにしてから
多分こういうことだと思います。
Range("B" & ActiveCell.Row, "n" & Range("B65536").End(xlUp).Row).ClearContents

＞Range("B65536").End(xlUp).Offset(1)
既存のデータの下に新規データを追記する場合に使われる常套手段です。
Range("B65536")の部分は、普通Excelシートの最終行を指定します。

2007以降、行列共にサイズ変更されているので
Range("B1048576").End(xlUp).Offset(1)
となります。

ただし、2003以前のExcelで実行するとエラーになります。
下記のようにすればどちらにも対応できます。
Range("B" & Rows.Count).End(xlUp).Offset(1)

＞ActiveSheet.Paste
クリップボードの内容をシートに貼り付けるだけの命令文です。

補足

お答えいただきありがとうございますRange("B" & ActiveCell.Row, "n" & Range("B65536").End(xlUp).Row).ClearContentsを入力するとエラーメッセージｗｏｒｋｓｈｅｅｔクラスのｐａｓｔｅメソッドが失敗しました。

回答No.1

xls88

Range("B65536").End(xlUp).Offset(1).Select
↑Offset(1) で1行下にオフセットしています。

↓Offset(1)を外せば希望通りになりませんか？
Range("B65536").End(xlUp).Select

Offset(行, 列)
列が省略された書き方がされています。
Offset(1, 0)
という事です。

あと、下記のようにSelectしなくてもコピーはできます。
ブック指定のところは、WindowsをWorkbooksに変更します。

Range("B" & 開始 + 1 & ":n" & 終了 + 1).Copy _
Workbooks("全体名簿.xlsm").Sheets("★名簿").Range("B65536").End(xlUp)

≪参考1≫
Copyメソッドには、Destinationという便利な引数があります。
セルをコピーする(Copyメソッド)
http://www.moug.net/tech/exvba/0050101.htm

≪参考2≫
1行の記述文が長いと見えにくくなります。
行継続文字「_（アンダーバー）」を使って改行することが出来ます。
コーディングの留意点
■長いコードは適当なところで改行する
http://www.tsware.jp/study/vol16/vbabegin_16.htm

補足

お答えいただきありがとうございましたRange("B65536").End(xlUp).Selectにかえましたがうまくいきませんでしたがいろいろアドバイスありがとうございました。