至急なんです～(>_<)！エクセル差込印刷（連番とんでる編）

質問

過去の質問で差込印刷などの要領はわかったのですが、同じような質問がないのでこちらから質問させていただきます。

sheet1に元データ
sheet2が帳票用（印刷用）となってます。

sheet1のA列に連番が入ってますが、
こちらはA1＝1、A2＝空欄、A3＝2、A4＝3　などとランダムな行にに連番が入ってます。

B列に会員番号が入ってます。

なのでＡ列の連番の1.2.3.4.5.6のすぐ右横のセルＢ列（会員番号）をsheet2のA1セルに入れて連続印刷していきたいのです。

2500人ほどの会員への手紙を明日中に発送しなくちゃいけなくて困ってます。

ちなみに「sheet2」は「A1」のセルにさえ会員番号が入れば出来上がるようになってます。

どうかわかる方がいましたらお力添え宜しくお願いいたします。

回答No.2

KenKen_SP · ベストアンサー

こんにちは。KenKen_SP です。

＞2500人ほどの会員への手紙を明日中に発送しなくちゃいけなくて困ってます。

そりゃ大変だ。取り急ぎですが、、

Sub Sample()

　　Dim Sh1 As Worksheet, Sh2 As Worksheet
　　Dim rngCel As Range
　　Dim rngDat As Range

　　Set Sh1 = ThisWorkbook.Sheets("Sheet1") '元データシート
　　Set Sh2 = ThisWorkbook.Sheets("Sheet2") '印刷用シート

　　'Sheet1 の A列のデータ範囲を取得
　　Set rngDat = Intersect(Sh1.Columns(1), Sh1.UsedRange)
　　If rngDat Is Nothing Then
　　　　Exit Sub
　　End If

　　'Sheet1 のA1～A列最終セルまでループ
　　For Each rngCel In rngDat
　　　　'A列が空なら処理しない
　　　　If Not IsEmpty(rngCel) Then
　　　　　　'Sheet2 の A1 に会員番号を転記
　　　　　　Sh2.Range("A1").Value = rngCel.Offset(0, 1).Value
　　　　　　'印刷
　　　　　　Sh2.PrintOut
　　　　End If
　　Next rngCel

　　Set rngCel = Nothing: Set rngDat = Nothing
　　Set Sh1 = Nothing: Set Sh2 = Nothing

End Sub

補足

早々の回答ありがとうございます。
すばらしいですね！いつも頭のなかがすっきりと整頓されているのだなぁっと感心してしましました。

これでいけそうなのですが、実際はsheet1のＡ１じゃなくてE1に連番、F1に会員番号なのですがその場合は
→Sh1.Columns(1)を変えればよろしいのでしょうか？

色々変えて試したのですが動かなかったので・・・
宜しくお願いいたします。

回答No.5

zenjee

＞連番のパターンは2になっております。

連番になっていれば並べ替えする必要も列挿入する必要もなく、そのままSheet2のA列に一連番号を振ることによって同じ作業を行えば一連番号に応じた会員番号がすべて自動的に入力されるので、問題なく処理可能と思います。

回答No.4

KenKen_SP

#2 です。

> ...実際はsheet1のＡ１じゃなくてE1に連番、F1に会員番号なのですが
> その場合は→Sh1.Columns(1)を変えればよろしいのでしょうか？

E 列は列番号で言えば A 列 から数えて 5 番目なので、

Sh1.Columns(5)

に変更して下さい。うまくいくといいですね。
頑張ってください。

お礼

できました(>_<)すご～い！！！
去年一枚ずつ印刷したのが馬鹿のようです！（ちなみに1週間かかりましたが、これなら半日でできました）
すばらしいです。これを他の帳票のも応用すれば、今の仕事量の半分以下になりそうです。本当にありがとうございました。

回答No.3

zenjee

＞sheet1のA列に連番が入ってますが、
こちらはA1＝1、A2＝空欄、A3＝2、A4＝3　などとランダムな行にに連番が入ってます。

１、A列の空欄を除いた会員へ発送するということでしょうか。
２、またSheet1,A列の番号は空欄を除けば一連番号になっているのでしょうか。

その辺がいまいち分かりませんが、１はそのとおり、２は連番になっていないと仮定して、VBAによらない次の方法はいかがでしょうか。

まずSheet1のA列を降順に並べ替え、一旦、発送分だけの表にまとめます。

ABの間に作業列を挿入し、1を起点とする一連番号を振ります。

Sheet2も最左列に作業列を挿入し、A1に「1」を入力します。

A1を検索値としてB1に変更された会員番号のセルに次の式を入力します。
=VLOOKUP（B1，Sheet1!B:C，2，FALSE）

この式を、Sheet2の帳票の2葉目のセル（例えばB21とします）にコピーするとともに、A21に
=A1+1
と入力します。

A21:B40をコピー、A41以下必要な行までペーストします。
すると会員番号が全部に入る筈なので、印刷範囲を１葉毎になるよう設定して（20行毎に改ページになるよう）印刷すればＯＫでしょう。

もちろん他の必要データは会員番号が入ればVLOOKUP関数等で自動的に反映されるようになっているとの前提です。

補足

早々のお返事ありがとうございます。
連番のパターンは2になっております。
並び替えをするとくずれてしまうような数式が入っていて並び替え不可能なのです。
ＶＢＡの初歩的な知識ならあります。
でも光が見えてきましたありがとうございます。

回答No.1

papayuka

エクセルだけで差込印刷をするのですか？
VBAを書かないと出来ないと思いますけど、、、

ワードと併用して、ワードの差込みでエクセルファイルを指定した方が簡単ですよ。

お礼

ありがとうございます。ＶＢＡ苦手なんですよね・・・・
数式がからみあってて、ワードでは無理なようです。
ご心配ありがとうございます(*^_^*)