全シートに対して連続実行する方法について

追加したいこと。

１、グラフ選択機能（グラフの名前がそれぞれ違うのでそれをどう解決すれば良いのか…）
２、次シートへの自動移動

これは、全１１シートしかないのですがもう一つ「１ファイル８シート」が１６ファイル
あるので短時間で変更したいのです。

Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count

        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
        MsgBox ActiveWorkbook.Worksheets(I).Name
        ChartObjects_Select
        graph_change
        

     Next I

  End Sub

Sub ChartObjects_Select()

ActiveSheet.ChartObjects("グラフ 60").Activate 'グラフの選択
__↑ここのグラフ選択はマクロの記録でやってしまっているため”グラフ６０”となっています。ここも、変更しないと他に対応が出来ません。__

End Sub
Sub graph_change()
Dim str1 As String, str2 As String, I As Integer

str1 = InputBox("変更前の値を入力してください")
If str1 = "" Then Exit Sub
str2 = InputBox("変更後の値を入力してください")
If str2 = "" Then Exit Sub

With ActiveChart
For i = 1 To .SeriesCollection.Count
.SeriesCollection.Item(I).Formula = Replace(.SeriesCollection.Item(I).Formula, "$" & str1, "$" & str2)
Next I
End With
End Sub

追加情報
⚫１→「10月度計測」と言うファイルがあり全シート11枚(１ヶ月分1ファイル)（各シートグラフ１つ、１１シート以外にデータシートが４シート）
⚫２→「10月度a地点計測」と言うファイルがありその中にシートが「1」～「8」まであります。そのファイルを1セットにして16ファイルあります。
(１ヶ月分16ファイル)（各シートグラフ２つ、８シート以外にデータシートが４シート）
◎３→１シートに、３つと２つの混合（「３，３、２」×８）

全ファイルを、一括と言うのは何かあってら大変なので1ファイルずつ全シート一括変更がせめて出来たらと思っています。

グラフの変更範囲は、１の全シートについて同じ。２の全ファイルの全シートについて同じ。
なので、１回指定したらシート毎に聞かれる事なく動いてくれるのが望ましいです。

グラフは、今書かれてる位置のままで状況によって3～4日増えるのでそれを、なんとか自動化できないものかと思ってます。

takito

2015/10/27 08:18

つまり、・エクセルファイルが多数ある・全てのファイルにはシートが多数ある・全てのシートにはデータが入力されていてグラフ描画されている・全ファイル、全シート、全グラフ、のデータ範囲を任意のセルに一括変更したいという理解でよいでしょうか？

行動規範の内容に同意します

回答2件

ベストアンサー

ワークシート、そこに埋め込まれているグラフ、グラフシートなどは、その「名前」を知らなくともインデックス番号によって指定することができます

例えばマクロの処理をこんなイメージで作ってはいかがでしょうか

以下の処理を行うための実行用ブックを作成
１．ブックを開く
２．グラフデータ範囲参照セルの変更前と変更後の入力を受け付ける
３．開いているブックの全シート、全グラフに対して３の内容を反映する
４．ブックを保存して閉じる
５．処理対象ブックに対して以上の１～４を繰りかえす

具体的な処理はこちらのサイトが参考になると思います
ブック内のすべてのワークシートをループするマクロ（これを応用すればグラフシートのループもできます）
グラフ操作の基礎知識
 グラフの参照範囲を変更する

また、マクロを使わない方法でもグラフの参照式を動的変化に対応させることができます
グラフのデータ範囲を自動更新する方法
 グラフの参照範囲を自動的に変更する
が、グラフが大量にあるようなのであくまでも参考にということで

ざっくりとした（でも一応動きます）サンプルコードを書いてみました
細かいエラー処理までは考えてません
「シートにグラフが埋め込まれてる」というイメージで作ってありますので、もしも「グラフシート」ならばシートのループを工夫する必要があります
よかったら参考にしてください

vbscript
1
2Sub sample()
3    
4    Dim strPath As String
5    Dim strBookName As String
6    Dim strBefore As String
7    Dim strAfter As String
8    Dim myWS As Worksheet
9    Dim WB As Workbook
10    Dim nRow As Integer
11    Dim i As Integer
12    Dim j As Integer
13    Dim k As Integer
14
15    'このマクロを動かすブックの先頭シートに処理対象のファイル名リストを作成
16    'ファイル名はＡ列先頭行から行方向に記述
17    'ファイル名が空欄になったら処理を終了
18    
19    'このマクロを動かすブックの先頭シート
20    Set myWS = ThisWorkbook.Worksheets(1)
21    
22    '計測データファイルの保存されている場所
23    strPath = "c:\計測データ"
24    
25    nRow = 1
26    'ファイル名のループ
27    Do While Len(myWS.Cells(nRow, 1).Value) > 0
28
29        'ファイルを開く
30        strBookName = myWS.Cells(nRow, 1).Value
31        Set WB = Workbooks.Open(strPath & "\" & strBookName)
32        
33        '置換情報の入力
34        strBefore = UCase(InputBox("変更前の値を入力してください", strBookName))
35        strAfter = UCase(InputBox("変更後の値を入力してください", strBookName))
36        
37        '入力が無かったら次へ
38        If strBefore = "" Or strAfter = "" Then GoTo NEXT_FILE
39        
40        '処理の確認
41        If MsgBox("[" & strBookName & "] 内の全グラフの参照先を" & vbCrLf _
42                   & strBefore & " → " & strAfter & vbCrLf _
43                   & "に変更してよろしいですか？", vbOKCancel) = vbCancel Then
44                   GoTo NEXT_FILE
45        End If
46        
47        '置換処理の実行
48        'シートのループ
49        For i = 1 To WB.Worksheets.Count
50            'グラフのループ
51            For j = 1 To WB.Worksheets(i).ChartObjects.Count
52                With WB.Worksheets(i).ChartObjects(j).Chart
53                'グラフの参照先情報のループ
54                For k = 1 To .SeriesCollection.Count
55                    .SeriesCollection.Item(k).Formula = Replace(.SeriesCollection.Item(k).Formula, "$" & strBefore, "$" & strAfter)
56                Next k
57                End With
58            Next j
59        Next i
60        
61NEXT_FILE:
62        '閉じる（変更があれば聞かれる）
63        WB.Close
64        
65        nRow = nRow + 1
66        
67    Loop
68        
69End Sub
70

投稿2015/10/28 08:33

編集2015/10/30 10:10

takito

総合スコア3111

cat_junko

2015/10/29 03:51

シートループは、解決できたのですが「３」についてのやり方が思いつきません。「２」で、変更前と変更後で値を入力してその値をどう次シート以降に反映すれば良いのかが分かりません。宜しくご教示願います。

takito

2015/10/29 04:45

お忙しいと思いますが、もしよろしければシートのループまでできているコードを質問に追記していただけませんか？その方が「ここをこうする」という回答、提案がしやすいです

takito

2015/10/30 10:21

サンプルコードを追記しましたヘタですが動くものにはなってると思いますご参考まで

cat_junko

2015/10/31 00:21

返信遅くなり大変申し訳ありません。最終的に、下記のようになりました。「変更前、変更後」の入力を促すのに加えて「シート内のグラフ数」と「シート数」の入力を促すことによりどんなグラフ？にも対応出来るようにしました。 Sub graph_change() Dim wb As Workbook, ws As Worksheet Dim str1 As String, str2 As String, i As Integer, j As Integer Dim str3 As String, str4 As String str1 = InputBox("変更前の値を入力してください") If str1 = "" Then Exit Sub str2 = InputBox("変更後の値を入力してください") If str2 = "" Then Exit Sub str3 = InputBox("グラフの数を入力してください") If str3 = "" Then Exit Sub str4 = InputBox("対象シート数を入力してください") If str4 = "" Then Exit Sub On Error Resume Next For Each wb In Workbooks For Each ws In wb.Worksheets For j = 1 To str3 With ws.ChartObjects(j).Chart For i = 1 To str4 .SeriesCollection.Item(i).Formula = _ Replace(.SeriesCollection.Item(i).Formula, "$" & str1, "$" & str2) Next i End With Next j Next ws Next wb End Sub

cat_junko

2015/10/31 00:24

また、回答いただいたサンプルでも試して色々勉強していきたいと思います。ありがとうございました！

行動規範の内容に同意します