自動でマクロファイルにデータを入力し保存したい

質問

何時も、大変お世話になっております。
私の能力では、解決出来ず大変困っています。
稼働率や共用率、その他の値を計算してくれるExcelのマクロファイルに、機器毎にデータを入力し、計算が終わるとこのマクロファイルを別名で保存する作業を毎回手作業で行っています。
機器は250個程あり、大変な手間が掛かってしまいます。
また、手作業で別名でファイルを保存する為、入力ミスが生じでしまいます。
この作業を自動化したいのですが、やり方が全く分からず困っています。もし、自動化のやり方をご存じでしたら、ご教示をお願いいたします。
・データファイルは下記のようなフォーマットになっています。
・セルA2～セルAOの範囲の装置分のデータを、マクロファイルの入力用シート『カテゴリーログ』のセルA4から値張り付けで入力します。
・出力ファイル名は、『ステーション_グループ_装置ID_装置名.xlsx』としたいです

装置ID 装置名 ステーション グループ 利用日 開始時間 終了時間 利用時間 利用形態 ユーザーID 利用者 所属区分 所属機関 利用区分 提供AM 課題ID 課題名 利用係数 単価 内容消耗品 数量 実費物件費合計 単価 役職 数量 実費人件費合計 標準単価 適用単価 数量 共用設備等使用料合計 基準額 役職 "技術代行
係数" 単価 数量 合計 小計 間接経費 税抜き合計 消費税相当額 合計 単合税込 備考欄1 備考欄2 備考欄3
AB0016 AB6連装置 融合St 001F 2022/6/2 13:30 15:30 2 技術代行 500037 大谷 翔平 大企業 ソソ株式会社 NNF利用 22HH8041 GaN系プロセス 1 2000 CR料 2 4000 2 0 2 0 15000 研究、エン 2 30000 2 60000 64000 19200 83200 8320 91520
AB0019 化合物装置 融合St 001F 2022/6/2 12:00 13:00 1 機器利用 500174 吉田 知也 公的研究機関 A総合研究所 AAM利用 JPNM0003 シリフォト 0.3 2000 CR料 1 2000 1 0 1 0 15000 研究、エン 0 0 1 0 2000 0 2000 200 2200
AB0009 水蒸気装置 融合St 001F 2022/4/30 16:00 16:15 0.25 機器利用 500114 柏 翼 大学等 ABC大学 AAM利用 JPNM0009 Si熱電集積 0.3 2000 CR料 0.25 500 0.25 0 0.25 0 15000 研究、エン 0 0 0.25 0 500 0 500 50 550
AB0024 赤外線装置 融合St 001F 2022/4/30 16:15 17:00 0.75 機器利用 500172 高橋 正夫 大企業 ソレ株式会社 NNF利用 22HH8002 GaN系材料 1 2000 CR料 0.75 1500 0.75 0 0.75 0 15000 研究、エン 0 0 0.75 0 1500 450 1950 195 2145
AB0024 赤外線装置 融合St 001F 2022/6/30 15:15 16:15 1 機器利用 500172 高橋 正夫 大企業 ソレ株式会社 NNF利用 22HH8002 GaN系材料 1 2000 CR料 1 2000 1 0 1 0 15000 研究、エン 0 0 1 0 2000 600 2600 260 2860
AB0009 水蒸気装置 融合St 001F 2022/6/30 15:00 15:30 0.5 18913 河野 久機 装置管理 自主運用 0 0 CR料 0.5 0 0.5 0 0.5 0 15000 研究、エン 0 0 0.5 0 0 0 0 0 0
AB0030 顕微式測定 融合St 001F 2022/6/30 15:00 16:00 1 機器利用 500135 中前 秀一 大学等 BBB大学 NNF利用 22HH8046 半導体パルス 0.3 2000 CR料 1 2000 1 0 1 0 15000 研究、エン 0 0 1 0 2000 600 2600 260 2860
AB0018 多目的装置 融合St 001F 2022/5/30 14:45 15:30 0.75 機器利用 500071 清水 悦朗 大企業 セラ株式会社 NNF利用 22HH8014 フォト 1 2000 CR料 0.75 1500 0.75 0 0.75 0 15000 研究、エン 0 0 0.75 0 1500 450 1950 195 2145
AB0165 フェム装置 分析St 分析2 2022/5/30 9:00 17:00 8 装置管理 使用不可 0 8 0 8 0 8 0 0 8 0 0 0 0 0 0
AB0166 放電質分析 分析St 分析2 2022/6/8 9:00 0:00 15 10637 装置管理 NNMS 使用不可 メンテナンス 0 15 0 15 0 15 0 0 15 0 0 0 0 0 0
AB0166 グロー分析 分析St 分析2 2022/6/9 0:00 9:00 9 10637 装置管理 NNMS 使用不可 メンテナンス 0 9 0 9 0 9 0 0 9 0 0 0 0 0 0
AB0166 グロー分析 分析St 分析2 2022/6/9 9:30 11:30 2 技術代行 15986 高田 裕一 NNMS NNMS 内部利用 22HH5379 Ni基超の分析 0.1 2 0 2 0 2 0 15000 研究、エン 2 3000 2 6000 6000 0 6000 600 6600
AB0166 グロー分析 分析St 分析2 2022/6/9 13:30 15:30 2 技術代行 15986 高田 裕一 NNMS NNMS 内部利用 22HH5379 Ni基超の分析 0.1 2 0 2 0 2 0 15000 研究、エン 2 3000 2 6000 6000 0 6000 600 6600
AB0176 酸素22装置 分析St 分析2 2022/6/1 14:00 15:00 1 20546 装置管理 自主運用 0 1 0 1 0 1 0 0 1 0 0 0 0 0 0
AB0169 簡易型粉末 分析St X回折 2022/5/8 11:30 13:30 2 機器利用 20458 田代 知美 NNMS スマート材料 内部利用 22HH5082 0.1 2 0 2 0 2 0 0 2 0 0 0 0 0 0
AB0169 簡易型粉末 分析St X回折 2022/5/8 13:40 15:40 2 機器利用 20729 直井 今子 NNMS 熱エネ材料 内部利用 22HH5176 0.1 2 0 2 0 2 0 0 2 0 0 0 0 0 0
AB0169 簡易型粉末 分析St X回折 2022/6/9 9:00 9:30 0.5 8101 上瀬 直美 NNMS 分析ステ 使用不可 メンテナンス 0.5 0 0.5 0 0.5 0 0.5 0 0 0 0 0 0
AB0169 簡易型粉末 分析St X回折 2022/6/13 14:00 15:30 1.5 機器利用 21015 タラチャンド NNMS 変換材料 内部利用 22HH5176 0.1 1.5 0 1.5 0 1.5 0 0 1.5 0 0 0 0 0 0
AB0081 X線光電子 融合St 002F 2022/4/2 9:30 10:00 0.5 技術代行 15659 李 ー勝 NNMS 内部利用 22HH5111 Two 0.1 0.5 0 0.5 0 0.5 0 15000 研究、エン 1.2 1800 0.5 900 900 0 900 90 990
AB0085 多連S装置 融合St 002F 2022/4/2 16:30 17:00 0.5 13109 装置管理 自主運用 0 0.5 0 0.5 0 0.5 0 0 0.5 0 0 0 0 0 0
AB0085 多連S装置 融合St 002F 2022/4/2 9:30 11:00 1.5 技術代行 18311 ロダ オマル NNMS 内部利用 22HH5034 人工シナプス 0.1 1.5 0 1.5 0 1.5 0 15000 研究、エン 1.2 1800 1.5 2700 2700 0 2700 270 2970
AB0095 SEM3装置 融合St 002F 2022/6/2 14:00 16:00 2 技術代行 17769 チェンフイ NNMS 内部利用 22HH5365 高次構造 0.1 2 0 2 0 2 0 15000 研究、エン 1.2 1800 2 3600 3600 0 360

回答No.36

kkkkkm · ベストアンサー

> 　装置の稼働率-共用率ファイル中に連番を付ける事は出来ませんでした。

連番ではなく
もし、「装置の稼働率-共用率_xxxxxx-xxxxxx.xlsx」にデータ転記後、A列、B列、C列のいずれかが「空白になる」可能性がある場合、データが上下デコボコに転記されてしまいます。

たとえばB列(グループ)に空白があった場合

A, B, C, D, E列
あ,い,う,え,お　1件目
か,空,く,け,こ　2件目
さ,し,す,せ,そ　3件目

上記のように転記されるのが本来ですが
空白があると下記のように転記されてしまいます。

A, B, C, D, E列
あ,い,う,え,お　1件目
か,し,く,け,こ　2件目
さ,空,す,せ,そ　3件目

> ST名 グループ 装置ID（資産番号）

上記がA列、B列、C列で空白になる可能性がないのでしたら変更しなくてもいけます。

先日「#DIV/0!」の件があったので、上記のセルで空白のセルが存在する可能性もあるのかなと思っての変更提案です。

連番は手作業でお願いします。

お礼

大変お世話になっております。
最終的な確認は一応完了しました。
この質問については、これで完了と致します。
今回の質問から困った事、分からない事が新たに発生した場合は、再度ご質問をさせて頂きますので、何卒よろしくお願い申し上げます。
最終までご教授下さり、本当にありがとうございました。

補足

大変お世話になっております。
更にかみ砕いたご教示ありがとうございました。

下記、理解しました。　連番は手作業で作成する事に致します。
『先日「#DIV/0!」の件があったので、上記のセルで空白のセルが存在する可能性もあるのかなと思っての変更提案です。』

では、回答35でご教示頂いたｽｸﾘﾌﾟﾄの箇所は、元へ戻した方がよろしいでしょうか？

回答No.6

kkkkkm

全てマクロで実行の場合

並び替えが違うと駄目だと思いますから、マクロの記録で「ステーション、グループ、装置ID、利用日順に並べ替え」をして記録されたコードを教えてください。

> 同じ装置ID分を、マクロファイルの[カテゴリーログ]シートへコピペする。

という事ですので、装置ID以外が違っていても同じ塊としてコピペすると考えてよろしいですか。
上から検査して装置IDが変化した時点で別の塊に移ったと考えます。

回答No.5

kkkkkm

①ステーション、グループ、装置ID、利用日順に並べ替えをした全機器3ヶ月が入っているデータファイルから、
　　同じ装置ID分を、マクロファイルの[カテゴリーログ]シートへコピペする。

この後に
1つのシートがどこなのか不明ですが、250個のA2～AA2を同じシート(第三のブックでしょうか)にコピペするのでしたら
コピペした時点の[Summary3]のシート内の稼働率等の値の行(A2～AA2)を、1つのシートへコピペして追記。
を実行して

確認用に上記のマクロファイルを『ステーション_グループ_装置ID_装置名.xlsx』の名前でホルダーへ保存する。

という流れを250回繰り替えす、で良さそうな気がするのですがいかがでしょう。

回答No.2は
手作業でデータファイルからマクロファイルへ必要なデータをコピーし、現状のマクロを実行後(現状のマクロが何をするのか不明でしたのでここでの実行を考えてました)、その後回答2のコードを実行するという事を考えてました。
手作業の中で「別名で保存」「ファイル名の記載」が一番面倒だと思ったので。

回答2のコードは以下の単純な動作をします(ブック全体保存なのでTest()の方です)
マクロファイルを
ステーション_グループ_装置ID_装置名.xlsx
で保存(ファイル名誤記が防げます)
実際のマクロファイル名.xlsm
を再度開きます(コピーしたデータはマクロファイルには保存されていないので削除コードを実行する必要はありません)
ステーション_グループ_装置ID_装置名.xlsx
を閉じます

コードは実際のマクロファイルに記載して実行してください。
「ステーション_グループ_装置ID_装置名.xlsx」は「実際のマクロファイル」と同じフォルダに保存されます。

とりあえず、マクロファイルが思った状態で保存されるかどうか
一操作分
手作業でデータファイルのデータをマクロファイルへコピペ
回答No.2のマクロを実行
で試してみてください。

よければ冒頭の手順(手順がよろしければ)を全てマクロで実行のコードにチャレンジしてみます。
「実際のマクロファイル名.xlsmを再度開く」は行わない方法になります。

補足

先程、回答7で補足の記載をさせて頂きましたが、
２つほど更に、ご返答させて頂きます。

④はい、下記の通りで良いはずです。
　『装置ID以外が違っていても同じ塊としてコピペすると考えて宜しいか？』

⑤大変お手数をお掛けしますが、下記について可能でしたら是非お願いいたします。
　『全てマクロで実行のコードにチャレンジしてみます』

回答No.4

kkkkkm

回答No.2の補足、訂正です

何かしらの計算をするマクロを実行後に回答No.2のマクロを実行してください。

また、「カテゴリーログ」以外のシートを開いているかもしれませんから

念のために「カテゴリーログ」のセルの値を取り込むように限定して

以下の部分を (Test()、Test2()どちらも)

NewBookName = Range("C4").Value & "_" & Range("D4").Value & "_" & Range("A4").Value & "_" & Range("B4").Value & ".xlsx"

こちら↓に変更してください。 Rangeの前に「.」があるので3行ともコピペしてください。

With Worksheets("カテゴリーログ")
NewBookName = .Range("C4").Value & "_" & .Range("D4").Value & "_" & .Range("A4").Value & "_" & .Range("B4").Value & ".xlsx"
End With

補足

早速のご教示、心より感謝致します。
何時ものように、説明が足りて無く申し訳ありません。説明にも誤りが有りましたので、やりたい事を再度記載させて頂きました。ご不明点がありましたら、再度ご説明させて頂きますので、何卒宜しくお願い致します。

・マクロファイルには、下記に記した、[カテゴリーログ]シートの値を取り消すマクロが組み込まれているだけです。
　マクロファイル内には複数のシートに関数式が組み込まれており、[カテゴリーログ]シートのセルA5～AO5以降に、装置データの同じ装置ID分のみを値張り付けで入力すると、[Summary3]のシートに稼働率等の計算値が書き込まれる仕組みになっています。
・[Summary3]のシートには、タイトル名と値の2行が記載されます。
　実は、最後に下記の(A2～AA2)の機器250台分の値のみが欲しいのです。
　St名、装置ID、装置名、装置稼働率、その他のタイトルが続く・・・・・・・(A1～AA1)
　分析St分析2、AB0013、フェム装置、82%、53％、その他の値が続く・・・・・・(A2～AA2)

・今現在、手作業で実施して作業手順は、①～③です。非常時手間がかかります。
　①ステーション、グループ、装置ID、利用日順に並べ替えをした全機器3ヶ月が入っているデータファイルから、
　　同じ装置ID分を、マクロファイルの[カテゴリーログ]シートへコピペする。
　②上記のマクロファイルを『ステーション_グループ_装置ID_装置名.xlsx』の名前でホルダーへ保存する。
　　※上記①、②を250回(機器の台数分)繰り繰り返します。
　　※値確認の為、250個の『ステーション_グループ_装置ID_装置名.xlsx』は保存しておきます。
　③250個のステーション_グループ_装置ID_装置名.xlsxの[Summary3]のシート内の稼働率等の値の行(A2～AA2)を
　　手作業で、1つのシートへコピペして追記します。

・ご質問があります。
　回答2でご教示頂いたやり方は、新しいExcelファイル上でVBAエディタを開いて、ご教示頂いたVBAのｽｸﾘﾌﾟﾄを
　入力後、実行すればよろしいでしょうか？
　その場合、"\実際のマクロファイル名.xlsm"とデータファイルは同じホルダーに入っていればよろしいでしょうか？
　装置毎のデータは手動でマクロファイルへの入力が必要でしょうか？

【以下はマクロファイル内のマクロの記載になります】
Option Explicit

Sub データ削除()
' ====================================
' データ削除 Macro
' シート「カテゴリーログ」のデータを全て削除する。
' 2022/03/24更新
' ====================================

'■（ダイアログボックスを表示して、実行するか否か確認する）
Dim rc As VbMsgBoxResult
rc = MsgBox("マクロを実行しますか？", vbYesNo + vbQuestion, "■■■～Logデータを削除します～■■■")
If rc = vbYes Then
'next
Else
MsgBox ("処理を中止します"), vbCritical
End
End If

'■ (Logデータを削除します。20000行まで。)
Sheets("カテゴリーログ").Select '←2022/03/24追加
Range(Selection, Selection.End(xlDown)).Select
Range("A5:AK20004").Select '←2022/03/24変更
Selection.ClearContents
Range("A1").Select
End Sub

回答No.3

kkkkkm

回答No.2の補足です。
データファイルの全ての行にたいして
コピペ
現状のマクロ
No.2のマクロ
と連続して行うマクロも可能ですが、ブックを開いたり閉じたりを連続で実行するとエラーになる可能性もありますし、一つ一つ確認して進む方が間違いがないような気もします。

回答No.2

kkkkkm

データファイルの各行をマクロファイルのA4にコピペし、何かしらの計算をするマクロを実行後にそのマクロファイルを別名(指定したセルの内容を繋げた名前)のマクロ無しで保存するという動作だとしたら。

質問のデータのデータファイルの2行目をマクロファイルのA4にコピーしたとした場合
新規ファイル名が
融合St_001F_AB0016_AB6連装置.xlsx
となります。

'↓元のブック全体をマクロなしで保存するマクロです

Sub Test()
Dim wb As Workbook
Dim NewBookName As String

Set wb = ThisWorkbook
NewBookName = Range("C4").Value & "_" & Range("D4").Value & "_" & Range("A4").Value & "_" & Range("B4").Value & ".xlsx"

'マクロ無しのブックに保存できないというメッセージが出ますがOkしてください。
wb.SaveAs ThisWorkbook.Path & "\" & NewBookName, FileFormat:=xlOpenXMLWorkbook
MsgBox "ファイル名を「 " & NewBookName & " 」として保存しました", vbInformation
'↓元のマクロブックを開きます　ブック名は実際のブック名にしてください
Workbooks.Open ThisWorkbook.Path & "\実際のマクロファイル名.xlsm"

wb.Close '←名前を変更し保存したブックを閉じたくない場合はここを削除

Set wb = Nothing

End Sub

'↓カテゴリーログのシートだけ新規ブックとして開いて保存するマクロです

Sub Test2()
Dim wb As Workbook
Dim NewBookName As String

Worksheets("カテゴリーログ").Copy
Set wb = ActiveWorkbook
NewBookName = Range("C4").Value & "_" & Range("D4").Value & "_" & Range("A4").Value & "_" & Range("B4").Value & ".xlsx"

wb.SaveAs ThisWorkbook.Path & "\" & NewBookName, FileFormat:=xlOpenXMLWorkbook
MsgBox "別ファイル「 " & NewBookName & " 」として保存しました", vbInformation
wb.Close '←新規ブックを閉じたくない場合はここを削除
Set wb = Nothing

End Sub

回答No.1

m_and_dmp

「機器毎にデータを入力し、計算する」ファイルとは別の場所（個人用マクロブックが良いとおもいます。）に次のマクロを登録します。
-------------------------------------------
Sub ChangeFileNameAndSave()
pPATH = ActiveWorkbook.Path 'ファイルのパスを取得
dFN = ActiveWorkbook.Name 'ファイル名を取得
dFN = pPATH & "\" & dFN 'デフォルト ファイル名を作成
FN = InputBox("ファイル名を入力してください。", , dFN)
'ファイル名の入力を促す。デフォルト ファイル名が表示されている。
ActiveWorkbook.SaveAs (FN) '指定したファイル名でファイルを保存
'
'Bookを閉じる。
ActiveWorkbook.Close
'
'エクセルを閉じる
'Application.Quit
'
End Sub
-------------------------------------------
「機器毎にデータを入力し、計算する」ファイルの作業が終了したら、上記のマクロを実行すると、ファイル名を聞いてきますので、新しいファイル名を入力して続行します。

まずは、試してください。作業するファイルは何でも良いので、適当な試験用ファイルを作って試してください。