[エクセルVBA]複数の勤務表ファイルを1か所で集計するマクロが複雑で困っています

Question

※長文失礼します。
勤務表(A氏).xlsx から各月の勤務時間を 集計表.xlsxに転記するマクロを作っています。

勤務表フォルダの中に、勤務表(A氏)、勤務表(B氏)・・・と複数名分のファイルが入っているので、
勤務表フォルダを指定した後、フォルダ内の全ファイルを処理します。(ファイル数は不明)


勤務表は、シート名が「1月」から「9月」まで9シートあります(9か月分)

まず勤務表(A氏)の「1月」シートJ351からJ509の縦150セルをコピーし、
集計表の「集計」シートのH15からH164の縦150セル領域に貼り付けます。(値で)

次に「2月」シートJ351からJ509の縦150セルをコピーし(全シート同一座標)、
集計表の「集計」シートのJ15からJ164の縦150セル領域に貼り付けます。

同様に「3月」はL列に、「4月」はN列に、、、
つまり2列ずつ右にずれながら貼り付けていきます。(9月はX列)
(間の列には数式(勤務時間×単価)が入っています)

★ですが各行に罫線が引いてあり、I列には、H列×金額の数式が入っているのですが、
何行用意しておくか予測できないのでその都度行を複製した方が良いと思っています。
どの流れが簡潔に処理できるか困っています。

また、縦150行分も貼り付けるのですが、ところどころ値のない行が存在します。(不規則)
★その行を一気に削除したいのです。(結果的には大抵20行以内に収まる想定ですが色んなパターンが存在するため150行用意しています。)

これで1ルーチン完了で、
次は勤務表(B氏)で同様の処理をしますが、
空行削除した後のすぐ下の行から下に追加していきます。

つまり、150行分作成しては空行削除して、その下に150行分作成して空行削除・・・の繰り返しです。
※都度空行削除せずに、150行×人数分全て作成した後に1回だけ空行削除の方が効率良いかもしれません。

この、繰り返しの中の繰り返しの処理で混乱してしまっており、
特に★印の処理が分からずに困っています。
詳しい方、アドバイスいただけると助かります。

fujillin · Accepted Answer

No4です

＞勤務表側でお膳立てするだけで簡略化できますね。
＞マクロだけにこだわってしまっていました。
もしも、このアイデアが利用可能であるのならば、もう少し工夫しておくことで、後の処理を更に簡単にできるのではないかと想像します。

1）勤務表側の集計シートの列構成を全体集計表と同じにしておく
　（月が1列おきになるようにしておく）
2）集計シートに作業列を設け、1～9月が空欄でないものをマーキングできるようにしておく
　（空欄を数えるCOUNTBLANK関数でも、データが数値ならSUM＝0でも良さそう）

上記の準備をしておけば、データをコピーするVBAでの処理は
3）集計シートで作業列をキーにオートフィルタでデータのある行だけを表示
4）集計表全体をCopy（作業項目名も含めて）し、PasteSpecialで全体集計シートへペースト
するだけで済むようになるのではないでしょうか。

4）のコピペでは、非表示の行は詰めてペーストされますので、後から削除する必要もなくそのままで必要な行だけがペーストされるはずです。（コピペだけでよくなる）
ですので、そのまま続けて次のブックの処理をしてゆけばよいことになります。
3）、4）はマクロの記録でもほぼそのままのコードが作成可能ですので、ループ内で使用できるように微調整すればよいものと想像します。

一方で、この方法だと、データとデータの間のセルにもペーストされますので、(勤務時間×単価)の関数式はコピペ処理の後に設定する必要が生じます。
しかしながら、想像するところ、設定されている関数式は全てフィルコピー可能な式であろうと思いますので、マクロから設定するにしても全部の範囲にまとめて設定すれば簡単に処理することができます。
関数式の設定についても、上記同様マクロの記録をとってみれば簡単に作成可能と思います。
（あるいはフィルコピーをそのまま行っても良いです）

fujillin · Answer

No5です。

＞最後の計算式をどうやってフィルコピーするかだけが課題です。
＞月と月の間の数式は不要になり、右端の合計欄にだけ計算式を入れる
もしそうであるなら、「右端の欄」というのはデータのコピペでは上書きされない範囲だと思いますので、先に計算式を設定しておいても、消されることがないのではないでしょうか？
（データが無ければ空白表示になるような関数式にしておいて、多めの行まで設定しておけば良さそう）

最初のご質問ではデータと式が交互の列にあったので、式を消さないためには、列のコピペを順に繰り返す必要がありましたが、質問者様はそのようなことを嫌っている様子でしたので、まとめてコピペする方法で説明をしました。
そうすると、式の範囲にもペーストされてしまうので、式が上書きされてしまうことになりますが、レイアウトを改良なさったようなので、データの範囲と式の範囲が被ることはないものと想像します。

ちなみに、セル（範囲）に関数式を設定するのは（No5にも書きましたが）、マクロの記録等を利用すれば方法はわかると思います。
実際のセル範囲が不明なので例で示してみると、A列に値を設定（後から入力でも良いですが）した状態で、
　Sub test1()
　　Range("B2").Resize(9, 2).Formula = "=A2"
　End Sub
を実行してみれば、わかるものと思います。

同様のことをオートフィルでやるなら
　Sub test2()
　　Range("B2").Formula = "=A2"
　　Range("B2").AutoFill Destination:=Range("B2:B10")
　　Range("B2:B10").AutoFill Destination:=Range("B2:C10")
　End Sub
のような感じになりますが、手での操作を模すことになるので、前記の方法の方が簡単だと思います。

fujillin · Answer

No3です

処理の全体像やシートの状況がわからないので（添付画像はNo2様がおっしゃるようにわからない）、なんとも言えませんが・・・
データを行単位で扱ってよいのなら、なおさらコピーする際に必要な行だけを詰めてコピーするようにした方が、一発で処理が終わるとは思いますけれどね。

＞ちょっと敷居が高いと思っているため、自分で実現可能案からスタートしている次第です。
それは仕方がないこととは思いますが、その分を工夫でカバーすることもできるのではないでしょうか？
内容をよくわかっていませんが、例えばの例として、各個人のブック側に個人の集計シートを非表示で作成しておいて、1～9月分を1行に（関数等で）まとめるようにしておくだけでも、VBAによる集計処理は簡単になると想像できます。
（単にループが1重分へるだけとも言えますが、これによって行単位で項目名も含めてコピーすれば、直接詰めてコピーするのも簡単になります。）

ご質問とは関係ないところに話が及びますが・・・
勤務時間は各人が入力するのだと思いますけれど、150行もある中から飛び飛びで自分に関係のあるところにだけ入力させるのって非効率的なのではないかと感じます。
自分に関係のある項目だけ、ソートしてまとめるとか、フィルタでまとめる等ができるのであればまだしもですが、文章の雰囲気からするとそのようなことはなさそうなので・・・
集計を簡単にするために、多くの人に面倒を分散するのって（←これって、形を変えながらあちこちでよく見かけますけれど）、個人的には本末転倒だと思っています。

＞実際には勤務表記載の氏名を集計表のF列に書き込むため、各人の区切りが着くと認識しています。
行単位で処理できるようですので、No5で懸念した問題は大分減ると思いますけれど、氏名を書き込む処理がデータを転記する処理とは別に行われている（？）ようですが、そのこと自体が不可解ですね。
データと氏名は一体のもののはずなので、一括して処理することで、万一の食い違い等の発生を防止することができるはずですけれど。

・・・とはいえ、私がとやかく言う筋合いではありませんので、
1行をまとめて削除したいのであれば、No5の例の様にキーとなるセル範囲をRangeとして取得しておいて、
　Range.EntireRow.Delete
とすることで、その行全体を削除することが可能になります。

fujillin · Answer

こんにちは

考え方は人それぞれなので、ご質問の★の部分について。

＞★ですが各行に罫線が引いてあり、I列には、H列×金額の数式が
＞入っているのですが、何行用意しておくか予測できないので
＞その都度行を複製した方が良いと思っています。
全体的な処理は値の書き写しだけのようですし、式というのも左隣のセルと固定セル（金額）のみを参照しているものと思いますので、例えば
　=IF(Hn="","",Hn * 金額）
のような式にしておいて（nは行番号）必要そうな範囲にあらかじめフィルコピーしておけば済むのではないかと想像します。

ただし、質問者様は「削除」する方法をお考えのようなので、式を先に設定しておく方法だと、削除を行った際に「#REF!」エラーが出てしまうため、「削除」を（隣の式の設定されているセルも含めて）2列単位で行うようにするか、あるいは後からマクロで式そのものをまとめて設定するなどの工夫が必要になりそうな気がします。
（削除を行わない方法なら、先に式を設定しておいても問題が起きることはないでしょう）

他の方法として、セルを単体で削除しても参照位置が変わらないような関数式にしておく方法も考えられます。
・INDIRECT関数を利用した式にしておく
・削除することのないセル（例えば、I列、K列・・など）を基準としたオフセット参照を用いる
などでしょうか。
このような参照方法にしておくことで、データ部分のセルを「削除」しても参照エラーになることは無くなると思います。

＞★その行を一気に削除したいのです。
想像するところ、「行」の削除ではなくて、「セル」の削除（上詰め）ではないのでしょうか？
ご説明によると、集計シートの列に各月の値が並んでゆくことになりますが、各月の値は（多分）独立しているのでしょうから、必ず同じ行の値が空白とは限らないものと思います。
それなので、「行」を削除してしまうと、必要な値の入っているセルまで削除してしまう可能性はありませんか。

ひとまず、指定のセル範囲から空白セルを削除して上に詰める簡単な方法の例を
　targetRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
（targetRangeは対象とするセル範囲です）

このような処理を行った際は、上にも書きましたように、別にセルの参照式等が設定されている場合には、そちらの式のエラーの原因となり得ます。
また、
＞予め集計表に、数千行の表罫線と数式を入れておいて
のように罫線などであらかじめ書式を設定していても、「セルの削除」をすることによって書式もそれぞれバラバラに移動しますので、そうなっても問題のないような書式（例えば、一律に同種類の罫線が指定してあるだけとか）でないかぎり、関数式と同様におかしな結果になる可能性があります。

なお、ご質問には関係ありませんが、ご説明のような処理を行うと、
A氏、B氏のデータの区切りがどこにあるのかわからなくなりますし、その区切りの位置もH列、J列・・・でそれぞれ位置が異なることになると考えられます。
集計作業としてはそれで問題はないのかも知れませんが、後から、データの不整合などが発見された場合もOKでしょうか？
（何かの都合で、集計データから元データをたどりたくなった際にかなり面倒である）

めぐみん_ · Answer

No.1です。

画像拡大しても不鮮明でちょっと疑問なのですが、例えばA氏の１月シートのG列の値とH列の値がリンクしているように
集計シート側では見られますが、空白行になる部分は毎月同じ箇所になるのでしょうか？
それとも変動するのでしょうか？

あと集計シートのG列は事前に記入されている？（3000行も？）

例えば各自の毎月のG列ーH列の組み合わせが同じであれば、最初（1月）はG・H列且つH列が空白でない範囲をコピーし、
集計シートに貼り付ける。
あとは同じ範囲を2月～9月までH列に限って行なう。
個々のGーHの範囲が違うならSheetを調べる最初だけ範囲を決めるようにする。

ただし月毎に上記の関係が変わるようだと難しいかな。

めぐみん_ · Answer

＞★ですが各行に罫線が引いてあり、I列には、H列×金額の数式が入っているのですが、

罫線の有無が何に関係あるのかわからないのと、I列ってどっちのBook（のシート）について言っているの？
でそれがコピペとどのように関係してくるのか？
『金額』って固定された数値を指すのかどこかのセルを指すのかも不明。
数式としてあるならその例題があった方が良かったかも。

＞また、縦150行分も貼り付けるのですが、ところどころ値のない行が存在します。(不規則)
＞★その行を一気に削除したいのです。

削除とはコピー元の話ではないですよね？コピー先の事ではないですか？
それなら元々空白行はコピーの範囲から外してコピペしては？
それとも不規則に存在する空白セルは、コピー元シートの別列にもあってと言う事でしょうか？

作業を行なうBookを目の前にして質問を書かれている質問者とは違い、何も持たない回答者側はその質問文『だけが』頼りです。
可能ならダミーデータで画像作成して添付して貰えてたなら、回答は直ぐに付いたのではないかと思いますよ。