Copy、Pasteメソッドを使わないコピペ

Copy, Pasteメソッドを使わないなら
コピペとは言わないとは思いますが
とりあえずわかりやすいと考え、こう表現しました。

まずは、
なぜワザワザCopy, Pasteを使わないのか？ですが
Copy, Pasteを使うプロシージャを実行しながら
他の作業をしているとき面倒が増えることがあります。

他の作業で手動でコピーをして
ペーストをしたい箇所にカーソルを動かす間に、
裏でExcel VBAがCopy、Pasteをしてしまうと
他の作業で手動でコピーしたデータが
クリップボードからなくなり
また手動でコピーをしなくてはならなくなります。

という理由で個人的には
Excel VBAでCopy、Pasteは使わない方針です。

さて、Copy, Pasteメソッドを使わないコピペとは
どうするのかというと
範囲を直接参照するだけです。

１つのセル同士ならイメージが沸きやすいです。

Range("A1") = Range("A2")

＝で結ぶだけです。

複数のセルでもほぼ同じです。

Range("A1:C10") = _
   Range("D1:F10").Value

複数のセル（範囲）だと「.Value」をつけないと
空白のままで値が入りませんでした。

この方法とコピペには1つの違いがあります。
Copy、Pasteはフォーマットと装飾もコピペされるのに対し
直接参照すると装飾はなくなり、
フォーマットは、
反映されたり、されなかったりです。

ん～
フォーマットは、反映されると言うよりは、
値を入れたときにオートフォーマットされるかされないか
といったほうが正しいです。

装飾はともかく
フォーマットは必要ですので、
次のように方法で移します。

Range("A1:C10").NumberFormatLocal = _
   Range("D1:F10").NumberFormatLocal

サンプルコードの前に
下のプロシージャを実行して準備してください。

下のプロシージャを実行して挿入される
シートmacro101127をアクティブにして
その他のプロシージャを実行してください。

Sub macro101127a()
'macro101127系準備
'A列に現在から1分ごとの時間を入れる

    Dim i As Integer
    Sheets.Add.Name = "macro101127"
    Sheets("macro101127").Range("A1") = "日時"
    For i = 2 To 10
        Sheets("macro101127").Range("A" & i) = _
            Now + TimeValue("00:0" & i & ":00")
        Sheets("macro101127").Range("A" & i).NumberFormatLocal = _
            "yyyy年mm月dd日(aaa) hh:mm"
    Next i
    Columns.AutoFit
   
    Debug.Print "セルA2のフォーマット:" & Range("A2").NumberFormatLocal
   
End Sub

個別から汎用へ進んでいきます。

最初は1列同士のコピペです。
例として
範囲A2:A10を範囲B2:B10へコピペします。

Sub macro101127b()
'Copy, Pasteを使わないコピペ
'一列
'元データをコピー先を同じ大きさの範囲を指定
'すべて同一のフォーマット

    '範囲の値を直接指定する
    Range("B2:B10") = Range("A2:A10").Value
   
    'フォーマットを元データと同じにする
    'すべてのセルが同じフォーマットであること
    Range("B2:B10").NumberFormatLocal = _
        Range("A2:A10").NumberFormatLocal
   
End Sub

通常の手動でコピペする時には
左上の1つのセルだけを選択した状態で
ペーストすることが多いと思います。

次のコードはコピー先をそのように指定します。

Sub macro101127c()
'Copy, Pasteを使わないコピペ
'一列
'一番上のセルを基準にコピペ
'すべて同一のフォーマット

    Dim Range1 As Object 'コピー元を指定
    Dim RCount As Integer 'コピー元行数
    Set Range1 = Range("A2:A10")
    RCount = Range1.Rows.Count
   
    Range("B2").Resize(RCount, 1).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range("B2").Resize(RCount, 1) = Range1.Value
   
    'フォーマットを元データと同じにする
    'すべてのセルが同じフォーマットであること
    Range("B2").Resize(RCount, 1).NumberFormatLocal = _
        Range1.NumberFormatLocal
   
End Sub

上のコードの
コピー元範囲、コピー先の一番上のセルを
それぞれ引数Range1、Range2に指定して実行するように改造したのが
次のコード

Sub macro101127d(Range1 As Object, Range2 As Object)
'Copy, Pasteを使わないコピペ
'macro101127cを引数付に変更
'Range1 = コピー元範囲
'Range2 = コピー先一番上のセル

    Dim RCount As Integer 'コピー元行数
    RCount = Range1.Rows.Count
   
    Range2.Resize(RCount, 1).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range2.Resize(RCount, 1) = Range1.Value
   
    'フォーマットを元データと同じにする
    'すべてのセルが同じフォーマットであること
    Range2.Resize(RCount, 1).NumberFormatLocal = _
        Range1.NumberFormatLocal
   
End Sub

Sub macro101127dexe()
'macro101127d使用例

    'macro101127d(コピー元範囲, コピー先一番上のセル)
    Call macro101127d(Range("A2:A10"), Range("B2"))
   
End Sub

いままでは1列のみでしたが
次のコードの範囲は複数列でもOKです。

For文を使って1つ1つのセルのフォーマットを指定していくので
すべてバラバラのフォーマットでも大丈夫です。

Sub macro101127e()
'Copy, Pasteを使わないコピペ
'一番左上のセルを基準にコピペ
'すべて同一のフォーマットである必要はない

    Dim i As Integer, j As Integer
    Dim Range1 As Object 'コピー元を指定
    Dim Range2 As Object 'コピー先左上を指定
    Dim RCount As Integer 'コピー元行数
    Dim CCount As Integer 'コピー元列数
    Set Range1 = Range("A2:B10")
    Set Range2 = Range("C2")
    RCount = Range1.Rows.Count
    CCount = Range1.Columns.Count
   
    Range2.Resize(RCount, CCount).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range2.Resize(RCount, CCount) = Range1.Value
    Range2.Resize(RCount, CCount).Columns.AutoFit
   
    'フォーマットを元データと同じにする
    '１つ１つ参照するのですべて同じでなくてよい
    For i = 1 To RCount
        For j = 1 To CCount
            Range2.Offset(i - 1, j - 1).Select
            Range2.Offset(i - 1, j - 1).NumberFormatLocal = _
                Range1(i, j).NumberFormatLocal
        Next j
    Next i

End Sub

上のコードをまたまた
引数Range1、Range2に指定して実行するように改造したのが
次のコードです。

Sub CopyPe(Range1 As Object, Range2 As Object)
'Copy, Pasteを使わないコピペ
'一番左上のセルを基準にコピペ
'すべて同一のフォーマットである必要はない
'Range1 = コピー元範囲
'Range2 = コピー先左上のセル

    Dim i As Integer, j As Integer
    Dim RCount As Integer 'コピー元行数
    Dim CCount As Integer 'コピー元列数
    RCount = Range1.Rows.Count
    CCount = Range1.Columns.Count
   
    Range2.Resize(RCount, CCount).Select 'わかりやすく選択
       
    '範囲の値を直接指定する
    Range2.Resize(RCount, CCount) = Range1.Value
    Range2.Resize(RCount, CCount).Columns.AutoFit
   
    'フォーマットを元データと同じにする
    '１つ１つ参照するのですべて同じでなくてよい
    For i = 1 To RCount
        For j = 1 To CCount
            Range2.Offset(i - 1, j - 1).Select
            Range2.Offset(i - 1, j - 1).NumberFormatLocal = _
                Range1(i, j).NumberFormatLocal
        Next j
    Next i

End Sub

Sub macro101127f()
'CopyPe使用例

    Call CopyPe(Range("A2:B10"), Range("C2"))
   
End Sub

Copyメソッドと同じような使い心地を目指しました。

ただフォーマットを指定するところで
１つ１つセルを回しているので
あまり広い範囲のコピペには時間がかかりそうです。