[[20170801143312]]　『マクロでシート名指定の方法』（おれんじー）　ページの最後に飛ぶ

『マクロでシート名指定の方法』（おれんじー）

 お世話になります。よろしくお願いします。

 簡単なマクロを組んで使っていたのですが、変更する必要がでました。
以下のようなとき、一般的にどのようにするものなのでしょうか？

 今までシート名を指定してそのシートでのみ使っていました。

     Set ws1 = Worksheets("AAA(aaa)AAA")
     Set ws2 = Worksheets("AAA(bbb)AAA")
 　　　：

 これまでこれで間に合っていましたが、AAAの分類以外も扱うことになり、
AAAと同じことをBBBやCCCでもやることになりました。
（BBB(aaa)BBB、BBB(bbb)BBB……CCC(aaa)CCC、CCC(bbb)CCC…というシートが増えました）

 AAAと同じ表で同じ処理なので、BBBのシートを開いているときはBBB分類のシート名のものだけを指定したいのですが、
どういう風にするべきでしょうか？
どこかセルに分類名を書いておいて、それをひっぱるとか？
処理方法の考え方がわかりません。

 どうぞよろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 これを、どう使うかですかね？

 msgbox activesheet.name
（BJ） 2017/08/01(火) 14:59

シート指定する他に、ActiveSheet というオブジェクトを使う事ができます。これは、現在アクティブになっているシートを指しますので、これに対して処理するとか？

または、シートを増やす度にWorkSheetオブジェクトを増やすのではなく、以下のように全シート分ループするマクロにするのも良いでしょう。

 Sub test()
    Dim i As Long

    For i = 1 To Sheets.Count
        With Sheets(i)
            'ここに1シート分の処理を書く
        End With
    Next i
 End Sub
（???） 2017/08/01(火) 15:02

 BJ様、???様

 ご指導ありがとうございます。
activesheetでシート名をとったり指定したりできるのですか。
ではこのあとにカッコの前部分を取り出してそれがついているシートを
順番にws1～に当てはめていく…という考え方になるのでしょうか？
調べてみます。

 > または、シートを増やす度にWorkSheetオブジェクトを増やすのではなく、以下のように全シート分ループするマクロにするのも良いでしょう。

 これは楽そう…と心揺れましたが、すみません、説明不足でした。
AAAのものをAAAまとめシートにまとめるマクロなのです。
増えたのでBBBまとめとCCCまとめと・・・と手作業で増やしておいて、
BBBのシート名のものはBBBまとめにまとめたく思っております。

 全シートループだと１つのまとめシートにまとまってしまうようです。

（おれんじー） 2017/08/01(火) 15:47

InStr関数を使って括弧の位置を調べ、括弧より前だけ取り出せば、AAAだけになります。また、括弧がある、または無い場合だけを処理対象にすれば、全シートループでも良さそうに思いますね。

まぁ、具体例がいまいち判らないので、何が適しているかまで教えられないのですが、何かしら元データシートとまとめシートの命名規則に違いがあるのでしょうから、それを判定すれば良いと思いますよ。
（???） 2017/08/01(火) 16:48

Sub main()

dim As String, x As String
ac = ActiveSheet.Name
x = Left(ac, IIf(InStr(ac, "("), InStr(ac, "(") - 1, Len(ac)))

Set ws1 = Worksheets(x & "(aaa)" & x)
Set ws2 = Worksheets(x & "(bbb)" & x)

End Sub

（mm） 2017/08/02(水) 09:51

???様

 ご指導ありがとうございます。

 > まぁ、具体例がいまいち判らないので

 すみません。現在のマクロを貼るべきでした。
 シロウトが書いたお恥ずかしい限りのマクロなのですが。
 　
Sub MATOME()
    Dim WS1 As Worksheet  
    Dim WS2 As Worksheet  
    Dim WS3 As Worksheet  
    Dim WS4 As Worksheet

    Set WS1 = Worksheets("AAA(aaa)")
    Set WS2 = Worksheets("AAA(bbb)")
    Set WS3 = Worksheets("AAA(ccc)")
    Set WS4 = Worksheets("まとめ")

        With WS1.UsedRange

            If .Rows.Count > 1 Then
               .Offset(1, 0).Resize(.Rows.Count - 1).Copy _
                   Destination:=WS4.Cells(Rows.Count, 1). _
                               End(xlUp).Offset(1, 0)
            End If

        End With

        With WS2.UsedRange

            If .Rows.Count > 1 Then
               .Offset(1, 0).Resize(.Rows.Count - 1).Copy _
                    Destination:=WS4.Cells(Rows.Count, 1). _
                                End(xlUp).Offset(1, 0)
                End If

        End With

        With WS3.Range("A1").CurrentRegion

            If .Rows.Count > 1 Then
                .Offset(1, 0).Resize(.Rows.Count - 1).Copy _
                    Destination:=WS4.Cells(Rows.Count, 1). _
                                End(xlUp).Offset(1, 0)
            End If

        End With

End Sub

 AAA(aaa)シート
日付		分類		名称	数量
2017/7/1	AAA(aaa)	あああ	20
2017/7/2	AAA(aaa)	あああ	32
2017/7/3	AAA(aaa)	あああ	51
2017/7/4	AAA(aaa)	あああ	34

 他シートも同じ表
まとめシートには各シートにあるこの表が順番に並びます。

 現在はAAA分類しかありませんので、まとめシートが１つしかありません。
これがAAAまとめシートとBBBまとめシートと・・・・とまとめシートだけで7つに増やす予定です。
AAA分類は3シートにわたってデータがありますが、BBBは2シート、CCCは1シート等、データシート数は分類によって変わります。

 そこで、各分類のまとめシートを開いてからマクロを実行することにし、
現在開いているまとめシートのシート名から対象データシートを判別したいです。

mm様

 ご指導ありがとうございます。
「AAAが含まれるワークシート」の指定がわからず調べている最中でした。
これでやってみます！

 仕事の関係で試すのは午後になってしまいます、お許しください。
また書き込みます。
（おれんじー） 2017/08/02(水) 10:46

なかなか素直なコーディングで、レイアウトがばっちり掴めましたよ。変更案なぞ。

 Sub test()
    Dim DIC As Object
    Dim i As Long
    Dim iw As Long
    Dim cw As String

    Set DIC = CreateObject("Scripting.Dictionary")

    For i = 1 To Sheets.Count
        With Sheets(i)
            iw = InStr(.Name, "まとめ")
            If 0 < iw Then
                DIC.Add Left(.Name, iw - 1), .Name
            End If
        End With
    Next i

    For i = 1 To Sheets.Count
        With Sheets(i)
            iw = InStr(.Name, "まとめ")
            If iw = 0 Then
                iw = InStr(.Name, "(")
                If 0 < iw Then
                    cw = Left(.Name, iw - 1)
                    With .UsedRange
                        If 1 < .Rows.Count Then
                           .Offset(1, 0).Resize(.Rows.Count - 1).Copy _
                               Destination:=Sheets(DIC(cw)).Cells(Sheets(DIC(cw)).Rows.Count, 1). _
                                           End(xlUp).Offset(1, 0)
                        End If
                    End With
                End If
            End If
        End With
    Next i

    Set DIC = Nothing
 End Sub
（???） 2017/08/02(水) 11:35

 mm様

 遅くなりましてすみません。
お教えいいただいたものから、現在使用しているマクロを修正できました。
本当にありがとうございます。
ただ、分類によってシート数が違うことにより、うまくいかない場合も出ました。
エラー回避をしなくてはいけませんね。
せっかくですので、考えてみようと思います。
 　
 　
 ???様

 ご指導本当にありがとうございます。
全分類のまとめ、一気に全部まとまりました！感動しました。
実はまだ、全部の構文が何をやっているか、完全理解してません。
特にScripting.Dictionaryの説明を読んでも今一つ……。
最初、全シート名が入っているのかと思って、分類名はキーにならないんじゃ…と悩みました。
（理解が悪くて書き込みが遅くなりました。申し訳ありません）

 また調べて覚えようと思います。
 本当にありがとうございました。
（おれんじー） 2017/08/03(木) 17:00

Scripting.Dictionaryのところだけは、ちょっとだけ難しいので、解説付けます。
これは、辞書と呼ばれるオブジェクトであり、Excel本来の命令ではありません。しかし、うまく使えばとても便利なものです。

今回のサンプルコードでは、For i のループが2つありますね。 1回目のForループは、シート名に「まとめ」が含まれるものを対象に、辞書を作成しているのです。ステップ実行してみれば、具体的にどういう値が格納されるのか判りますが、たとえば「AAAまとめ」を見つけると、キーは「AAA」、アイテムは「AAAまとめ」として辞書に1件追加します。BBB等も同様に、「まとめ」が含まれていたら1件辞書追加します。

次に、2つ目のForループですが、今度は「まとめ」以外のシート名に反応します。例えば「AAA(aaa)」ですね。これも文字列検索等で「AAA」だけ抜き出し、cwという変数に代入しています。

コピー元は現在調べているシートで良いですが、コピー先はAAAに該当するまとめシートですよね。これを、DIC("AAA")とすると、答えが「AAAまとめ」と返るのです。これで、コピー先を決定している訳ですよ。
（???） 2017/08/03(木) 17:28

 ???様

 すみません、金曜に社外に出てて気が付くのが遅くなりました。
 失礼いたしました。

 丁寧な解説をありがとうございます。
おかげさまでだいぶ内容がわかった（気になってる）と思います。
２つめのループで「「まとめ」以外のシート名に反応」させるところなんて、
自分ではとても思いつかない処理です。
こんな風に書けるようになりたいです。

 辞書は一度リストを作っておけば、後の処理でかなり応用が利きそうですので、
使えるように勉強してみます。
 楽しみです。

 最後のフォローまでありがとうございました。

（おれんじー） 2017/08/07(月) 15:32

コメント返信：

[ 一覧(最新更新順) ]

[[20170801143312]] 『マクロでシート名指定の方法』（おれんじー） ページの最後に飛ぶ

[[20170801143312]]　『マクロでシート名指定の方法』（おれんじー）　ページの最後に飛ぶ