100行ごとにデータ分割して別シートへコピー

大量のデータを指定行数ごとに分割して別シートに分ける作業をします。

リンクユニット


100行ごとに別シートへコピー Excel2013 マクロ講座 75回

指定行数ごとに別シートにコピーする

今日、紹介するのは、データベースのように大量のデータが記載されているシートを、指定数ごと(仮に100行ごと)に分割して別シートにコピーするマクロです。
データがあまりにも大量にあると、シートも長くし、とても見づらくなりますね。
サンプルでは500件のデータを100件ごとに分割して、同じブック内の別シートにコピーするというマクロを作ります。
(サンプルファイルは、こちらから マクロ75回サンプルデータ

膨大なデータの分割、タイトル行なしの場合

下図のようにタイトル行がない場合は、100行ごとに区切ってコピーする場合のコピー回数は、総行数を100で割れば求められます。 もし余りが1行でも出たら、コピー回数に+1します。
100行ごとに別シートへコピーマクロ1
通常、場合分けをしたい場合、If~Then~Elseステートメントを使いますが、今回はコードを簡略にしたいのでInt関数とIIf関数を使うことにしましょう。
Int 関数は、引数に指定した数値の整数部を返すので、Int(データの総行数 / コピー行)で、コピーする回数を求めることができます。さらに IIf関数では、行数にあまりが出た場合に、コピー回数を1回増やすという処理をさせます。
100行ごとに別シートへコピーInt 関数
100行ごとに別シートへコピーIIf 関数
Int関数とIIf関数を使って、操作する回数を求めるコードを作ります。 変数としては、総行数、回数が必要です。 また分割する行数をコピー行と名前で宣言していますが、これは定数ですのでDimステートメントではなく、Constステートメントで宣言しています。 コピー行の数値を変更することで汎用的に利用できます。
Dim 総行数 As Long, 回数 As Long
Const コピー行 = 100
回数 = Int(総行数 / コピー行) + IIf(総行数 Mod コピー行 > 0, 1, 0)

上記コードでコピー回数がわかりますから、あとはワークシートを追加Sheets.Addして、それぞれのシートにデータ元があるシートからコピーして貼り付け にはCopyメソッドを利用すればいいのです。
100行ごとに別シートへコピーマクロ4

100行ごとに追加シートへコピー

コピー回数分処理を繰り返すには、For~Nextステートメントを使います。
Sub データを100行ごとに分割する()
    Dim シート As Worksheet, 元 As Worksheet '元は元データのあるシート
    Dim 総行数 As Long, 回数 As Long, i As Long, 開始行 As Long
    Const コピー行 = 100
    Set 元 = ActiveSheet '変数の元をActiveSheetにセットする
    総行数 = 元.UsedRange.Rows.Count
    回数 = Int(総行数 / コピー行) + IIf(総行数 Mod コピー行 > 0, 1, 0)
    開始行 = 1
    For i = 1 To 回数
        Set シート = Sheets.Add
        シート.Name = 開始行 & "~" & 開始行 + コピー行 - 1
        元.Rows(開始行 & ":" & 開始行 + コピー行 - 1).Copy シート.Range("A1") 
        Columns("A:F").AutoFit
        開始行 = 開始行 + コピー行
    Next i
End Sub
指定行数をコピーして新規シートに貼り付けるマクロの結果です。
100行ごとに別シートへコピーマクロ5
実務では、データを分割して複数シートに分けてチェックするなどということも、けっこうあるので便利なマクロだと思います。
次は、データの1行めにタイトル(見出し)行があり、それを分割したシートにもそれぞれコピーして表示させたい時はどうするかというマクロコードです。
スポンサーリンク
スポンサーリンク
おすすめコンテンツ