🛣️

Excel VBA でセル範囲を転記する書き方

2024/04/25に公開

Excel

VBA

tech

Value で転記するのがおすすめ

VBA でセル範囲をコピペするのに PasteSpecial を使っているなら、他の方法に変えるのをおすすめします。

理由はこの記事を読んでみると分かります。

Value を使った転記がうまくいかないと悩んだことがあるなら、以下の記事を読んでみると参考になります。

おしまい。

おまけ

VBA で別のブックに転記するマクロの例です。
PAD から実行する前提で書いています。

Power Query を使って読み込んだデータを別のブックに転記するイメージです。

「Power Automate Desktop CSV を Excel に転記 Power Query 編」でも VBA で転記するマクロを書いています。ほぼ同じ構成になっています。

Power Query のデータを更新
転記元のデータの範囲を取得
転記先の範囲を元と同じ大きさにする
Value で転記

Option Explicit

Sub テスト()
    Call 転記("template.xlsx")
End Sub

Sub 転記(destName As String, _
    Optional isRefresh As Boolean = False)

    Dim origBook As Workbook
    Set origBook = ThisWorkbook

    'PADから開くと自動更新されないので、更新を行う
    'Power Queryの「バックグラウンド更新」はオフにすること
    If isRefresh Then
        origBook.RefreshAll
    End If

    '転記先のブックは、このブックと同じ階層に置くこと
    Dim destBook As Workbook
    Set destBook = Workbooks.Open( _
        origBook.Path & "\" & destName)

    'シート1枚目から8枚目まで転記
    'このシート名と同じ名前のシートへ転記する
    Dim index As Integer
    For index = 1 To 8
        Dim origSheet As Worksheet
        Set origSheet = origBook.Worksheets(index)
        Dim sheetName As String
        sheetName = origSheet.Name
        Call DoPaste(origSheet, destBook, sheetName)
    Next index

    destBook.Save
    destBook.Close True
End Sub

Sub DoPaste(origSheet As Worksheet, _
    destBook As Workbook, sheetName As String)

    Dim row As Long, col As Long
    With origSheet.UsedRange
        row = .Rows.Count
        col = .Columns.Count
    End With

    Dim destSheet As Worksheet
    Set destSheet = destBook.Worksheets(sheetName)
    With destSheet
        .Cells.ClearContents
        .Range("A1").Resize(row, col).Value = _
            origSheet.UsedRange.Value
        .UsedRange.EntireColumn.AutoFit
    End With
End Sub

sheetName = origSheet.Name
- 転記元のシート名を取得して転記先のシートを特定するのに使っています。
  （名前を合わせています。）

上で色々コードを書いていますが、この記事タイトルのセル範囲を転記するコードはこの 1 行です。

destSheet.Range("A1").Resize(row, col).Value = origSheet.UsedRange.Value

まるごと転記で良いなら、UsedRangeを使うとセル範囲を調べなくて良いので簡単です。

他にもCells(1, 1)やCells(1, "A")などセルの指定方法は色々ありますが、Cellsを使う場合は数値よりもアルファベットの方がわかりやすいかなあと思います。お好みで。

別のシートを扱うときの落とし穴は、以下の記事で詳しく説明されています。

補足

ここまで Value がおすすめ。と書いてきましたが、「Power Automate で SharePoint ファイルの情報で処理を分岐する」では、.Copy destSheet.Range()を使っています。これはこれでコピーするときにセル範囲を気にしないでいいので便利ですよね。テーブルをコピーするときはこっち使うかな。

あまり一つの方法に固執せずに臨機応変に使っていけたらな。と思います。

おわりに

Active や Select をしないで転記する方法、ぜひ持って帰ってください。

Value で転記するのがおすすめ

おまけ

補足

おわりに

Discussion