エクセルの複数シートで支払調書を作成したい

質問

今期の決算で配当金を出すことになり
300名近い株主の方へ支払調書を作成せねばならなくなりました。

前期までは手書きで出していたのですが、
エクセルでなんとかならないものでしょうか？？

支払調書はエクセルで作成してあります。

フォーマットはこんな感じで、全員分印刷が必要です。
http://www.nta.go.jp/category/yousiki/soukatu/pdf/23100023-2.pdf

株主のデータは別のシートで名前・住所・配当金額をリストにしてあります。

ワードは苦手なのでエクセルでなんとかしたいのです。
リストのシートをを参照させて、別シートの支払調書にデータが
全部入るような感じで簡単に全員分作る方法がないでしょうか。

よろしくお願い致します。

回答No.3

ham_kamo · ベストアンサー

最初のマクロを機能強化してみました。
実行すると、１枚ずつプレビューして、プレビュー画面で「印刷」か「閉じる」を押すと、プレビューを続けるかどうかダイアログで聞いてきます。
「はい」を押すとプレビュー続行、「いいえ」を押すとその後は全てを自動的に印刷、「キャンセル」を押すと処理を中断します。
ついでにダイアログのタイトルに処理件数を表示しています。
これでいかがでしょうか？

Sub PrintAll()
　　Dim R As Range
　　Dim S As Worksheet
　　Dim a As Integer
　　Dim Num As Integer
　　Dim IsPreview As Boolean

　　Num = 1
　　IsPreview = True
　　Set S = Worksheets("株主データ")

　　For Each R In S.Range("A2", S.Range("A2").End(xlDown))
　　　　With Worksheets("支払調書")
　　　　　　'以下、A1、A2、A3はそれぞれ名前、住所、配当金額が入るセルに置きかえる
　　　　　　.Range("A1").Value = R.Value
　　　　　　.Range("A2").Value = R.Offset(0, 1).Value
　　　　　　.Range("A3").Value = R.Offset(0, 2).Value
　　　　　　.Calculate
　　　　　　.PrintOut Preview:=IsPreview
　　　　　　If IsPreview Then
　　　　　　　　a = MsgBox("プレビューを続けますか？" & vbCr & vbCr & _
　　　　　　　　　　　　 "はい　　 ->プレビュー続行" & vbCr & _
　　　　　　　　　　　　 "いいえ　　 ->以降を全て自動的に印刷" & vbCr & _
　　　　　　　　　　　　 "キャンセル ->処理中断", vbQuestion + vbYesNoCancel, _
　　　　　　　　　　　　 Num & "件目完了")
　　　　　　　　If a = vbNo Then
　　　　　　　　　　IsPreview = False
　　　　　　　　ElseIf a = vbCancel Then
　　　　　　　　　　Exit Sub
　　　　　　　　End If
　　　　　　End If
　　　　End With
　　　　Num = Num + 1
　　Next
End Sub

お礼

ham_kamoさん

できました！
完璧です。
どうもありがとうございました。

これを機にマクロ勉強してみようかと思います。

本当にお世話になりました。
ありがとうございました。

回答No.2

ham_kamo

プレビューは…、確かに終わりませんね、これでは。(^^;
自分で試したときはデータを３件しか入れてなかったので気がつきませんでした。

印刷用のマクロをコピーして、最初の一つだけプレビューするように修正してマクロを追加してみました。先のマクロと同様にＡ１～Ａ３のセルは正しいものに置きかえてください。

本当は共通な処理をする箇所は、サブプロシージャとして外に出した方がいいのですが、今回はまあ手抜きということで。

Sub PreviewOne()
　　Dim R As Range
　　Set R = Worksheets("株主データ").Range("A2")
　　With Worksheets("支払調書")
　　　　'以下、A1、A2、A3はそれぞれ名前、住所、配当金額が入るセルに置きかえる
　　　　.Range("A1").Value = R.Value
　　　　.Range("A2").Value = R.Offset(0, 1).Value
　　　　.Range("A3").Value = R.Offset(0, 2).Value
　　　　.Calculate
　　　　.PrintPreview
　　End With
End Sub

補足

ham_kamoさん　ありがとうございます！

できましたっ！！！！！　嬉しいです。

何度も申し訳ないのですが、もうひとつだけ教えてください。

1枚1枚プレビューしながら印刷するには
マクロをどのように訂正したらよろしいのでしょうか。
初めのマクロと2度目のマクロを見比べて色々試してみたのですが
どうもうまくいきませんでした。

もう一度だけ助けてください。
お願い致します。

回答No.1

ham_kamo

自動的にリストから値をセットしてプリントするマクロを組んでみました。

動作条件として、支払調書のフォーマットのシート名を「支払調書」、株主データのシート名を「株主データ」としており、株主データのリストが

　　　Ａ列　　Ｂ列　　　　　　Ｃ列
１行　名前　　住所　　　　　　配当金額
２行　山田　　東京都…　　　　1,000
３行　田中　　神奈川県…　　　2,000

のように、Ａ列に名前、Ｂ列に住所、Ｃ列に配当金額が入っていて、最初の行はタイトル行だと仮定しています。

また、支払調書のシートのA1、A2、A3にそれぞれ名前、住所、配当金額が入ると仮定します。（これはぜんぜん違うと思いますが）その他計算で求められるセルにも計算式が入っているものとします。

Alt+F11でVBAの編集画面を開き、ThisWorkbookをダブルクリックして開いて右に以下をコピーします。

Sub PrintAll()
　　Dim R As Range
　　Dim S As Worksheet
　　Set S = Worksheets("株主データ")
　　For Each R In S.Range("A2", S.Range("A2").End(xlDown))
　　　　With Worksheets("支払調書")
　　　　　　'以下、A1、A2、A3はそれぞれ名前、住所、配当金額が入るセルに置きかえる
　　　　　　.Range("A1").Value = R.Value
　　　　　　.Range("A2").Value = R.Offset(0, 1).Value
　　　　　　.Range("A3").Value = R.Offset(0, 2).Value
　　　　　　.Calculate
　　　　　　.PrintPreview
　　　　　　'一括で印刷したいときは上の行の先頭に'をつけてコメントアウトし、
　　　　　　'下の行の先頭の'を取り除く
　　　　　　'.PrintOut
　　　　End With
　　Next
End Sub

VBAの中のA1、A2、A3は実際に名前、住所、配当金額が入るセルに置きかえてください。

これを実行すると、支払調書のフォーマットに順番に名前と住所と配当金額をセットし、シートの再計算を行って印刷プレビュー画面を出します。間違ってなかったら「印刷」ボタンで印刷し、自動的に次の人のプレビュー画面が出ます。

何人分かプレビュー画面で確認し、大丈夫だったら、上のリストの文中にあるように.PrintPreviewを.PrintOutに切り替えてください。自動的に全員分の印刷を行います。

不明な点や、うまくいかない場合は補足してください。

補足

ham_kamoさん

ありがとうございます！！
早速試してみたところ、うまくいきそうです。

今　セルの選択をを間違えたので直そうとしております。
プレビューで確認して間違えていたので直したいのですが
プレビューは一人分だけ確認することはできないのでしょうか？
まだ配当金額が決まっていないので、印刷はせずに
準備だけしておきたいのです。
プレビューを途中で終らせる方法があれば教えていただけないでしょうか？

何度もすみません。
よろしくお願い致します。