表を配列として簡単に利用する マクロ講座

表を配列として簡単に利用する

シートの表を配列として簡単に利用する
表を配列として簡単に利用する

マクロ 87回

利用したいデータが表として存在している場合

エクセルでは、利用したいデータが表として存在している場合がほとんどです。
シートにある支店名でシートを作成したいケースを考えてみましょう。

(サンプルファイルは、こちらです。 セル範囲を配列として取得して、使うにはどうするか、サンプル101回

こちらは、動画にもしていますので、動画も参考にしてください。

シートの表を配列として取得する

1. どのような作業をしたいのかというと、シートを支店名で作成し、そのシートの見出しとして、A1セルに支店名、隣のセルに支店長を入力するという作業です。


表を配列として簡単に利用する1

2.Alt + F11で Visual Basic Editorを開きます。


表を配列として簡単に利用する2

3. 配列として利用したい範囲にはbranchArr という変数名を使い、Variant型で宣言することにします。
また、シート名をshlistという名前に変更してあります。

Sub getrange_addsheet()
    Dim branchArr As Variant
    branchArr = shlist.Range("A1").CurrentRegion
End Sub

表を配列として簡単に利用する3

4. そしてそのbranchArrは、
シート名shlistのRange("A1")
現在の範囲=CurrentRegionですよと、
指定します。
続いて、支店の数だけシートをFor文で追加していきます。変数として今回は行番号の意味でrを使うことにします。(もちろん i でもいいです。)


表を配列として簡単に利用する4
スポンサーリンク
スポンサーリンク

配列の範囲を指定するだけで配列を取得する

5. さらに変数を追加します。新しいブックを作成するので、newbook 、新規シートを追加するのでnewsh を変数として追加します。


表を配列として簡単に利用する5

6.あとは、 すでにプロシージャで配列を指定しているので、実行時にはエクセルが配列を持っていますから、 新しいシートの名前として newsh.Name = branchArr(r, 1)
さらに、A1セルに branchArr(r, 1)
つづいてB1セルに branchArr(r, 2)
と指定するだけで、支店名のシートを作成することができます。


表を配列として簡単に利用する6

7. コードは以下のようになります。

Sub getrange_addsheet()
    Dim branchArr As Variant
    branchArr = shlist.Range("A1").CurrentRegion
    Dim r As Long, newbook As Workbook, newsh As Worksheet
    Set newbook = Workbooks.Add
    For r = 2 To 10
        Set newsh = newbook.Worksheets.Add
        newsh.Name = branchArr(r, 1)
        newsh.Range("A1").Value = branchArr(r, 1)
        newsh.Range("B1").Value = branchArr(r, 2)
    Next r
End Sub

表を配列として簡単に利用する7

8. もしワークシートを右側に追加したい場合は、Addの引数にAfterを使い、コードを以下のように変更します。

Set newsh = newbook.Worksheets.Add After:=Worksheets(Worksheets.Count)

表を配列として簡単に利用する8
こちらは、動画にもしていますので、動画も参考にしてください。