lisz-works

技術と興味の集合体

別のブックにセルをコピーするExcel VBA

【スポンサーリンク】

Excel

Excelのあるブックから、別のブックにコピーする処理についてです!

職場のルール上

「あるExcelと同じ内容を、別Excelにも入力しないといけない……」

という鬼のように面倒な仕打ちを受けたりしませんか?

廃止や改善されればベストですが、なかなかそうはいかない……

なら、自力で楽をしよう。

ソース

ソースはこんな感じ。

  1. 現在のブックから、指定のブックに対して処理
  2. コピー元/先、それぞれシートと範囲の指定が必要
  3. コピー先はブックのパスも
  4. コピー先の範囲は、先頭セルを与えれば、コピー元と同じ範囲分貼付け
' 処理高速化用:関数開始時に実行する処理セット
Function startFunc()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
End Function
' 処理高速化用:関数終了時に実行する処理セット
Function endFunc()
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Function

' コピー処理
Function book2bookCopy(srcSheet As String, srcRange As String, _
                dstBook As String, dstSheet As String, dstRange As String)
    ' 処理高速化用:開始処理
    Call startFunc
    With Workbooks.Open(ThisWorkbook.Path & "\" & dstBook)
        ' コピー
        ThisWorkbook.Worksheets(srcSheet).Range(srcRange).Copy
        .Worksheets(dstSheet).Range(dstRange).PasteSpecial _
            xlPasteValuesAndNumberFormats
        ' コピー中状態を解除
        Application.CutCopyMode = False
        ' 処理高速化用:終了処理
        Call endFunc
        ' 保存して閉じる
        .Close True
    End With
End Function

' テスト実行用
Function test()
    Call book2bookCopy("Sheet1", "A1:E5", "b.xlsx", "Sheet1", "A1")
End Function

解説

startFunc/endFunc

startFunc/endFuncは、コチラのページを参照した際に、「処理高速化のためにするとよい」とあったので採用。
書かれていたものを関数化しただけのものです。

qiita.com

それぞれこのような効果があります。

  1. 「.ScreenUpdating = False」:画面更新の停止
  2. 「.EnableEvents = False」:イベントを無効
  3. 「.Calculation = xlCalculationManual」:自動計算の停止

ブックを開く

まず

With Workbooks.Open(ThisWorkbook.Path & "\" & dstBook)

の「Workbooks.Open」で、ブックを開いています。
対象は「このブックがあるフォルダパス」内の「引数指定のブック」となっています。

ここは状況によって変更すればOKです。
例えば引数でフルパスを貰うとか。

コピー処理:コピー元/先

ここでコピーを行っています。

' コピー
ThisWorkbook.Worksheets(srcSheet).Range(srcRange).Copy
.Worksheets(dstSheet).Range(dstRange).PasteSpecial _
    xlPasteValuesAndNumberFormats

コピー元とコピー先は

  • 「ThisWorkbook.Worksheets(srcSheet)」がコピー元
  • 「.Worksheets(dstSheet)」がコピー先

となります。
なのでコレを逆にすれば、「別のブックからこのブックにコピー」ということも可能です!

コピー処理:各メソッド

コピー処理で使用しているメソッドと指定しているものは

  • Copy:コピー
  • PasteSpecial:形式を選択して貼り付け
  • xlPasteValuesAndNumberFormats:値と数値の書式

という意味合いになります。

なので対象のシート「.Worksheets()」の
対象の範囲「.Range()」を

  • コピー元は、コピー「.Copy」
  • コピー先は、値と書式の貼り付け「.PasteSpecial xlPasteValuesAndNumberFormats」

ということを行っています。

コピーの終了

コピー処理が完了したら

Application.CutCopyMode = False

コピー中の状態を解除します。

「~.Copy」を解除する意味合いがあります。

Excelを操作しているとき、コピーすると点線が出ますよね?

このコピー中状態を解除(GUIだとESCキー)するのと同じイメージかと!

Withと「.xxx」について

ちなみに「.Worksheets()」というのは、「With」で指定しているものを省略しているだけです。

With Workbooks.Open(...)
    .Worksheets(...)
        ()
End With

とすると、Workbooks.Open()で、Workbookが戻り値とされます。
なのでこの「.Worksheets(...)」は、「対象のブック.Worksheets()」と同じ意味になります。

記述を省略しているような感じですね。

注意点

endFuncを、「保存して閉じる」の前に行っておかないと、閉じるブックが開始処理で諸々OFFに設定した状態で保存されてしまいます。

そうすると例えば

自動計算などがOFFとなるので、数式に関わるセルが更新されても計算が反映されない……

なんてことが起こるので、注意しましょう!
ぼくは実際それで頭抱えました!(笑)

参考

コチラを参考にしました。
ありがとうございました!

excel.style-mods.net

qiita.com

Application.ScreenUpdating プロパティ (Excel)

イベントの発生・無効をコントロール(EnableEvents プロパティ) | ExcelWork.info

thom.hateblo.jp

セルをコピーする(Copyメソッド):Excel VBA|即効テクニック|Excel VBAを学ぶならmoug

形式を選択して貼り付ける(PasteSpecialメソッド):Excel VBA|即効テクニック|Excel VBAを学ぶならmoug

oshiete.goo.ne.jp

あとがき

コレを応用すれば、いろいろと便利になりそう!

ぼくは現にめんどくさい雑務が、キー1発で済むようになりました!

  1. 元のExcelを開く→編集する
  2. もう1つのExcelを開く
  3. 元からコピー
  4. もう1つの方に貼り付け

という、一見単純そうで地味に時間がかかるこの処理。
しかもNASにファイルがあると、余計に時間がかかるんですよね……

しかし!これが!ボタン1発!

楽ちんだ!