エクセル ＶＢＡ 水平改ページ位置の変更方法について教えてください。

質問

おはようございます。いつも大変お世話になっています。

エクセルで、ある元のシートから、ある条件で抽出して新しいシートを作りました。
その新しいシートの印刷指定方法について教えてください。

最初だけ５３行、その後５０行づつ区切って印刷したいため、以下のようなＶＢＡを書いてみました。

ActiveWindow.View = xlPageBreakPreview
Set ActiveSheet.HPageBreaks(1).Location = Range("a53")
Set ActiveSheet.HPageBreaks(2).Location = Range("a103")
Set ActiveSheet.HPageBreaks(3).Location = Range("a153")
Set ActiveSheet.HPageBreaks(4).Location = Range("a203")
Set ActiveSheet.HPageBreaks(5).Location = Range("a253")
・・・・・・

が、元のシートのデータが、日によって変動するため、新しいシートのデータが全部で１２０行位で終わってしまう場合があります。
その場合、１００以上の指定していた部分から、
「インデックスが有効範囲にありません」と、止まってしまうのです。

日によってデータの行数は変わりますが、多い場合も同じように５０行区切りで印刷したい場合には、どのようにしたら良いでしょうか？

どうぞよろしくお願いします。

回答No.4

hana-hana3 · ベストアンサー

＞どうしたら良いのでしょうか？

印刷は実行していませんが、改ページプレビューで確認しても、指定位置できちんと動作していますが・・・。

実行するときに対象のシートがアクティブ（前面）になっていますか？
そうで無いなら、ActiveSheet を Sheets("Sheet2") など書き換えてテストしてみてください。

お礼

できました！
すごくうれしいです。

何度も丁寧に教えていただいてすみませんでした。
ありがとうございました。

回答No.3

hana-hana3

＞以下のように入れてみましたが、印刷されません。

下記で問題無いかと思います。
データが無くてもエラーが出ません。
-----------------------------------------
'改ページ設定解除
ActiveSheet.ResetAllPageBreaks
'改ページ設定
ActiveSheet.HPageBreaks.Add Before:=Range("a53")
ActiveSheet.HPageBreaks.Add Before:=Range("a103")
ActiveSheet.HPageBreaks.Add Before:=Range("a153")
ActiveSheet.HPageBreaks.Add Before:=Range("a203")
ActiveSheet.HPageBreaks.Add Before:=Range("a253")
ActiveSheet.HPageBreaks.Add Before:=Range("a303")
'印刷プレビュー
ActiveSheet.PrintPreview
--------------------------------

ActiveWindow.～　のプロパティでエラーになっていた感じですね。（ActiveSheet.～に変更）

お礼

ご回答ありがとうございます。

何度も申し訳ないのですが、再度教えてください。

Ｎｏ.3のように、入れてみたのですが
印刷すると設定した水平改ページが、まったく反映されないのです。改ページ設定をしない前と、まったく同じ物が印刷されてしまうのです。

hana-hana3さんが教えてくださったＶＢＡに、
ActiveSheet.Printout
とだけ、追加したのですが・・・。

どうしたら良いのでしょうか？

甘えて申し訳ありませんが、教えてください。

回答No.2

hana-hana3

＞５０行区切りで印刷したい場合には

シートの印刷設定で５０行が印刷されるように設定したらどうですか？
これなら、自動的に対応してくれます。
記録マクロを使って印刷設定を呼び出すだだけで、簡単に殆どの印刷設定を取得できますから、実行時にこの設定を呼び出すだけです。

＞元のシートのデータが、日によって変動するため

データ数をチェックして行うのがベストですが、安直に済ませるなら、On Error Resume Next の一文をマクロの途中（エラーが予測される処理の開始前：今回は Set ActiveSheet.HPageBreaks(1) の前）に入れるとエラーを無視（本来はマクロ中でエラー対処が必要）する事ができます。

http://t_shun.at.infoseek.co.jp/My_Page/Excel-VBA/vba_page21.htm

お礼

ご回答ありがとございます。
さっそく２番目の方法でやってみました。
以下のように入れてみましたが、印刷されません。

ActiveWindow.View = xlPageBreakPreview

On Error Resume Next

Set ActiveSheet.HPageBreaks(1).Location = Range("a53")
Set ActiveSheet.HPageBreaks(2).Location = Range("a103")
Set ActiveSheet.HPageBreaks(3).Location = Range("a153")
Set ActiveSheet.HPageBreaks(4).Location = Range("a203")
Set ActiveSheet.HPageBreaks(5).Location = Range("a253")
・・・・・・

ActiveWindow.PrintOut

印刷させるためにはどうしたら良いのですか？
重ね重ね申し訳ないですが、教えてください。

回答No.1

merlionXX

> 日によってデータの行数は変わりますが、多い場合も同じように５０行区切りで印刷したい場合には、どのようにしたら良いでしょうか？

orennji1111さん、こんにちは。
その日のデータの最下行を調べ、53行を超える部分に50行がいくつあるかを計算してHPageBreaksを挿入するようにしたらどうでしょう？
こんな感じです。

Sub test()
ActiveWindow.View = xlPageBreakPreview
Set Rng = ActiveSheet.UsedRange'使用範囲設定
x = Rng(Rng.Count).Row'最終行
If x > 53 Then
Set ActiveSheet.HPageBreaks(1).Location = Range("A53")
b = (x - 53) / 50
If b > 1 Then
For i = 1 To b
Set ActiveSheet.HPageBreaks(i + 1).Location = Range("A" & 53 + 50 * i)
Next
End If
End If
End Sub