【ＶＢＡ】飛び飛びのセルにデータを入力する方法

Question

Excel2010 ＶＢＡで質問です。
抽象的な話になります。
転記について件のですが、
生徒カードを自動で作ろうとして困っています。
シート1にデータ名簿
列の項目として
①生徒番号
②氏名
③組
④住所
⑤保護者
⑥学校名
があり現在は6名いるため、６行ですが、次第に増えていくものとします。(最終行の取得)

シート1の生徒の情報をシート2に転記します。
シート2
ＢとＥ列がセルが大きくなり、こちらに転記
sheet２の２行Ｂ列一人目の生徒一人分sheet1①～⑥を縦に６行分転記→sheet２の２行Ｅ列に二人目の生徒一人分sheet1①～⑥を縦に６行分転記
一行空けてsheet２の9行Ｂ列3人目の生徒一人分sheet1①～⑥を縦に６行分転記
・・・・のように１人目→2一人目　改行　3人目→4人目　改行　5人目→6人目・・・というように転記したいと思います。
1sheet16カードで17カード目はsheet2→3へ転記
と生徒カードを自動生成したんですが、
sheet2の2行目(1列カード目)が終わった後の9行目(2列カード目)にいく手立てがよくわかっていません。
変数、for文、if文を織り交ぜるとよくわからなくなってしまいます。
なにかヒントとか頂けないでしょうか？？

Comments

[sazi]

文章ではよく分かりませんね。excelで完成形を作成してそのイメージを追加されてはどうでしょう

[ksk_2200]

お返事ありがとうございます。アバウトになりますが、イメージ図を記載したので拝見お願いします。

[dit.]

Excel VBAで　という趣旨からは離れてしまうのでこちらに書きます。もし「その状態を印刷したい」くらいの用途でしたらExcelで作ったリストを基にWordの差し込み印刷（ラベル）を使うというのも一つの手だと思います。https://www.wanichan.com/pc/word/2010/09/　「生徒カード」を作成した後どのような利用をするかわかりませんが、ご参考まで

Answer 1

私がするなら、という方法を。

まず、sheet1 の表は、テーブルにします。
「1行目は項目名にする」にチェックを入れておきます。
テーブル名は「生徒リスト」等、分かりやすいものにします。

テーブルにしておくと、データ（行）が増えたり、項目（列）が増えても、
自動的に拡張されるのでレンジ範囲を意識する必要がなくなります。
VBAでアクセスするときは、ListObjectにセットしてデータに簡単にアクセスできます。
(例えば、For Each で各行にアクセスできます。)

生徒データのカードへの転記ですが、横並びを縦並びに変換するだけですので、WorksheetFunction.Transpose関数で縦横変換して、代入すれば簡単にできます。

上記を考慮してコーディングすると下記のようになります。

Sub カード作成()
    Dim Lst As ListObject
    Set Lst = Sheet1.ListObjects("生徒リスト")
    
    Dim ColCount As Long
    ColCount = Lst.ListColumns.Count '項目数（列数）
    
    Dim CardRng As Range
    Set CardRng = Sheet2.Range("B2").Resize(ColCount, 1) ’転記先の最初のカードの範囲
    
    Dim rowdata As ListRow
    For Each rowdata In Lst.ListRows 'テーブルの各行
        CardRng.Value = _
            WorksheetFunction.Transpose(rowdata.Range.Value) '縦横変換してカードに代入
        If rowdata.Index Mod 2 = 1 Then '奇数行なら、
            Set CardRng = CardRng.Offset(, 2) 'カード範囲を右に２つ移動
        Else                            '偶数行なら
            Set CardRng = CardRng.Offset(ColCount + 1, -2) '項目数+1下に移動して、左に２つ移動
        End If
    Next
End Sub

こうすれば、
行が増えたり、列が増えても、コードを変更する必要はありません。

1sheet16カードで17カード目はsheet2→3へ転記
と生徒カードを自動生成したんですが、

の部分は考慮してませんが、
If rowdata.Index Mod 16 = 0 Thenで判断して、
WorkSheets.Add でシートを追加するというコードを追加することになりますね。
シート名はどうするか、既存のシートはどうするか、などの使用が不明なので，
コードは提示できませんので、ご自身でトライして、うまくいかないようなら、
また質問しなおしてください。

Answer 2

私がよくやる手段としては、

1. 転記先のレンジの左上を基底セルとして変数に保存しておき
2. データを転記する際に、その基底セルから何列右、何行下をoffsetで指定する

という方法を使っています。
今回のケースならそれぞれのカードの左上の位置は計算できると思います。
1カード目の左上がB2、2カード目がE2ならrange.offset(0,3)で３列分横へ移動できます。
3カード目がB9なら、B2からoffset(7,0)、4カード目はoffset(7,3)でしょうか。
一般化するとxカード目のオフセットは、
range("B2").offset( ((x-1)/2)*7 , (x mod 2)*3 )
基底のセルさえセットできれば各カードへのデータセットはできると思います。
あとは頑張ってください。