【Excel VBA】指定のブックを指定のファイル名、指定の場所に複製してくれるマクロ

A.xlsx と　B.xlsxがあります。
A.xlsx には「申請用紙①」「申請用紙②」「申請用紙③」「プルダウン用シート」のシートで構成され、
「プルダウン用シート」は B.xlsx を参照しています。
そして、「申請用紙①～③」のフォーマットには入力規則でプルダウンから選ぶようになっていますが、
その選択肢は「プルダウン用シート」に記載しています。

最終的に、A.xlsxのみを各所に配布し、入力して戻してもらうのですが、
A.xlsxのみを開くと「このブックには、ほかのデータソースへのリンクが含まれています。」等の
メッセージが表示されるため煩わしいです。(キャンセルすればいい、無視すればいいという話ではありません)

配布前に、「プルダウン用シート」の参照式をすべて値にしてしまえば良いのかもしれませんが、
かなり手間に感じています。
もっと良い方法はないでしょうか？

例えば考えたのは、
A生成マクロ.xlsm などで、「申請用紙①～③」と「値化されたプルダウン用シート」で構成されたxlsxで
保存できれば良いと思ったのですが実際にどうすればよいのか分かりません。
(申請用紙はパスワードでシート保護をかけ、 プルダウン用シートは非表示させた状態で実施できるのかどうか)

また、どうせなら保存内容を指定するためのシートを用意しておき、
そのシートのC2セルから下方向に列挙したテキストを用いて、
そのままファイル名として保存できるようにするとか、
保存場所は、このマクロが置いてあるフォルダ内に「申請書」というフォルダを作成し、
その下の階層に保存されるようにするなど便利にしたいです。

もっと良い方法、または上記を実現するためのポイントを教えていただけると助かります。
(まだ自由にマクロを組めないのでポイントとなる命令を教えていただけると。)

No.2 ベストアンサー 回答者： fujillin 回答日時： 2018/06/27 15:37

No1です。

私の所属するところでも似たようなことは行いますが、配布元が各人別に個別のファイルを作成するような親切なことはまず無いですね。(笑)
大抵の場合、共通サーバに記入用のファイル（読み取り専用に設定）を置いておいて（あるいはメール等で配布）、そのディレクトリに提出用の空のフォルダを置いています。
『提出者する際はファイル名の後に「＿氏名」を付け加えて提出』などのルールを配布時の説明で併せて行なうという方法をとっていますね。

それはさておき、
＞B10セルから下に、保存したいファイル名が列挙されているとして、
＞上記を繰り返す方法を教えていただけないでしょうか？
若干効率は悪いかも知れませんが、保存時のファイル名を順にセルの値に変えながらループすれば良いですね。
リストが記入されているブック、シートが不明ですが、実行されているマクロが記入されているブック内のシートと仮定して、以下の「"リストのあるシート名"」の部分を実際のシート名に変えて試してみてください。
（動作がきちんと確認できれば良いので、テストには3～4人分で十分でしょう）

※ B10セルから連続してファイル名が記入されているものとして、空白セルが出現したら終了します。
　（仮に上限をB1000セルとしてあります）
※ 先に最終行を求めておいて、その範囲で繰り返すという方法の方が一般的かもしれません。

Dim rw As Long, fName As String
Const shName = "リストのあるシート名"

For rw = 10 To 1000
　fName = Trim(ThisWorkbook.Worksheets(shName).Cells(rw, 2).Text)
　If fName = "" Then Exit For
　ActiveWorkbook.SaveAs Filename:="C:\Users\tanapyondai\Desktop\全員分の書式\" & fName & ".xlsx"
Next rw

この回答へのお礼

>配布元が各人別に個別のファイルを作成するような親切なことはまず無いですね。(笑)

配布先に取引先も含まれるため、ある程度新設設計が必要なんです。。。

空白セルが出るまでループさせる方法、ありがとうございます。
成功しました！
ありがとうございました。

お礼日時：2018/06/27 16:25

No.1 回答者： fujillin 回答日時： 2018/06/27 13:22

こんにちは

＞配布前に、「プルダウン用シート」の参照式をすべて値にしてしまえば良いのかもしれませんが、
＞かなり手間に感じています。もっと良い方法はないでしょうか？
実際の作業として、「セル全体をコピーそのまま値をペースト」という数秒で済む作業と思いますが、これを「もっと簡単に」と言っても、マクロを実行するのにも、エクセルを開いたりボタンを押したり（マクロの実行）で、ほぼ同様の手間がかかかるのではないかと想像します。

・・・ということで、実際のところ何を期待なさっていらっしゃるのかよくわかりませんが、マクロ化したいのであれば、以下のようなものを作成すれば良いのではないでしょうか。

　1）現状のA.xlsxを開く
　2）A.xlsxのプルダウン用シート内の値をCopy後、
　　 PasteSpecialで値を確定する
　3）これを別名保存で「配布用A.xlsx」等の名称で保存する
　4）「配布用A.xlsx」のブックを閉じる
　　（元のA.xlsxは、元の形のままで残ります）

上記の内容であれば、エクセルのリボンからの操作で行うように注意して操作すれば、マクロの記録でそのままのものを作成することが可能です。（例えば、ファイルのダブルクリックで開いたりすると記録はとれません）

これを、別ブックのボタンに登録しておけば、ボタンのクリックで上記の処理が完了するようになります。

この回答へのお礼

ありがとうございます。
「エクセルのリボンからの操作で行うように注意して操作」
↑これで記録が取れる事を知りませんでした。今後も大いに役に立ちそうです。

やりたかった操作の記録を取ったところ、別名保存する部分は以下のように記録されました。
--------------------------------------------------
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\tanapyondai\Desktop\全員分の書式\配布用_鈴木.xlsx"
--------------------------------------------------
人数分の複製を行いたいのですが、
B10セルから下に、保存したいファイル名が列挙されているとして、
上記を繰り返す方法を教えていただけないでしょうか？

配布用_鈴木　→B10
配布用_佐藤　→B11
配布用_石井　→B12
・
・
・
件数は変動します。

保存先も指定できるようにしたいのですが、これはどこかのセルに記入しておいて、その値を使うようにしたいと思っています。
データがある分だけ繰り返す方法を教えていただけると大変助かります。
よろしくお願いいたします。

お礼日時：2018/06/27 13:53