[[20150116130942]] 『ブックすべてに非表示処理を行うマクロ』(祐希) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『ブックすべてに非表示処理を行うマクロ』(祐希)

はじめましていつもいろいろな方々を参考にさせてただいています。
今回いろいろなところを拝見しましたがどうしてもわからなず、日にちも迫っていたため書き込みさせていただきました。

【1】一覧を作成。
【2】G行に入っている種類ごとにシートに分ける。
【3】シート毎にブックに分ける。

上記の3つの作業をしています。

【2】と【3】の各作業毎にマクロを作る事はできたのですが、
【3】の作業を行いブックに分かれた際に、非表示にしていた列(A〜E、K、J、P〜U)が
表示されてしまいます。

その為、フォルダ内のブックすべてに非表示処理(削除ではない)をできるマクロを作りたいです。

また可能であれば、マクロに詳しくない人でも使えるように『マクロを組んだブックを入れたフォルダに入っているブック』というようにできないでしょうか?

もう一つ、
同じく【3】の作業後、条件付書式が機能しません。
L列に新規と入っていたらピンク、既存と入っていたら水色といった条件が入っていました。

見てみると条件は入っているのですが、両方とも開いて確認しOK後に適用を押すという作業をしないと作動しませんでした。

こちらも同様の作業のマクロを作ることはできないでしょうか。
尚、非表示と同時に行える様にできるのでしょうか。

ブックの量が多く一つ一つ行うことが困難な為、どなたかご教示願います。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 【3】のマクロを見せていただくことはできますか?
 そちらを変更したほうが早そう。
(稲葉) 2015/01/16(金) 14:03

稲葉さん

さっそくお返事ありがとうございます。

Sub シートを独立したBookにする()
Dim s As Worksheet
For Each s In Worksheets
s.Copy
ActiveWorkbook.SaveAs s.Name
ActiveWorkbook.Close False
Next
End Sub

こちらを使っています。

(祐希) 2015/01/16(金) 14:14


 条件付き書式も消えないし、非表示もそのままですけど・・・

 もしかして、一覧に条件付き書式が入っている?
 であれば【2】の段階で消えていますよね?

 すべての
 コード
 表構成
 条件付き書式の数式
 提示頂けますか?

(稲葉) 2015/01/16(金) 14:36


 あ、ちなみに非表示だけの問題なら
 これでどうですか?
 このコードが入っているブックと同じフォルダにあるすべてのブック、シートの
 A:E,J:K,P:U列を非表示にする
    Sub test()
        Dim FN As String
        Dim WS As Worksheet
        FN = Dir(ThisWorkbook.Path & "\*.xls?")
        Do Until FN = ""
            If Not FN = ThisWorkbook.Name Then
                With Workbooks.Open(FN)
                    For Each WS In .Sheets
                        WS.Cells.EntireColumn.Hidden = False
                        WS.Range("A:E,J:K,P:U").EntireColumn.Hidden = True
                    Next WS
                    .Save
                    .Close
                End With
            End If
            FN = Dir()
        Loop
    End Sub

(稲葉) 2015/01/16(金) 14:53


稲葉さん
一応シートに分けた段階では色がついているし、非表示にもなっています。。。
ありがとうございますさっそく使ってみます!!

【2】は下記を使っています。
ちなみに1〜3行目に題名を入れ、4行目に科目を入れてているのですが、エラーが出てしまう為、1〜3をいったん削除し、シート分けが完了した後にグループ作業で挿入しています。


【2】
 '---------------------------------
 Sub Grouping()
 '---------------------------------
    Dim i%

    Application.ScreenUpdating = False
    With Worksheets(1)
        For i = 2 To .Range("G65535").End(xlUp).Row
            Call AddLine(i, .Cells(i, 7).Value)
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 '---------------------------------
 Sub AddLine(lineNum%, sheetName$)
 '---------------------------------
    Dim lastLine%

    Call checkAndMake(sheetName)
    lastLine = Worksheets(sheetName).Range("G65535").End(xlUp).Row + 1
    Worksheets(1).Rows(lineNum).Copy
    Worksheets(sheetName).Rows(lastLine).Insert Shift:=xlDown
End Sub

 '---------------------------------
 Sub checkAndMake(sheetName$)
 '---------------------------------
    Dim tmpWS As Worksheet
    On Error Resume Next
    Set tmpWS = Worksheets(sheetName)
    If tmpWS Is Nothing Then
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = sheetName
        Worksheets(1).Rows(1).Copy
        Worksheets(sheetName).Rows(1).Insert Shift:=xlDown
    End If
    On Error GoTo 0
End Sub


(祐希) 2015/01/16(金) 15:09


 昔懐かし(恥ずかし)のコードなので、一言だけ。
    Dim i%
    Dim lastLine%
 は
    Dim i As Long
    Dim lastLine As Long
 に修正してください。

 32768 行以上データがあったとき、エラーになりますので。

(Mook) 2015/01/16(金) 15:52


Mookさん

ありがとうございます。
さっそく変更させていただきます。

すいませんいろいろと参考にさせていただいています。
マクロ初心者なのですが、こんな方法があるのかと最近楽しくて仕方ありません。

(祐希) 2015/01/16(金) 15:58


 >マクロ初心者なのですが、こんな方法があるのかと最近楽しくて仕方ありません。 
 ですよねー。
 私もいろんな先生方のコード見るのが楽しくて仕方ないです。
(稲葉) 2015/01/16(金) 16:01

[[20050721182519]] 
 見つけた!!

(稲葉) 2015/01/16(金) 16:03


 もう10年も前ですか。ふぅ〜、年取ったわけだ。
 このコードは時々顔を見せ、何回か改版したので最新版は
[[20111030151618]] 『担当別に自動振り分け』(アイル)
 になっています。

 この版だと、ヘッダをコピーする機能もついているので、
 >ちなみに1〜3行目に題名を入れ、4行目に科目を入れてているのですが、エラーが出てしまう為、1〜3をいったん削除し、シート分けが完了した後に
 は回避できるかもしれません。
(Mook) 2015/01/16(金) 16:48

 こんにちは。

 アップされた【2】のコードを見ると、【2】の段階で列の非表示は解除されるようです。
 【2】と【3】のあいだで、手作業かなんかで非表示にしているのでしょうか。
 でないならば【3】のコードの冒頭部分に適宜列を非表示にするコードを追加すればよいと思いますが
 いかがでしょうか。

  
 >また可能であれば、マクロに詳しくない人でも使えるように『マクロを組んだブックを入れたフォルダに入っているブック』というようにできないでしょうか? 
 これはお勧めしませんねぇ。
 マクロに詳しくない人のためなら、ボタンをひとつ押せば作業が全部済むようにするべき。
 『マクロを組んだブックを入れたフォルダに入っているブック』みたいな難しいことを要求してはいけません。

 それに、おっしゃるとおりのコードにすると、
    【3】で、ファイルを保存して閉じて。
    そのあとでまたファイルを開いて、列を操作して、保存して、閉じる。
 こういう手順になります。でも
    列を操作して、保存して、閉じる。
 これだけで十分じゃないですか。

( 佳 ) 2015/01/18(日) 10:59


佳さん

ありがとうございます。

>【2】と【3】のあいだで、手作業かなんかで非表示にしているのでしょうか。

【1】の時点で表を作成後に A:E,J:K,P:U列を非表示にしています。

>でないならば【3】のコードの冒頭部分に適宜列を非表示にするコードを追加すればよいと思いますがいかがでしょうか。

すいません。どう繋いでいけば正しく動くのか、またどのようなコードを入れればいいのかわからなかった為ご質問させていただいておりました。

>マクロに詳しくない人のためなら、ボタンをひとつ押せば作業が全部済むようにするべき。

1〜3行目に題名を入れ、4行目に科目を入れてているのですが、エラーが出てしまう為、1〜3をいったん削除し、シート分けが完了した後にグループ作業で挿入している為、【2】と【3】の作業を分けてボタンと説明を記載したエクセルシートを用意していました。
もちろん、やりたい作業がすべてひとつで済ませられるのでしたらとても助かります。

>『マクロを組んだブックを入れたフォルダに入っているブック』みたいな難しいことを要求してはいけません。

こちらは、マクロをいじって作業をするフォルダのフルパスを指定してからマクロを実行してくださいといったものが多かったため。
作業をしたいファイルと同じフォルダに入れれば、ボタンを押すだけで作業を完了できる。
マクロの内容を変更しなくてすむようにしたい。という意味でした。
わかりにくい書き方をしてしまい申し訳ありません。

>【3】で、ファイルを保存して閉じて。
そのあとでまたファイルを開いて、列を操作して、保存して、閉じる。

この部分に関しては、【3】の作業後出来上がったファイルをフォルダにまとめて、フォルダ名等を変更しているためその際に、行ってもらっています。
(祐希) 2015/01/19(月) 09:44


稲葉さん、Mookさん

お返事が遅くなり申し訳ありません。
本日後程試してみます!!

条件付書式が引き継がれているのに実行されない件は、現在も不明です…
また、いろいろご教示ただけたらうれしいです!
(祐希) 2015/01/19(月) 09:54


Mookさん
すいません。
先日教えていただいたマクロを確認させていただきました。

知識がなさすぎるせいで理解ができておりません。

 '//--------------------------------------------------------
 '// 処理の行・列定義
 '//--------------------------------------------------------
 Const checkCol = "G"            '--- 元データの分割判定を行う列
 Const checkLastCol = "A"        '--- 各シートの最終列を判定する列
 Const rowUnitSize = 1           '--- コピー行単位
 Const dataStartLine = 5         '--- 各シートのデータ開始行(ヘッダ行+1)
 Const IDCol = "A"               '--- appendMode が 2 のときのみ使用:この列は重複データが無いように運用のこと

A〜E、J〜K、P〜U等は非表示
最終的にGの担当でブックを作りたいので、同じくGでシート毎に分けたいです。
下記の表のようなものの場合、上記の変更でいいのでしょうか?

  A     B     C     D      E       F    G       H ・・・・・
1
2セル結合題名等
3
4 CD1 エリア名  CD2  場所  エリア   CD3  担当   CD4←項目
5 001 北海道 555  札幌 01.北海道  555  札幌  123
6 001 北海道 556 苫小牧 01.北海道  556  札幌  353
7 002 東北  456 いわき 02.東北  456  いわき  125
8 002 東北  335  千葉 02.東北  335 いわき  122
9 002 東北  444  仙台 02.東北  444  仙台  205
・
・
・
・
※4の項目はすべて仮表記です

(祐希) 2015/01/19(月) 13:19


 各行にユニークな ID が無い場合、appendMode を 0 にするのであれば、上記の設定で
 良いと思います。

 試してみてダメだったのでしょうか。
(Mook) 2015/01/19(月) 13:55

Mookさん

実行エラー'9':
インディックスが有効範囲にありません。

上記のように出てしまいました。

    With ThisWorkbook.Worksheets(masterSheetName)
こちらが黄色く出ています。
(祐希) 2015/01/19(月) 14:25

     GroupingMain "ALL" '// 分割をしたいシート名を指定
 を実際の処理シート名に変更してください。

 マクロは不慣れでも、多少なりとも中身を理解する努力はあったほうがよいかと思います。
(Mook) 2015/01/19(月) 14:54

Mookさん

申し訳ありません。
そちらを変更しましたら使用することができました。

ヘッダのコピーや非表示設定も引き継げました。
なぜか条件付書式が引き継がれているのに実行されないのですが、

もっと基礎的なところから勉強してみようと思います。
お手数をお掛けしました。
(祐希) 2015/01/19(月) 15:46


 >なぜか条件付書式が引き継がれているのに実行されないのですが、 
 は、きちんと条件が正しい関係で残っているでしょうか。

 条件付書式はコピーや削除により、条件の参照セルや範囲がおかしく
 なることがあるので、確認したほうが良いような気がします。
(Mook) 2015/01/19(月) 16:17

Mookさん

範囲や条件はちゃんと残っています。
ブックを分割後にファイルを開き、
条件付き書式を選択、条件を確認して異常がないのでokをクリック、適用を押すと条件が反映されます。

ブック分割の時に何か入ってしまっているのかなと思って調べてはいるのですが、現段階ではちょっと原因を見つけられていません…。

(祐希) 2015/01/19(月) 17:32


 簡単にテストしてた範囲では、計算が手動になっていても、貼り付けたタイミングで
 反映しましたが、更新されない条件があるのでしょうか。

 設定されている条件式が不明なので、詳細はわかりませんが、適用を押すと反映するので
 あれば、マクロの処理の最後に同様の処理を追加してあげればよいような気がしますが、
 簡単なところで
 Application.ScreenUpdating = True
 を追加してダメでしょうか。
(Mook) 2015/01/20(火) 11:42

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.