爆速で終わらせる配列の転記について

マクロ講座 動画編 106回後編

動画でExcel　なぜ配列をつかうのか、爆速で終わらせる配列コード編つづき

爆速で転記、データを配列で取得して別ブックのシートに転記する

動画版「マクロ講座１０６回後編」です。
前回は配列が100倍速く処理をするということをタイマーを使って実証し、そのコードを解説しました。
今回は、その配列を使って、取得したデータを別シートや別ブックに転記する例です。

この記事で学べる内容

• Excel マクロでのデータ処理を高速化するために配列を使用する方法。
• 配列を使用して同じブック内のシート間でデータを転送する方法。
• 配列を使用してワークブック間でデータを転送する方法。
• 配列データ転送用の変数を設定および宣言する方法。
• 配列を使用して転送するデータの範囲を決定する方法。

爆速で転記、データを配列で取得して別ブックのシートに転記する

配列で元データを取得する

1. 配列で取得するデータの範囲です。入力
   しているシートは Sh_data、転機するシートはSh_otherという 名前をつけています。
   そして 別ブックは、”別ブック.xlsx” という名前を付けてい ます。

   

2. 今回使う変数をあらかじめ設定して宣言、セクションに記載しておきます。
   配列として取得するデータを入れる変数は、myArrayという名前にして、Variant型で宣言します。

   

3. 行数と列数を取得するために、MaxRow、MaxColを用意しました。
   Dim myArray, sh As Worksheet
   Dim MaxRow As Long
   Dim MaxCol As Long
   

   

4. 一番簡単な範囲の取得の仕方は、CurrentRegionです。 それで、CurrentRegionでコードを書いていき ましょう。
   これでもう配列を取得できます。

   

   Public Sub 配列で取得するCurrentRegion()
       Set sh = Worksheets("sh_data")
       myArray = sh.Range("A1").CurrentRegion
   End Sub

5. マクロを実行して、配列がどのように取得されたのかを見てみましょう。
   これがウォッチウィンドーに表示された配列の様子です。

   

別シートを設定する

6. では、データを取得できたので、次にsheetに転記しましょう。 Dim sh_other As Worksheet
   Set sh_other = Worksheets("sh_other")

   

7. アクティブなシート以外に転記する場合は、しっかりとその親オブジェクトから上下関係を指定する必要があります。住所と同じです。

   

8. 別シートの転記先は、Rangeで範囲をきちんと指定します。その際にMaxRow、MaxColを使います。
   転記先の範囲を、転記元のMyArrayと同じサイズにしてやります。ここがちょっと難関なんです。
   MaxRowもMaxColもまだ値が入っていませんので、値を取得します。

   

9. MaxRowの行数は、データのあるシートの行数を数えて、一番下に降りて行った場所から、データのあるところまで飛んできた、その行という意味で、
   MaxRow = sh.Cells(Rows.Count, 1).End(xlUp).Row
   そして、MaxColも考え方は同じで、
   MaxCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
   で値を取得できます。

10. 転記元からデータは取得できていますので、転記先の範囲が決まれば、データを転記することができるのです。

    Public Sub 配列で取得OtherSheet()
    Dim myArray, sh As Worksheet
    Dim MaxRow As Long
    Dim MaxCol As Long
    
        Set sh = Worksheets("sh_data")
        myArray = sh.Range("A1").CurrentRegion
        
        MaxRow = sh.Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
        MaxCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
        Dim sh_other As Worksheet
        Set sh_other = Worksheets("sh_other")
        
        sh_other.Range(Cells(1, 1), Cells(MaxRow, MaxCol)) = myArray
    
    End Sub

配列で別ブックへの転記

11. 別シートへの転記がわかるように、ウィンドウをもう１つ表示させておきます。この状態でマクロを実行してみましょう。

    

12. 後ろのシートがデータのあるシート、手前のシートが転記先のシートです。

    

13. 今度は別ブックにコピーして転記しましょう。
    別ブックという名前のブックを用意しています。
    そしてこのブックはすでにOpenしてあるんです。
    それで転記先が、別ブックのシートだとわかるように、以下のコードのようにSetしてください。

        Dim sh_other As Worksheet
        Set sh_other = Workbooks("別ブック.xlsx").Worksheets("sh_other")
    

14. 別シートとの違いは、頭に Workbooks("別ブック.xlsx").という親オブジェクトをつけただけですね。

    

15. マクロを実行すると、別ブックに転記されました。

    

別ブックへをOPENしてからの転記

1. 別ブックを開いて転記する場合は、
   Workbooks.Open Filename:="ファイルのフルパス"で指定します。
   Dドライブの『顧客データ』というフォルダであれば、
   

     Workbooks.Open Filename:="D:\顧客データ\別ブック.xlsx"

   その開いたブックに対して先ほどと同じ以下のコードを記述します。

       Dim sh_other As Worksheet
       Set sh_other = Workbooks("別ブック.xlsx").Worksheets("sh_other")
   

2. 先に転記するワークブックを開いてから、別シートを宣言して、セットしてやっています。
   次にそこへデータ元のから取得した配列を転記するという流れです。

   Public Sub 配列で取得別ブックOpen_OtherSheet()
       Dim myArray, sh As Worksheet
       Dim MaxRow As Long
       Dim MaxCol As Long
       Set sh = Worksheets("sh_data")
       myArray = sh.Range("A1").CurrentRegion
       Workbooks.Open Filename:="D:\顧客データ\別ブック.xlsx"
       Dim sh_other As Worksheet
       Set sh_other = Workbooks("別ブック.xlsx").Worksheets("sh_other")
       
       sh_other.Range(Cells(1, 1), Cells(MaxRow, MaxCol)) = myArray
       Workbooks("別ブック.xlsx").Close SaveChanges:=True
   End Sub

   最後のコードは、ブックを閉じるコードですが、引数に保存して閉じるを指定しています。
   Workbook.Close メソッドを使っています。