[[20240418162450]]　『シートコピーと入力規則のリスト設定について』（sea）　ページの最後に飛ぶ

『シートコピーと入力規則のリスト設定について』（sea）

 外部から頂くエクセルファイルに80シート程あり、

 ☆1　シートごとにファイルを分ける
  ↓
 ☆2　各シート担当者にデータを入力して頂く
  ↓
 ☆3　80シートをひとつのエクセルファイルにまとめる　←今回ここで質問です
 （外部から頂いたエクセルファイルの形に戻す）

 をしています

 各シート担当者にファイルを分ける理由は、
 ・ブックの共有はエラーがおきやすく管理がしづらいと感じている
 ・共有をしないと担当者が開いていると他の担当者が入力できないのはつらい

 外部から頂くエクセルファイルのシートは

 Table ｜ 支店1 ｜ 支店2 ｜ 支店3 ｜ … ｜ 支店80

 支店1～支店80シートは、1行目が見出しでそれぞれの支店データが入力されています

 支店シートとTableシートの関係は、
 支店シートにデータの入力規則のリスト設定がされており、

 たとえば、1支店シートの A3のセルにデータの入力規則のリスト設定で
 =Table!$A$2:$A$3　このように設定されています

 ☆1　支店シートごとにファイルを分けるのは、
 次のようなマクロを使っています

     For i = 1 To Worksheets.Count

         SName = Worksheets(i).Name

         If SName <> "Table" Then

             Worksheets(Array(i, "Table")).Copy

             With ActiveWorkbook

                 .Sheets("Table").Visible = False
                 .SaveAs Filename:=分割後Folder & "\" & SName & ".xlsx"

                 .Close

             End With

         End If

     Next i

 出来上がり
 ----------------
 支店1ファイル
 　Table ｜ 支店1

 支店2ファイル
 　Table ｜ 支店2

 …
 ---------------

 つぎに、
 ☆2　担当者に入力してもらう
 ここまでは問題ないです

 ここから、
 ☆3　外部から頂いたファイルの状態に戻すマクロです

     First = 1
     Do Until TargetFile = ""

         Set wb = Workbooks.Open(ThisWorkbook.Path & "\Data\" & TargetFile)

         If First = 1 Then
             Sheets(Array(2, "Table")).Copy
             Set wb結合後 = ActiveWorkbook
             First = 0
         Else
             Sheets(2).Copy Before:=wb結合後.Sheets(1)
         End If

         wb.Close False

         TargetFile = Dir

     Loop

 こちらですと、2回目以降にコピーした支店2シートのリスト設定が
 =Table!#REF! このようになりリストが機能しなくなります

 どのように対応したらよいでしょうか？

 説明が下手ですみません
 不足等ありましたら書き込みさせて頂きますので
 よろしくお願いします

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

 入力規則では、「他のブックの参照はできない仕様である」ことが根本原因でしょう。
 wb結合後ブックの各シートに、自前で入力規則を設定せざるを得ないと思います。
 マクロ記録とれば、コードの骨格がえられるので、それを参考にしたらどうでしょうか。

（xyz） 2024/04/18(木) 17:02:15

 複数リスト設定がされており、
 リスト範囲の変更などがないか毎回確認が必要になることになるので、
 ちょっと設定しづらいかもしれないです…

 うーん…対応しにくい…

 xyzさんありがとうございました
（sea） 2024/04/22(月) 10:17:54

コメント返信：

[ 一覧(最新更新順) ]

[[20240418162450]] 『シートコピーと入力規則のリスト設定について』（sea） ページの最後に飛ぶ

[[20240418162450]]　『シートコピーと入力規則のリスト設定について』（sea）　ページの最後に飛ぶ