シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > VBA > 【VBA】別シートにセル範囲をコピーしたい【100本ノック】

【VBA】別シートにセル範囲をコピーしたい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画「VBA100本ノック」に挑戦してみたいと思います。 ますはその1本目から。 FillAcrossSheets メソッドを使ってみます。

お題：VBA100本ノック 1本目：セルのコピー

問題ページ

方針

セルのコピーは Excel VBA の基本ですね。

セルのコピーに関連したメソッドには Copy や Paste 、PasteSpecial、FillAcrossSheets などがあり、それぞれクセがあるので状況によって使い分けられなければなりません。

本記事としてはそのうち、 Worksheets/Sheets オブジェクトの FillAcrossSheets メソッド を使ってみます。

ただしこれが適用できるのは、本題のように、コピー先が同じブックのワークシートで、かつセル範囲のアドレスも同じという状況に限られます。

解答：FillAcrossSheets を使ってみる

Sub VBA100_001()
    Worksheets(Array("Sheet1", "Sheet2")).FillAcrossSheets Worksheets("Sheet1").Range("A1:C5")
End Sub

　

　

考察

FillAcrossSheets メソッド はあまり知られていないメソッドかもしれません。

FillAcrossSheets メソッドは Copy メソッドのようにセル範囲を別シートにコピーするものです。

コピー先Worksheets.FillAccrossSheets コピー元セル範囲

複数のシートに一括でコピーできるところが Copy メソッドなどと異なります。 その代わりコピー先のアドレスを指定することができず、コピー元と同じセル位置に展開されます。 別ブックのシートからはコピーできません。

コピー先の指定には、 Worksheets（あるいはSheets） にシート名かインデックスを配列（Array）にして渡してやります。 このとき、配列にはコピー元のワークシートも含める必要があり、さもないとエラーになります。

FillAcrossSheets メソッドにはコピー対象を選べるオプションがあります。

  • xlfillwithall セル内容と書式
  • xlfillwithcontents セル内容のみ
  • xlfillwithformats 書式のみ

セル内容は値と数式です。ふりがなは xlfillwithall ではコピーされるのですが、なぜか xlfillwithcontents ではされません。 xlfillwithcontents は表示形式をコピーしないので日付はシリアル値になるでしょう。

書式は表示形式、スタイル、セルの結合、条件付き書式を含みます。

シート全体のセル範囲をコピー対象とすると、セルの内容や書式だけでなく、セルの幅や高さもコピーされます。

クリップボードにコピーしたセル範囲はクリアされますが、テキストのコピーなどシステムのクリップボードはクリアされません。

速度的には Copy メソッドよりも高速になるようです。

    dim wb as Workbook
    set wb = ActiveWorkbook

    ' インデックスで指定
    wb.Worksheets(Array(1,2)).FillAccrossSheets wb.Worksheets(1).Range("A1:L10"), xlfillwithformats

    ' 新規シートに書式をコピー
    wb.Worksheets.Add Before:=1, Count:=12
    wb.Worksheets(Array(1,2,3,4,5,6,7,8,9,10,11,12, "雛形")).FillAccrossSheets _
      wb.Worksheets("雛形").Range("A1:L10"), xlfillwithformats

    ' 先頭シートをすべてのシートにコピー
    wb.Worksheets.FillAcrossSheets wb.Worksheets(1).UsedRange

関連記事

www.shegolab.jp