マクロからセルをコピーしたい（速度重視or度外視）

コンテンツ

例えばA1セルからB１セルにコピーしたいとき、「Excelでコピー」って言っても色々あるんですよね。

値だけをコピーしたい
数式だけをコピーしたい
書式だけをコピーしたい
etc…

人の手でやるなら簡単です。
A1セルでコピー（Ctrl+C）して、B1セルを選択して右クリックして「形式を選択して貼り付け(S)」から好きなモードを選択すればOK！
これをVBAでやるとどうするって話をちょっとまとめていきます。

人の手と同じことをする（クリップボードを使う）

上で説明したコピペ方法をVBAでやるだけ。
例えば値をコピーしたい場合はこう。

With Sheet1
    .Range("A1").Copy
    .Range("B1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

Paste:=に指定するXlPasteTypeが xlPasteAll なら全てコピペするし、 xlPasteFormulas なら数式、 xlPasteValues なら値、 xlPasteFormats なら書式…と色々選べます。
検索すると解説サイトさんばかり引っかかりますが、公式のこちらも参照してください。
ちなみにSheet1以外のシートを表示していても動きます。

ただ、なにより書くのが簡単な方法ですが、デメリットがいくつかあります。

クリップボードがうまく動かないことがある
人の手を介していても「クリップボードが開けませんでした」というエラーが出ることがありますしね。
並行作業ができない
クリップボードを使うわけですし、マクロを動かしている間、ちょっと横でテキストファイルのコピペを…なんてできませんよ。
クリップボードに残していい内容なのか
個人使いなら気にしなくていいでしょうが、セルに個人情報が入っている場合、クリップボードに保存されてしまうことは果たして問題ないでしょうか？
ごく稀に、エラーにならないのにコピーされない（空欄のまま）になる端末が存在する
WindowsUpdateをあてたとか、なにかのきっかけでなる端末が世の中に存在してそう。
しかもその状態になると、かなり高確率で発生する。
WindowsUpdateを当て直すとかで解決することもある。
こういう「こともある」系が一番タチが悪い！

Range.Valueを使う

オススメは断然こっちなんですが、コピペしたい条件によって注意が必要です。
まず、Range.Valueを使う場合は「値だけ」もしくは「値と書式と数式」のどちらかしか選べません。
なので、「書式だけ」コピーしたいとかなら前述のPasteSpecialかねぇ。

値だけのコピーなら簡単

これでOK！速度も早いしクリップボードも使わない！

With Sheet1
    .Range("B1").Value = .Range("A1").Value
End With

[応用]Valueに入れる値を自分で作る

1セル分ならString型でもLong型でも変数用意すればいいんですが、複数セルが必要な場合はVariantで宣言します。
ちなみに1行×N列なら簡単です。
下記コードはA1セルから横に1、2…5をセットします。

Dim aryValue As Variant
aryValue = Array(1, 2, 3, 4, 5)
Sheet1.Range("A1:E1").Value = aryValue

これがX行×Y列なら二次元配列にする必要があります。
下記コードでは、A1～B2の4セルにZ向きに1、2、3、4をセットします。

Dim aryValue(1, 1) As Variant
aryValue(0, 0) = 1
aryValue(0, 1) = 2
aryValue(1, 0) = 3
aryValue(1, 1) = 4
Sheet1.Range("A1:B2").Value = aryValue

「1行は添字が0で…？」って混乱する人は配列宣言時に添字をずらしても大丈夫ですよ。
下記コードはまったく同じことをやります。

Dim aryValue(1 To 2, 1 To 2) As Variant
aryValue(1, 1) = 1
aryValue(1, 2) = 2
aryValue(2, 1) = 3
aryValue(2, 2) = 4
Sheet1.Range("A1:B2").Value = aryValue

あと、これで値をセットする場合、オートフィルタでフィルタリングされていると思わぬ動きをするので注意が必要です！

オートフィルタが設定されている可能性があるなら、セットする前に解除しましょうね。

With Sheet1
    If .AutoFilter.FilterMode Then
        .ShowAllData
    End If
    ' ここ以降でセット
End With

[応用]Range.Valueで取得した値を判別したい

Range.ValueをVariant型変数に格納すると、1セルの場合は値が、複数セルの場合は行数に関わらず二次元配列になります。

Dim aryValue As Variant
Dim i As Long
Dim j As Long
aryValue = Sheet1.Range("好きな範囲").Value
If IsArray(aryValue) Then
    ' 複数セル
    For i = LBound(aryValue, 1) To UBound(aryValue, 1)
        For j = LBound(aryValue, 2) To UBound(aryValue, 2)
            Debug.Print aryValue(i, j)
        Next j
    Next i
Else
    ' 1セルのみ
    Debug.Print aryValue
End If

まるごとなら xlRangeValueXMLSpreadsheet を指定

コピー元と先の両方のValueにxlRangeValueXMLSpreadsheetを指定します。

With Sheet1
    .Range("B1").Value(xlRangeValueXMLSpreadsheet) = .Range("A1").Value(xlRangeValueXMLSpreadsheet)
End With

ただ、これドチャクソ遅いです。

1回こっきり動かすだけなら良いですが、頻繁に使うシステムとしてはとても採用できません。
うまくValueとPasteSpecialを使い分けましょう。

他にもニッチなIT関連要素をまとめていますので、よければ一覧記事もご覧ください。

みにましあ

https://minimashia.net/it-tips

2019.12.04

IT tips

ITカテゴリーのページじゃ探しにくい方向けにまとめた記事です。ニッチな情報ばかりなので、需要あるのか疑問だわぁ…。Windowsスリープ中のWindowsパソコンでもUSB充電する方法Windows10（1909）のダウンロードフォルダの表示をもとに戻す自宅勤務でネットワーク設定の切り替えをする自宅勤務でスピーカー設定の切り替えをするWindows11対応端末かどうか調べるWindows10の大型Update（21H1）に失敗する場合の対処法いまさらWindows7を用意して.NET Framework4.6.2をインストールしてみたWindows11を触ってみた感想@2022年4月同じ...