顧問先から回収したExcelの経理データなどを会計ソフトにCSVデータとしてインポートする際にマクロを利用してデータ加工を行うことがあるんですが、自分がマクロを作成するときによくでてくる処理としてExcelのデータをコピーして別のシートへ「値のみ貼り付ける」ことがよくあります。
今までこの「値のみ貼り付ける」処理をなんとなく、いきあたりばったりでコードを書いてきた感じがするので、今後のことも考えて一回整理してみます。
目次
Excelのデータを別シートの違う場所に「値のみ貼り付ける」マクロ
今回やりたいことはこんなことです。
例えばこんな感じのデータがあったとします。
で、このデータですがD列には数式（B列の単価×C列の個数）が入力されていて、
G列にはVLOOKUP関数が入力されている状態です。
このうち、B3セル～G9セルまでのデータをコピーして、別のシート（今回は「コピー先」シート）に値のみ貼り付けたいんです。
しかも、貼り付ける場所は別シートの適当なセル（例えばA2セルとか）に貼り付けたいんです。
ちなみに、緑の表のデータは後からどんどん追加されるという前提です。今回は都合上データが7個しかありませんが、10個や20個、100個以上ある場合も想定しています。
ただし、「単価」、「個数」、「合計」、「商品コード」、「備考」、「摘要」などの項目名はこれ以上追加されないことにしておきます。
この状態で例えば緑の表のデータ部分（B3セル～G9セルまでのデータ）だけをコピーして別のシートに貼り付けるようなマクロを作成して実行すると、数式や関数が入力されたセル部分に関してはエラー値になって貼り付けられてしまいます。
そんな時に今回の「値のみ貼り付ける」マクロを作成します。
Excelマクロで値のみ貼り付けるパターン① valueとresizeを使う
1つめの方法として考えたのがこのやり方です。
いわゆる？「range(a2).value=range(b2).value」の形式で貼り付けるやりかたです。
Sub tenki1()
'■valueによる値のみ貼り付け resizeで張り付ける範囲を設定する必要あり
Dim lastrow As Long
lastrow = Sheets("元データ").Cells(Rows.Count, 2).End(xlUp).Row
With Sheets("元データ")
Sheets("コピー先").Range("a2").Resize(lastrow - 2, 6).Value = _
.Range(.Range("b3"), .Range("g" & lastrow)).Value
End With
End Sub
まずは変数の宣言(今回はlastrowとしました。)をしておいて、データの最終行を取得しておきます。
今回の場合はlastrowに最終のデータ行である「９」が格納されることになります。
次にRange(.Range(“b3”), .Range(“g” & lastrow))とすることで、コピー元のデータ範囲を取得することができます。
下の赤枠で囲った部分です。
今回の場合は上のコードを書くことで、「元データ」シートのB３セル～G９セルまでの範囲を取得することができます。
最後に、貼り付け先の範囲を取得する必要がありますが、これについては一旦、貼り付け先の「コピー先」シートの適当なセルを設定（今回はA２セル）しておいて、そのあとにresizeを使用してコピー元と同じセル範囲を取得させることにしています。
下の赤枠で囲った部分です。
このようにResizeを使って、A2セルを縦に7セル（lastrowには「９」が格納されているので9－2＝７）、横に6セル分大きくしています。
このコードを実行すると、コピー先シートのA2セルを起点として、「元データ」シートのB３セル～G９セルまでの範囲のデータを値のみ貼り付けることができます。
これで別シートに値のみ貼り付けることができました。ただ何となくコードがわかりにくいというか汚い感じがします。
resizeの使い方が苦手という個人的な問題のせいかもしれませんが。。。
Excelマクロで値のみ貼り付けるパターン② valueとoffsetを使う
２つめの方法として考えたのがこのやり方です。
一旦、コピー元と同じセル範囲で値のみ貼り付けておいて、その後offset関数で貼り付ける位置を移動？調整？させるやり方です。
（コードをすっきりされるために「コピー先」シートを変数ｗｓに格納する処理を追加しています。）
Sub tenki2()
'■valueによる値のみ貼り付け、一旦、同じ範囲を指定して、offsetで貼り付ける場所を調整する。
Dim lastrow As Long
Dim ws As Worksheet
lastrow = Sheets("元データ").Cells(Rows.Count, 2).End(xlUp).Row
Set ws = Sheets("コピー先")
With Sheets("元データ")
ws.Range(ws.Range("b3"), ws.Range("g" & lastrow)).Offset(-2, -1).Value = _
.Range(.Range("b3"), .Range("g" & lastrow)).Value
End With
End Sub
このコードを実行すると、コピー先シートの「A１」セルを起点として、「元データ」シートのB３セル～G９セルまでの範囲のデータを値のみ貼り付けることができます。
offsetの括弧の中の記載を変更することで貼り付けたいセルを自由に設定することができます。
例えばoffset(2,2)とすればD5セルが起点になります。多分。
Excelマクロで値のみ貼り付けるパターン③ copyしてpastspecialを使う
３つめの方法として考えたのがこのやり方です。
考えたというか、マクロ記録とかやるとこのやり方が記録されるかと思います。
Sub tenki3()
'■copyしてpastspecialで値のみ張り付ける。 張り付ける範囲を設定する必要はない
'■コピー元が選択状態のままになるので、気になる人は張り付け後にコピーモードを解除
Dim lastrow As Long
lastrow = Sheets("元データ").Cells(Rows.Count, 2).End(xlUp).Row
With Sheets("元データ")
.Range(.Range("b3"), .Range("g" & lastrow)).Copy
End With
Sheets("コピー先").Range("a16").PasteSpecial xlPasteValues
Application.CutCopyMode = False '■コピーモードを解除する
End Sub
このやり方だと貼り付け先のセルはコピー元のセル範囲と同じセル範囲にしなくてもよい。ので
今回のケースではA16セルとだけ指定すればいいので楽です。
この点は先の2つのマクロよりメリットを感じます。
まとめ
今回3つのやりかたで「値のみ貼り付ける」マクロを考えて整理してみました。
最終的な動作としてはどのやりかたでも当然同じになるんですが、コードをぱっと見たときに見やすいかどうかっていう面から考えると個人的には（何となく感覚的に）pastspecialを使用してコードを書く方がいいような気がします。ホントに何となくですが。
その次はoffsetを使うやりかたですかね。
ということで今後はpastspecialを使用していこうと思いますがどのやりかたが一番いいんでしょうかね？？？
【本日の近況報告】
楽天証券にて証券口座を開設しました。
NISA口座の開設は今回は見送ってとりあえず特定口座の源泉徴収有りを開設しました。
頻繁に売買する気はないので長期保有で運用できればと考えています。
【本日の１曲】
Fluorescent Adolescent / Arctic Monkeys
アークティックモンキーズのセカンドアルバムの中の１曲
I bet you look good on the dance floor などが有名なバンドですが（当社調べ）
アルバムでしっかり聴いたことがあるのは1枚目、2枚目くらいでその後は
チェックしてません。
ちなみに1枚目と2枚目はレコードで買った記憶があるのでどこかにあるはず。。
セカンドアルバムでのハイライトの1曲はBrianstormだと思いますが、個人的には
この曲がこのアルバムでは1番好きだったりします。
最近、スピッツの草野さんがラジオでこのバンドを特集してまして、
この曲も紹介されてました。
