エクセルのマクロについてです。 作成者がやめてしまい、マクロができる人がおらず困っています。 画像の

エクセルのマクロについてです。
作成者がやめてしまい、マクロができる人がおらず困っています。
画像のようなエクセルのリスト(これはシート2とします。)を使用しており、別のシステムからコピペしたデータをシート1に貼り付けてシート3に必要な情報のみを抽出(VLOOKUP関数仕様)した表を作成し、シート2の表のセル(=シート3!B7の式をいれてます)に反映させて下の行にドラッグしています。
リスト上のマクロボタンは、B7を基準にそこからD列までなのかはたまたI列までなのかは不明ですが、昇順になるようになっています。
それで昇順にし終わったらシートを印刷するといったマクロが組まれていました。
今回のことは、マクロを組まなくても出来ることではあり、作成者がいた時は大量に件数が無かったので改ページ挿入をいちいちしておりましたが、ここ最近大量に来る日もあるので印刷範囲を可変にして印刷出来るようになったらいいネと話が出ました。
今回載せた画像のリストは10件までを表示して作成していますが、本物のリストは1500件くらいまで対応出来るようになっています。

ここで質問です。
印刷範囲を1ページ35行ずつで、最終ページだけシート3で抽出したデータがシート2に反映されてる最終行まで【最終ページだけ33行まで反映されてれば33行まで】＋対象件数をきちんとコピペしてるか通番を見て確認するための1行【対象総件数が500件なら1番最後の行の通番が501になってるかで確認するため】)にしたいです。
また、1500件分に対応するように作っており、抽出したデータが500件ならば1000件分反映されないセルが残ってしまいます。
この1000件のうちの1行は必要なので残りの999件は印刷範囲に入って欲しくない場合、どうやってマクロを組めばいいでしょうか？
可能なことなのでしょうか？
手動で修正した方がこのマクロを考えるよりも遥かに楽なのは承知なのですが、もし出来るのであれば教えていただきたいです。

おわかりになる方、無知なこの私にお知恵をお貸しください。
今組まれてるマクロの文章は画像の通りです。
塗りつぶしてるのはシート名で全て同一のシートです。
今回載せたエクセルのリストはマクロを修正したいExcelファイルと同一のものではなく、このようなリストだということをお見せするために私物のパソコンで作成したものです。
抽出日付の列は表示形式が日付になっています。他は標準になっていたと思います。

質問者からの補足コメント

• 画像見にくくて申し訳ございません。
  こちらに見やすいの載せておきます。

  
    補足日時：2019/04/09 23:45

• お礼を書いた後に気付いてまたお礼をしようとしたらお聞き出来なくなったので補足の方に書かせていただきます。
  貼り付けるデータの数なのですが、日によってばらつきがあり、30件の時もあれば500件などあります。(前日の集計を翌日抽出してリストを作成して利用してます。)
  なので、データの入ってる行の一番下の行までを最下行というと最下行についてはバラツキがあるのですが。。。

    補足日時：2019/04/10 08:39

• お返事ありがとうございます。
  分かりにくくて申し訳ございません。
  はい、仰られた通りのエラーが入っております。
  教えてくださった関数はエラーが表示されてるセルに入力すればいいのでしょうか？
  また、1ページの印刷範囲を35行にして最後の行だけ最終行＋1行にしたいのですが、
  1ページを35行にするのはどうやればいいでしょうか？
  質問ばかりで本当に申し訳ございません。

    補足日時：2019/04/10 11:36

• ifの関数を使用してシート2で反映するデータがなくてエラーの出てるセルは空欄にはなりました。
  その後、教えて頂いたマクロを入れてみたのですがエラーが出てしまいました。
  あと家のパソコンで試して見てるので印刷はせずに印刷範囲がきちんと出来てるか見たいので印刷プレビューに変えてます。
  私が無知すぎるが故に色々教えて頂いてるのにも関わらず出来なくて申し訳ございません。

  
    補足日時：2019/04/10 20:44

• コピペの内容はこれで完全OKでしょうか？

  
    補足日時：2019/04/10 23:11

• 今教えて頂いたマクロを入れて行の高さが大きかったのか小さくしたら通番35行ずつで出来ました！(入力件数は105件まで)
  かつ、空欄の行もはいってはいたのですが、1ページは通番35まで2ページは通番70まで
  3ページは通番105までで4ページ目で空欄の106行目が入ってました。
  この後、200番目まで試しに入れたら空欄の200番目まで6枚目にプレビューされてきてしまいました。(最下行は201番目に当たる空欄になってます。)
  シート2のB列からD列にシート3のデータが反映されてるとこまで+通番だけが入った空欄の直下1行だけが欲しかったのでこれだと通番だけが入っているだけの必要のない行が沢山印刷されてしまうのでこれは不要なのですが、印刷範囲に入らなくなるでしょうか？
  多分私が最初1ページ35行数指定とか言ってたからこうなったんですよね(すみません)

  
    補足日時：2019/04/11 00:10

No.1 回答者： zongai 回答日時： 2019/04/10 00:40

余計な行を非表示にすることで、印刷されないようにしてみます。

画像のコードの下の方の、
印刷部分「ActiveWindow.SelectedSheets.PrintOut ～」の前後に、下のように追加します。

----------
matubi = Cells(Rows().Count, 2).End(xlUp).Row
Rows(matubi + 2 & ":" & Rows().Count).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Rows(matubi + 2 & ":" & Rows().Count).EntireRow.Hidden = False
----------

1行目　B列（「2」番目の列）【※】で入力のある最下行を取得。
2行目　入力最下行から2行下の行から、シートの最終行までを非表示にする。
3~4行目　印刷。（元からあるもの）
5行目　2行目で非表示にした行を再表示する。

※ここは、最下行で確実に入力のある列番号を入れて下さい。C列なら「3」、D列なら「4」という具合で。

この回答へのお礼

お返事ありがとうございます！
また質問させて頂きたいのと
もう一度お聞きしちゃうことになるかと思うのですが、例えば抽出対象件数が500件でシート1で貼り付けたデータをシート3で必要なものだけ抽出してシート2に反映させるようにシート2の1500件分に対応したリストのB列からD列まではシート3のセルを参照する式が1500件分入ってます。
500件分反映されたとして501件からは参照するデータがないためエラーが表示されてるのですが、このエラーになる部分は非表示にしてくれるというマクロなのでしょうか？
またデータの表をあまり詰め込み過ぎると小さくて見えないという年配の方のために1ページを35行ずつに指定して自動印刷したいのですが、気が向いた時なんかで構いませんので教えていただきたいです。
お返事お待ちしております！
でも、こんな長い読む気の失せる質問にまず的確な回答を頂けたことに感謝しております！

お礼日時：2019/04/10 08:02

No.2 回答者： zongai 回答日時： 2019/04/10 09:49

補足コメントにつて。

> 30件の時もあれば500件などあります。
> データの入ってる行の一番下の行までを最下行というと最下行についてはバラツキがあるのですが。。。

matubi = Cells(Rows().Count, 2).End(xlUp).Row

これは、
・シートの2列めの最下行であるB1048576（バージョンに酔ってはB65535）のセルを選択
・[CTRL]+[↑]を押す
これで選択された列番号を取得するものになります。

[CTRL]+[↑]の操作についてはご存知でしょうか？
B1048576のセルから上に見ていって、最初にデータのあるセルに移動します。
空白行を無視して入力された最下行にたどり着けるというわけです。

なので、30件あろうと、500件あろうと、入力されている最下行を取得できます。

B列（2列目）にしているの理由は、
1列目は通し番号で、情報入力のない1500番（？）まで入力されていると思ったので、最下行取得に向かないと判断。
2列目は顧客番号なので、情報入力のある場合は確実に入力されていると判断したためです。


ところで、
実際に試して不具合が生じるということでしょうか？

No.3 回答者： zongai 回答日時： 2019/04/10 10:23

印刷するシート2は、入力がない行は空欄ではなくてシート3を参照している関数が入っているから動作がうまくいかないってことかな。

matubi = Cells(Rows().Count, 2).End(xlUp).Row
Do While WorksheetFunction.CountBlank(Cells(matubi, 2)) = 1
matubi = matubi - 1
Loop
Rows(matubi + 2 & ":" & Rows().Count).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Rows(matubi + 2 & ":" & Rows().Count).EntireRow.Hidden = False

Do～Loopで、空欄行を考慮して最下行を取得するようにしてみました。

とりあえずこれでお試しを。
実際のシートの状態がわからないので噛み合わない部分もあるかもしれません。

No.4 回答者： zongai 回答日時： 2019/04/10 10:58

> 500件分反映されたとして501件からは参照するデータがないためエラーが表示されてるのですが、このエラーになる部分は非表示にしてくれるというマクロなのでしょうか？

参照するデータが無いとエラーになるの？
どういうエラー？（「#N/A」？）参照元のシート3の時点でエラーになってるの？
（↑こういう情報も大事です）

このままだとNo.2の回答のは使えないかな・・・。

> シート2の表のセル(=シート3!B7の式をいれてます)に反映させて下の行にドラッグしています。

シート3のエラーを反映してきちゃっているなら、こういう式に置き換えるのもありかな。

=IF(ISERROR(シート３!B7),"",シート３!B7)

もし、シート3でエラーなら、空欄（””）。エラーでなければシート3の情報を反映。


質問や補足が、処理の流れだけ書いててわかりにくいです。
シート構成を明確に。
聞きたいことは完結に箇条書きに。
細かいことは更に補足すればわかりやすくなると思います。

例えば...

◼シート1　
別のシステムからデータをコピペ。
◼シート2
シート1の情報から必要な情報をVLOOKUP関数で抽出。
該当がない場合は「#N/A」になっています。（←こういう情報も）
◼シート3
印刷用のシート。
シート2の情報を「=シート3!B7」の様に直接参照しさせ、マクロで印刷させます。

★やりたいこと
データの最終行+1行までを印刷するようにしたい。

...とか。
細かく文章で書かれておられると思いますが、文章だけで書かれちゃうと把握し辛いです。

No.5 回答者： zongai 回答日時： 2019/04/10 12:13

> 教えてくださった関数はエラーが表示されてるセルに入力すればいいのでしょうか？

エラーが入ってるセルだけに関数を入れるなら、はじめからエラー選択肢て削除したほうが早いですが。
私の回答は、シート2で「=シート3!B7」のように書いてある全セルをイメージしてました。

補足コメントされるのはいいのですが、そのエラーが何なのかも補足すべきですよ。
ついでに、そのエラーに関しての問題が解消されたのかどうかも含めて。


エラーの問題解消済みとして・・・
35行区切りというのも漠然としてるので、勝手なイメージで作ってみました。
とりあえず確認してみて下さい。
（印刷範囲も指定するようにしたので、行の非表示処理は無くしました。）


Sub 昇順と印刷()
'
' 昇順と印刷 Macro
'

'
'最下行を取得するよ――――
matubi = Cells(Rows().Count, 2).End(xlUp).Row
Do While WorksheetFunction.CountBlank(Cells(matubi, 2)) = 1
matubi = matubi - 1
Loop
'印刷範囲を初期化するよ――――
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.PrintArea = ""
'印刷範囲を設定するよ（7行目 項目行は全ページに印刷）
With ActiveSheet.PageSetup
.PrintArea = "$A$7:$I$" & matubi + 1
.PrintTitleRows = "$7:$7"
End With
i = 8: j = 0
'ページの区切りを入れるよ――――
Do While i <= matubi + 1
i = i + 35: j = j + 1
ActiveSheet.HPageBreaks.Add before:=Cells(i, 1)
Loop
'印刷するよ――――
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

No.6 回答者： yoreyore 回答日時： 2019/04/10 12:17

脇から済みません。

１）現状は、Sheet2のページ設定のシートでタイトル行を[$1:$7]のようにされているのでしょうか。
2)現状では、Sheet2のページ設定の印刷範囲はどうなっているのでしょうか。
印刷しようとするときに、手動で印刷範囲を設定しているのでしょうか。
例えば、[A1:I501]のようにされているのでしょうか。
3)現状では、Sheet2のA列は、8行目～1500行目まで1からの通し番号を入れてあるのでしょうか。
4)現状では、Sheet2の[$B$8:$D$1500]あるいは[$B$8:$I$1500]に、Sheet3のセルを参照する式が入っているのでしょうか。
5)現状では、Sheet3のデータ数が400件の場合、Sheet2のB407はきちんとデータが表示されていて、B408は、＃N/Aのように(Sheet3で参照してるセルの表示と同じ)になっているのでしょうか。

もしも、3)4)5)がハイであって、【印刷は、各ページに1~7行目も印刷する】【最終ページは407行目(データの400件目)までで終わり、408行目以降はA列の遠し番号も印刷しない】ので構わないのであれば、次のようなコードでも、良いと思います。

Sub MacroSample()
Dim rng As Range
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not (rng Is Nothing) Then
r = rng.Row - 1
End If
Application.PrintCommunication = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & r
ActiveSheet.PageSetup.PrintTitleRows = "$1:$7"
ActiveSheet.ResetAllPageBreaks
For i = 43 To r Step 35
ActiveSheet.Rows(i).PageBreak = xlPageBreakManual
Next
Application.PrintCommunication = True
End Sub

上のマクロを、Sheet2をアクティブにしておいてから実行すると、エラーのあるセルの1行上の行までを、Ａ列～I列の範囲で、各印刷ページの上部に1~7行目をタイトルとして印刷する設定になります。
For i = 43 To r Step 35　は、印刷改ページ設定で、実質35行毎の改ページ、
第一ページが（タイトル7行＋35行＝42行)なので、最初の改ページが43行目の上で、
(実質35行)毎で、エラーのある行(r)の上で終わり

あとは印刷をします。

この回答へのお礼

いえいえ、とても助かります！

１）現状、タイトル行の指定はなしでした

2)仰る通りです。
今日の件数は、50件くらいでしたので改ページプレビューで印刷範囲を指定したので500までは範囲指定されてはいません。
いつも手動でやってるのでそのせいですね。

3)はい、そうです。

4)そうです！

5)仰る通りです。

通し番号を見てきちんと抽出対象件数分あることを確認したいので、通し番号は欲しいのですが。。

本当に申し訳ございません。
でも見つけて回答して下さり、ありがとうございます！
もしお時間あるなら通し番号アリでの方法が知りたいです。

お礼日時：2019/04/10 14:52

No.7 回答者： yoreyore 回答日時： 2019/04/10 18:00

＞　今日の件数は、50件くらいでしたので改ページプレビューで印刷範囲を指定したので500までは範囲指定されてはいません。

　いつも手動でやってるのでそのせいですね。

改ページプレビューでやってもいいのですが、だいたいが決まり切った様式で、意味のある有効行数が40,53,78,123,236,367,982,1357のようになるSheet2を、全部35行分/頁で印刷するor全部41行分/頁で印刷するという程度ならば、タイトル設定、改ページ設定をすれば、あとは、手動で[印刷]をすれば、全ページが連続して印刷されます。

＞　通し番号を見てきちんと抽出対象件数分あることを確認したいので、通し番号は欲しいのですが。。　通し番号アリでの方法が知りたいです。

3)現状では、Sheet2のA列は、8行目～1500行目まで1からの通し番号を入れてあるのでしょうか。
3)はい、そうです。

そうならば、
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & r
これで、A列の通し番号も印刷されます。
つまり、全ページに、通し番号付きで印刷されます。
なお、Sheet2のどこかのセルにエラーがある前提で｢MacroSample()｣はできていて、エラーの出てくる一番上の行の１つ上の行までで、印刷は終わります。エラーのあるセルの行を含む下の行は一切印刷されません。

4)現状では、Sheet2の[$B$8:$D$1500]あるいは[$B$8:$I$1500]に、Sheet3のセルを参照する式が入っているのでしょうか。
4)そうです！
5)現状では、Sheet3のデータ数が400件の場合、Sheet2のB407はきちんとデータが表示されていて、B408は、＃N/Aのように(Sheet3で参照してるセルの表示と同じ)になっているのでしょうか。
5)仰る通りです。

Sheet2のどこかには＃N/Aがでているハズですが、どこにも＃N/Aががないとなると、このコードのままだと、動作が狂います。

このコードは印刷設定をするだけなので、印刷は手動でやる前提にしてあります。紙をセットして、プリンターを選んで、プリンターのプロパティを設定しては、手動でやってください。　あとはexcelに戻って、Sheet2を表示させて、印刷をすれば、全ページが連続して印刷されます。

この回答へのお礼

度々申し訳ございません。
シート1で貼り付けてるデータをVLOOKUP関数を使用してシート3に反映させてるので500件分のみしかデータがない場合、501件以降には#N/Aと表示されており、シート2はシート3に反映されてるデータをそのまんま引っ張ってきてるのでシート2の501件以降にも#N/Aと表示されております。
分かりにくくて申し訳ございません。

お礼日時：2019/04/10 19:56

No.8 回答者： zongai 回答日時： 2019/04/10 21:41

他の方の回答もあるので、混乱されるかもしれませんが…

補足日時：2019/04/10 20:44
こちらは私の作った方のようなので、また回答投稿しますね。

画像にあるエラーは、コピペしてる内容が不十分だからです。
↓この頭に⬛を付けた3行が欠けています。

⬛'最下行を取得するよ――――
⬛matubi = Cells(Rows().Count, 2).End(xlUp).Row
⬛Do While WorksheetFunction.CountBlank(Cells(matubi, 2)) = 1
matubi = matubi - 1
Loop

余裕があれば、改めてコピペ等見直してみて下さい。

No.9 回答者： zongai 回答日時： 2019/04/10 22:02

すみません、3行じゃなく、もう1行で

4行抜けです。

No.10 回答者： zongai 回答日時： 2019/04/10 23:34

補足コメント（2019/04/10 23:11）拝見しました。

元からコピペし直したなら大丈夫だと思いますが…。
（最後の1行はご自身で追加されたのですよね）

その確認、回答者にさせます？(￣＿￣;）

ついでに実行結果も書いていただけると良かったんだけど。

この回答へのお礼

すいません、最後の1文以外でお願いします。

お礼日時：2019/04/11 00:13