[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数シート合算時、リンクや入力規則を解除する方法』（KICHI）
Excelで、複数のシートを1つのブックに合算した時に、
各シートはもともとデータの入力規則でリストを設定しており、名前の管理も設定しているため、合算後のファイルを開こうとすると、
「ブック内の一部リンクはすぐに更新できません。
値を更新せずにそのままにするか、間違っていると思われるリンクを編集してください。」というメッセージが表示されます。
このメッセージが出ないように、シートを合算するときに、最後に入力規則・名前の管理を解除するマクロはないでしょうか。
20シート以上あるので、合算後手動で解除するのが厳しいためです。
宜しくお願い致します。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
1.ブックの名前の定義を巡回して（削除できるものは）削除する 2.ブックの各シートを巡回して、 2-1.シートの名前の定義を巡回して（削除できるものは）削除する 2-2.シートの入力規則をクリアする
とりあえず現状のコードを示して、具体的に説明（エラーが出るならその箇所・エラー番号・エラーメッセージを提示、エラーにはならないが想定通りにならないなら○○になるはずが××になるというように状況を提示）いただくとアドバイスできることがあるかもしれません。
（もこな２ ） 2024/06/18(火) 18:16:49
合算は、お恥ずかしながらVBAではなく単純に合算するマクロです。
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\ファイル\データ\りんご.xlsx"
'
Sheets("?@りんごA").Select
Sheets("?@りんごA").Copy Before:=Workbooks("合算ファイル.xlsm").Sheets(1 _
)
Windows("りんご.xlsx").Activate
ActiveWindow.Close
Sheets("りんごA").Name = "?@りんご"
Range("A1").Select
Workbooks.Open Filename:="C:\ファイル\データ\みかん.xlsx"
Sheets("?AみかんA").Select
Sheets("?AみかんA").Copy Before:=Workbooks("合算ファイル.xlsm").Sheets(2 _
)
Windows("みかん.xlsx").Activate
ActiveWindow.Close
Sheets("みかん").Name = "?Aみかん"
Range("A1").Select
これが20個くらい続き・・
最後に、シートコピー・値にして終了です。
この後に、入力規則・名前の管理を解除できればと思います。
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("?@りんご", "?Aみかん", "?Cすいかﾞ", "?Dぶどう", "?Eいちご", "?Fバナナ", "?Gメロン" _
, "?Hいちじく", "?Iもも", "?Jパイナップル", "?Kいよかん", "?Lさくらんぼ", "?Mマンゴー", "?Nパパイヤ", "?Oキウイ", _
"?Pグレープフルーツ", "?Qでこぽん", "?Rあんず", " ?Sマスカット")).Select
Sheets(Array("?@りんご", "?Aみかん", "?Cすいかﾞ", "?Dぶどう", "?Eいちご", "?Fバナナ", "?Gメロン" _
, "?Hいちじく", "?Iもも", "?Jパイナップル", "?Kいよかん", "?Lさくらんぼ", "?Mマンゴー", "?Nパパイヤ", "?Oキウイ", _
"?Pグレープフルーツ", "?Qでこぽん", "?Rあんず", " ?Sマスカット")).Copy
ActiveWindow.TabRatio = 1
Application.DisplayAlerts = True
（KICHI） 2024/06/20(木) 09:19:41
■１
＞VBAではなく単純に合算するマクロです。
マクロ・・・機能の名前 VBA ・・・・Visual Basic for Applicationsの略でプログラミング言語
ということなので、提示されたものはVBAで書かれたマクロです。
（マクロとVBAは厳密には違いますが、Excelにおいては、しばしば同一視されています）
■２
＞まる１、まる２が ？マークにばぐっています
この掲示板では丸付き数字などの【環境依存文字】は、見る環境によっては文字化けするので使用を避けた方がよいとおもいます。
(シート名に使っているようなのでどうしようもないのかもしれませんが)
■３
VBAの世界では基本的にブックやシートなど（オブジェクトと言います）は、いちいち選択したりアクティブにしたりしなくとも操作可能です。
また、【標準モジュール】にシートの指定を省略したコードを記述すると、ActiveSHeetを指定したとみなされるルールです。
わかっていてやるならば止めませんが、可読性向上の観点から、きちんとオブジェクトを指定するようにすることをお勧めします。
■４
＞これが20個くらい続き・・
パターンが決まっていたりするのであれば、全部記述しなくても【ループ処理】という手法で単純化できます。
たとえば、【C:\ファイル\データ】というフォルダにある、「りんご、みかん、すいか、ぶどう、いちご」という名前のブックを片っ端から開いて、１番目のシートを自ブックに"集約"して、名前を変更するには↓のようにすればよいです。
Sub 研究用01()
Dim 名前 As Variant
Dim フォルダ As String
Dim ブック名 As String
Dim srcWB As Workbook
Dim i As Long
フォルダ = "C:\ファイル\データ"
With ThisWorkbook
'▼ダミーシートを挿入してから、ダミーシート以外を削除
Worksheets.Add after:=.Worksheets(.Worksheets.Count)
Application.DisplayAlerts = False
For i = .Worksheets.Count To 2 step ‐１
.Worksheets(i).Delete
Next i
Application.DisplayAlerts = False
'▼フォルダの中から条件（名前）に一致するブックを探して、１番目のシートを自ブックの末尾にコピー挿入
For Each 名前 In Split("りんご、みかん、すいか、ぶどう、いちご", "、")
ブック名 = Dir(フォルダ & "\" & 名前 & ".xls?")
If ブック名 <> "" Then
Set srcWB = Workbooks.Open(フォルダ & "\" & ブック名)
srcWB.Worksheets(1).Copy after:=.Worksheets(.Worksheets.Count)
.Worksheets(.Worksheets.Count).Name = 名前
srcWB.Close False
End If
Next 名前
'▼ダミーシートを削除（ダミーシート以外のシートが無いとエラーになるので、一応シート枚数を数えてから実行）
If .Worksheets.Count > 1 Then Worksheets(1).Delete
End With
End Sub
■５
肝心の質問のほうは、既に述べたアプローチは検討はされたのでしょうか？
「2-1」は要らないっぽいので↓のようなアプローチで解決しそうですが、思う通りの結果になりませんでしたか？
Sub 研究用2()
Dim SH As Worksheet
Dim MyName As Name
With ThisWorkbook
On Error Resume Next
For Each MyName In .Names
MyName.【名前の定義を削除する命令】
Next MyName
On Error GoTo 0
For Each SH In .Worksheets
SH.Cells.【入力規則】.【削除する命令】
Next SH
End With
End Sub
（もこな２ ） 2024/06/20(木) 12:38:11
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.