VBA セルの値を別セルにコピーするには

質問

VBAでPastespecialでセルの値を別セルにコピーするマクロを組みたいです。
以下は参考にしたソースコードです。
Worksheets("Sheet1").Range("A1:B10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial _
　　　　　　　　　　　　　　　　 Paste:=xlPasteValues, _
　　　　　　　　　　　　　　　　 Operation:=xlNone, _
　　　　　　　　　　　　　　　　 SkipBlanks:=False, _
　　　　　　　　　　　　　　　　 Transpose:=False

あるExcelマクロの入力フォームSheetに、製品リストと使用している場所のマスタデータをクエリで読み込んで、製品IDと場所のコードを入力したらINDEX関数で抽出し、マクロ実行ボタンを押すと抽出結果を入力フォームの入力欄に貼り付けします。

上記のマクロだと貼り付けする元セルを移動させたら内容がずれた値がそのまま貼り付けされてしまうと思われますが、地道にコードのコピー元のセルを書き直さないといけないのでしょうか。

Excelの関数だと参照範囲を固定したら掴んで移動させてもセル番地が連動して移動してくれますが、マクロの場合どのようにすれば良いでしょうか。

また、複数個所のセルをコピーするので
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

をコピーするセルの箇所に毎回入れていますが、コピー元のセル・コピー先のセル番地を一括して実行する方法はありますでしょうか。

VBAはソースコードを参考に当てはめているだけで、自力でコードを書くスキルは皆無です。
Excelは2016です。

詳しい方いましたらご教授ください。よろしくお願い致します。

回答No.8

kkkkkm · ベストアンサー

> Worksheets("リスクアセスメントシート").Range("X9").Formula = Worksheets("リスクアセスメントシート").Range("D8").Formula

> X9に関数で抽出した値を、D8へ代入するとX9とD8が空欄になってしまいます。

コードはD8をX9に代入となっています。代入の場合は右辺を左辺に代入となります。

一番最初の回答でも質問のコードをもとにして右辺(Sheet1)を左辺(Sheet2)にとなっていると思いますよ。

お礼

おかげさまで無事代入できるようになり、入力フォームの入力作業が簡略化できました。

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

補足

＞一番最初の回答でも質問のコードをもとにして右辺(Sheet1)＞を左辺(Sheet2)にとなっていると思いますよ。

右辺左辺を逆にしていました。
Worksheets("リスクアセスメントシート").Range("D8").Value = Worksheets("リスクアセスメントシート").Range("X9").Value

にしたところ結合セルに値を代入することができました。

回答No.7

kkkkkm

ところで、回答で何度か示したコードを自身の環境に変更して試したのしょうか。

補足

初歩的な質問にお付き合いいただき、本当にありがとうございます。
＞ところで、回答で何度か示したコードを自身の環境に変＞更して試したのしょうか。

Sub Step1_copy()

Worksheets("リスクアセスメントシート").Range("X9").Formula = Worksheets("リスクアセスメントシート").Range("D8").Formula

End Sub

補足足らずでした。別シートのセルの値ではなく、同一のシートのセルからセルへのコピーです。
仮ですが、X9に関数で抽出した値を、D8へ代入するとX9とD8が空欄になってしまいます。

.Formulaを.Valueにしても同じ現象になります。
D8が結合セルになっているからでしょうか。

ご教授ください。

回答No.6

kkkkkm

別シートと書きましたが質問のコードが別シートになっているのでそのようになっていると思ってますが

> 代入する方法だとコピー元の抽出関数を入れたセルも抽出した値になってしまい

というのは、たとえばE1の値をC3に代入したらE1も書き換わるということですよね。
コードを見直した方がいいです。

回答No.5

kkkkkm

> 入力フォームに必要な情報をINDEX＋MATCH関数で抽出した値をコピーしようとしています。
> 抽出関数で出た値→入力フォームに貼り付け

値をコピーしたいのですよね。数式をコピーする必要はないのでは？

> 代入する方法だとコピー元の抽出関数を入れたセルも抽出した値になってしまい、関数が抽出した値に上書きされてしまいます。

別のシートの入力フォームのセルに代入するのにコピー元が書き換わるわけはないでしょう。
どこかコードが間違ってます。

回答No.4

kkkkkm

E1の数式によってはNo.2のパターンでもいけると思いますので色々試してみてください。

回答No.3

kkkkkm

「仮に」のように位置が違うとしたら

Worksheets("Sheet1").Range("E1").Copy
Worksheets("Sheet2").Range("C3").PasteSpecial _
Paste:=xlPasteFormulas
E1の式の参照位置によってはC3が#REF!になるとは思いますが、その場合は絶対参照にするとか対応が必要ですね。

数式を毎回コピペする必要があるのでしょうか。

補足

補足回答頂きありがとうございます。

数式を毎回コピペする必要があるのでしょうか。
＞＞
入力フォームに手打ち・ドロップダウンリストから選択する手間を省こうと、フォーム横の空いてるスペースに製品IDと箇所CD(検索値)を入力し、入力フォームに必要な情報をINDEX＋MATCH関数で抽出した値をコピーしようとしています。

抽出関数で出た値→入力フォームに貼り付け

といったイメージです。

代入する方法だとコピー元の抽出関数を入れたセルも抽出した値になってしまい、関数が抽出した値に上書きされてしまいます。

回答No.2

kkkkkm

> 結合セルにコピーペーストするとエラーになる

代入したらエラーにはならないですよね。

> コピー元の関数を入れたセルにも関数で抽出した値が上書きされてしまい、関数が消えてしまいます。

質問のコードをもとにした場合

Worksheets("Sheet2").Range("A1:B10").Formula = Worksheets("Sheet1").Range("A1:B10").Formula

にすればいいだけだと思います。

回答No.1

kkkkkm

> VBAはソースコードを参考に当てはめているだけで、自力でコードを書くスキルは皆無です。

という事は、たとえばのコードを示してもそれを自身の環境に合わせることはできないという事だと思いますので、セルの位置とかを具体的に記載して、どこをどこにコピーしたいのか、貼り付けする元セルを移動させるとはどのように移動するのか等を記載しないと回答ができないと思います。

値貼り付けならコピー貼り付けでなくでも代入すれば早いです

Worksheets("Sheet2").Range("A1:B10").Value = Worksheets("Sheet1").Range("A1:B10").Value

これのA1:A10が変化してA1:B10とC1:D10にデータを代入するなら、

Worksheets("Sheet2").Range("A1:B10").Value = Worksheets("Sheet1").Range("A1:B10").Value
Worksheets("Sheet2").Range("C1:D10").Value = Worksheets("Sheet1").Range("C1:D10").Value

とは書かずに

たとえば以下のように変化します。

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For i = 1 To 3 Step 2
ws2.Range(ws2.Cells(1, i), ws2.Cells(10, i + 1)).Value = ws1.Range(ws1.Cells(1, i), ws1.Cells(10, i + 1)).Value
Next
End Sub

補足

回答頂きありがとうございます。

どこをどこにコピーしたいのか、貼り付けする元セルを移動させるとはどのように移動するのか等を記載しないと回答ができないと思います。
＞＞画像を添付するべきでした。失礼いたしました。

1）既存の入力フォーム(Sheet)の横スペースにIDを入力するとINDEX関数+MATCH関数で別シートから製品名とIDに紐付いている情報を抽出(仮にE1セルとします)
2）抽出した情報をVBAで入力フォームの入力欄に貼り付け
(仮にC3セルとします)

結合セルにコピーペーストするとエラーになるので予め、入力欄の結合されたセル枠を結合しないにします。

代入するVBAは、私も試してみましたが、コピー先のセルに貼り付けはできたものの、コピー元の関数を入れたセルにも関数で抽出した値が上書きされてしまい、関数が消えてしまいます。