Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2010)
条件でシートを選択して新規ブックを作成する方法
投稿日時: 17/12/15 02:43:55
投稿者: vicky_T

VBA初心者です。
よろしくお願いします。
 
以下の内容で作成しているブックがあります。
シート[印刷設定][A][B][C][D][E][F][名簿]
 ・シート[名簿]A列・・・会社
        B列・・・シート[A]必要なら○
          〜
        G列・・・シート[F]必要なら○
 
 ・シート[印刷設定]セルC3・・・開始番号[会社]
          セルE3・・・終了番号[会社]
          セルC8・・・○が入っていたら[印刷]
               ( VLOOKUP($C$3,名簿!$A$1:$G$10000,2,FALSE)&"")
              〜
          セルC13・・・○が入っていたら[印刷]
               (VLOOKUP($C$3,名簿!$A$1:$G$10000,7,FALSE)&"")
          セルC14・・・セルC8~C13のどれかに○が入っていたら○
 
以下のVBAで会社b指定して連続印刷を行っています。
Sub 連続印刷()
   Dim 番号 As Integer
   Dim 開始番号 As Integer
   Dim 終了番号 As Integer
   Dim 印刷部数 As Integer
    開始番号 = Sheets("印刷設定").Range("C3")
    終了番号 = Sheets("印刷設定").Range("E3")
    印刷部数 = 1
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
      
   For 番号 = 開始番号 To 終了番号
       Sheets("印刷設定").Range("C3").Value = 番号
If Sheets("印刷設定").Range("C14") = "○" Then
 With Sheets("印刷設定")
   .Select
     If .Range("C8") = "○" Then
         Sheets("A").Select
     End If
     If .Range("C9") = "○" Then
         Sheets("B").Select ActiveSheet.Name = .Name
     End If
     If .Range("C11") = "○" Then
         Sheets("C").Select ActiveSheet.Name = .Name
     End If
     If .Range("C12") = "○" Then
         Sheets("D").Select ActiveSheet.Name = .Name
     End If
     If .Range("C13") = "○" Then
         Sheets("E").Select ActiveSheet.Name = .Name
     End If
     If .Range("C14") = "○" Then
         Sheets("F").Select ActiveSheet.Name = .Name
     End If
  End With
     ActiveWindow.SelectedSheets.PrintOut
 End If
       Next 番号
      
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
    Sheets("印刷設定").Select
 End Sub
 
これから、印刷だけではなく、同じ条件で新規にブックを作成することになりました。
 > ActiveWindow.SelectedSheets.PrintOut
この部分をどうにかアレンジして作ること可能でしょうか?
勉強不足で申し訳ありません。
 
上記方法では無理でしたら他の方法をどうかご教授ください。
よろしくお願いします。
ファイル名は、シート[印刷設定]セルA1
保存先パスは、シート[印刷設定]セルA2 に入力されているデータを利用したいです。
 

回答
投稿日時: 17/12/15 10:50:43
投稿者: mattuwan44

シートをグループ化したいんだと思いますが、
>Sheets("B").Select ActiveSheet.Name = .Name
これはどういう意図で書いてます?日本語で説明してみてください。
 
それから、
ちょっと、単純にSelectする考え方だと臨機応変に対応できないきがします。
配列に入れちゃう方がいいと思いますが、「配列」ってわかりますかね?
解らなければ少し検索して勉強してみてください。

回答
投稿日時: 17/12/15 17:07:17
投稿者: もこな2

私も、mattuwan44さんと同じところがよくわからなかったので、テストしてみたらちゃんと作業グループになってくれましたので、そういう書き方もあるのか〜と勉強になりました。
 
ただ、ちょっと好みではなかったので、こんなやり方もありますよというのを紹介しておきます。

引用:
セルC14・・・セルC8~C13のどれかに○が入っていたら○
 
If .Range("C14") = "○" Then
 Sheets("F").Select ActiveSheet.Name = .Name
End
という仕掛けですから、こんな感じにもできますよね
With ThisWorkbook
    If .Sheets("印刷設定").Range("C14").Value = "○" Then
        .Sheets("F").Select
        If .Sheets("印刷設定").Range("C8") = "○" Then .Sheets("A").Select False
        If .Sheets("印刷設定").Range("C9") = "○" Then .Sheets("B").Select False
        ’なんでC10がないのかよくわかりませんが・・・
        If .Sheets("印刷設定").Range("C11") = "○" Then .Sheets("C").Select False
        If .Sheets("印刷設定").Range("C12") = "○" Then .Sheets("D").Select False
        If .Sheets("印刷設定").Range("C13") = "○" Then .Sheets("E").Select False

        ActiveWindow.SelectedSheets.PrintOut
    End If
End With
(mattuwan44さんの仰るように配列使った方がきれいで見やすいとはおもいますが、始めに初心者といっているので、いきなり配列はちょっとハードルが高いかなと思ったので、Selectで対応する方法にしてみました。)
 
さて、ご質問の
引用:
これから、印刷だけではなく、同じ条件で新規にブックを作成することになりました。
 > ActiveWindow.SelectedSheets.PrintOut
この部分をどうにかアレンジして作ること可能でしょうか?
については、↓のような方法でどうでしょうか。
ActiveWindow.SelectedSheets.Copy
(プリントアウトじゃなくてシートを(新規ブックに)コピーしたいのでPrintOutメソッドをコピーメソッドに変更してみました。)
 
コピーした先のブックに名前を付けて保存する方法はちょっといい方法が思いつかないので、ほかの回答者様のアドバイスを参考にしてみてください。
(ActiveBookの操作でとりあえずは大丈夫だとおもいますが、他にいい方法があるかもしれません。)
 
以上をまとめるとこんな感じのものをループに組み込んでやれば希望の動作になりませんか?
Sub Sample()

With ThisWorkbook.Sheets("印刷設定")
    If .Range("C14").Value = "○" Then
        '---コピー対象のシートを選択
        .Parent.Sheets("F").Select
        If .Range("C8") = "○" Then .Parent.Sheets("A").Select False
        If .Range("C9") = "○" Then .Parent.Sheets("B").Select False
        If .Range("C11") = "○" Then .Parent.Sheets("C").Select False
        'C10は無くて大丈夫なんでしょうか?
        If .Range("C12") = "○" Then .Parent.Sheets("D").Select False
        If .Range("C13") = "○" Then .Parent.Sheets("E").Select False

        '---選択されているシートを新規ブックへコピー
        ActiveWindow.SelectedSheets.Copy

        '---アクティブ(になっている新規)ブックを名前を付けて保存
        ActiveWorkbook.SaveAs _
            Filename:=.Range("A1").Value & _
            Application.PathSeparator & _
            .Range("A2").Value

        '---アクティブ(になっている新規)ブックを閉じる
        ActiveWorkbook.Close

    End If
End With
参考になれば、研究してみてください。

投稿日時: 17/12/18 02:13:56
投稿者: vicky_T

mattuwan44様・もこな2様
 ご回答いただきありがとうございます。
 
 申し訳ありません。
 もこな2様のご指摘のとおり、私の記述に間違いがありました。
 
 If Sheets("印刷設定").Range("C14") = "○" Then
  With Sheets("印刷設定")
  .Select
  If .Range("C8") = "○" Then
         Sheets("A").Select
  End If
   If .Range("C9") = "○" Then
         Sheets("B").Select ActiveSheet.Name = .Name
   End If
   If .Range("C10") = "○" Then
         Sheets("C").Select ActiveSheet.Name = .Name
   End If
  If .Range("C11") = "○" Then
         Sheets("D").Select ActiveSheet.Name = .Name
   End If
   If .Range("C12") = "○" Then
         Sheets("E").Select ActiveSheet.Name = .Name
    End If
    If .Range("C13") = "○" Then
         Sheets("F").Select ActiveSheet.Name = .Name
   End If
  End With
    ActiveWindow.SelectedSheets.PrintOut
 End If
 
 C14に○が入っていたら印刷する。
 (○がなかったら、印刷不要)
 C8に○が入っていたらシート[A]必要
   〜
 C13に○が入っていたらシート[F]必要となります。
 
今回のご質問の新規ブック作成に際しては
 C14に○が入っていたら新規Excelブック作成する。
 (○がなかったら、新規Excelブック作成不要)
 C8に○が入っていたら新規Excelにシート[A]必要
   〜
 C13に○が入っていたら新規Excelにシート[F]必要となります。
 
 
もこな2様
 ご丁寧なアドバイスありがとうございます。
   '---アクティブ(になっている新規)ブックを名前を付けて保存
      ActiveWorkbook.SaveAs _
        Filename:=.Range("A1").Value & _
        Application.PathSeparator & _
        .Range("A2").Value
 残念ながら、この部分がエラー(実行時エラー'1004')になってしまいます。
 
mattuwan44様・もこな2様
 『配列』については、ほとんど知識がありません。
 検索してみましたが、まだ、ぼんやりとしたイメージだけです。
 ただ、ヒントをいただきましたので、これから使うことができるよう勉強したいと思います。

回答
投稿日時: 17/12/18 04:49:38
投稿者: もこな2

vicky_T さんの引用:
'---アクティブ(になっている新規)ブックを名前を付けて保存
      ActiveWorkbook.SaveAs _
        Filename:=.Range("A1").Value & _
        Application.PathSeparator & _
        .Range("A2").Value
 残念ながら、この部分がエラー(実行時エラー'1004')になってしまいます。
ちょっとミスしてたので、↓のとおりであれば、そうなりますよね・・・・
引用:
ファイル名は、シート[印刷設定]セルA1
保存先パスは、シート[印刷設定]セルA2 に入力されているデータを利用したいです

回答
投稿日時: 17/12/18 10:25:27
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
'---アクティブ(になっている新規)ブックを名前を付けて保存
      ActiveWorkbook.SaveAs _
         Filename:=.Range("A1").Value & _
         Application.PathSeparator & _
         .Range("A2").Value
 残念ながら、この部分がエラー(実行時エラー'1004')になってしまいます。
ファイル名は、シート[印刷設定]セルA1
 保存先パスは、シート[印刷設定]セルA2 に入力されているデータを利用したいです
 

 
 
セルA1とセルA2が逆に記述されているからです。

回答
投稿日時: 17/12/18 15:28:02
投稿者: もこな2

17/12/18 02:13:56の投稿への回答です
 
ふーむ、つまり、C14は印刷処理をするかしないかの判定であって、印刷をする場合必ず「F」を印刷するというわけではないのですね?そうすると、シートSelect (False)作戦はちょっと具合が悪いですね。
 
であれば、私も↓を参考に配列を検討することををオススメするかなぁ・・・
http://www.moug.net/tech/exvba/0040032.html
まぁ提示のあったやり方でも対応できるみたいなので、お好きな方で対応すればいいですね。
その場合、これも好みではありますが、VBAのIF文はElse節がなくて、Then節が1行で済む場合は、End Ifは省略可能というルールがあるので、下記のように整理すると後々、見やすいかもしれません。

With Sheets("印刷設定")
    If .Range("C14") = "○" Then
        .Select 'わざと「印刷設定」シートをselectして選択状況をクリア
        If .Range("C8") = "○" Then Sheets("A").Select ActiveSheet.Name = .Name
        If .Range("C9") = "○" Then Sheets("B").Select ActiveSheet.Name = .Name
        If .Range("C10") = "○" Then Sheets("C").Select ActiveSheet.Name = .Name
        If .Range("C11") = "○" Then Sheets("D").Select ActiveSheet.Name = .Name
        If .Range("C12") = "○" Then Sheets("E").Select ActiveSheet.Name = .Name
        If .Range("C13") = "○" Then Sheets("F").Select ActiveSheet.Name = .Name

        ActiveWindow.SelectedSheets.PrintOut
    End If
End With

更に言えば、「○」以外の文字が入力されても動くようにブランク以外なら処理という考え方にしたり、ループ処理にしてコードを短くしたりすることを考えるならばこういう感じにもできますね
Dim i As Integer
With Sheets("印刷設定")
    If .Range("C14") <> "" Then
        .Select 'わざと「印刷設定」シートをselectして選択状況をクリア
        For i = 2 To 7 Step 1
            If .Cells(6 + i, "C").Value <> "" Then _
                Sheets(i).Select ActiveSheet.Name = .Name
        Next i
        ActiveWindow.SelectedSheets.PrintOut
    End If
End With

 
引用:
残念ながら、この部分がエラー(実行時エラー'1004')になってしまいます。
については、答えがでてしまったのでアレですが、掲示板に回答のあったコードをそのままコピペして実行するのではなく、どのような働きをするかわからないメソッドなどがあればちょっと調べてみるなど、自分なりに研究しながら実行することをオススメします。
まぁ答えるときにミスするなと言われればそれまでなんですが(^_^;)

回答
投稿日時: 17/12/18 17:16:08
投稿者: mattuwan44

あぁ
 
>ActiveSheet.Name = .Name
これは、代入式ではなくて比較式だったのですね。。。
で、アクティブシートが印刷設定じゃなければFalseになると。。。
でも、その前に必ず印刷設定シートを選択しているのだから、
単純にFalseって書いて置けばいいような気がします。
そして、シートは選択しなくても、VBAのコードで指定できるので、
名前をどうにかして取得すればいいと思います。
 
Sub test()
    Dim v As Variant
    Dim ix As Long
    Dim ixBegin As Long
    Dim ixEnd As Long
    Dim ixNumber As Long
 
    With Worksheets("印刷設定")
        ixBegin = .Range("C3").Value
        ixEnd = .Range("E3").Value
        ixNumber = 1
 
        For ix = ixBegin To ixEnd
            v = ""
            If .Range("C14").Value = "○" Then
                .Range("C3").Value = ix
                v = .Name
                If .Range("C8").Value = "○" Then v = v & ",A"
                If .Range("C9").Value = "○" Then v = v & ",B"
                If .Range("C10").Value = "○" Then v = v & ",C"
                If .Range("C11").Value = "○" Then v = v & ",D"
                If .Range("C12").Value = "○" Then v = v & ",E"
                If .Range("C13").Value = "○" Then v = v & ",F"
            End If
             
            If Len(v) > 0 Then
                v = Split(v, ",")
                Worksheets(v).Copy
                With Workbooks(Workbooks.Count)
                    .PrintOut Copies:=ixNumber, Collate:=True
                    .Close True, .Range("A2").Value & "\" & .Range("A1").Value
                End With
            End If
        Next
    End With
End Sub
 
本当は、間違ってシートを削除しちゃう可能性がなくはないと思うので、
各シートの存在確認とかもしたいかもしれませんね。
でも、そうなると、も少し工夫しないとだめかもしれません。
 
ま、その辺は今後の課題として、現状は動くコードで運用していきながらですかね^^

回答
投稿日時: 17/12/18 18:06:59
投稿者: もこな2

トピ主ではないですが、たぶん、印刷(コピー)したいのは、A〜Fのシートであって、印刷設定シートはいらないんだろうなと思います。
実は、「ActiveSheet.Name = .Name」で、なんで「印刷設定シート」のSelectが外れて、A〜Fの複数Selectが掛かるかよく解ってなかったんですよね。

mattuwan44 さんの引用:
>ActiveSheet.Name = .Name
これは、代入式ではなくて比較式だったのですね。。。
で、アクティブシートが印刷設定じゃなければFalseになると。。。
この発言でようやく理解しました。要は、条件に合う最初のシートは、「印刷設定」シートがアクティブのときに「ActiveSheet.Name = .Name」を判定するからTrueになって、ただのselectになってるんですね。
で、2番目以降のシートからは、1番目のシートがアクティブになっているため「ActiveSheet.Name = .Name」がFalseになるから、選択が解除されずに追加になってると。
めちゃめちゃ勉強になりました。
 
Workbooks(Workbooks.Count)のアイデアも目から鱗です。
便乗して勉強させていただきました。ありがとうございました。m(_ _)m

回答
投稿日時: 17/12/19 17:06:38
投稿者: mattuwan44

>、たぶん、印刷(コピー)したいのは、A〜Fのシートであって、印刷設定シートはいらないんだろうなと思います。
 
そかぁ。。。
了解です。
 
vicky_Tさん。
じゃ、With句を使わない方が行儀のいいコードになるかなぁ。
With句を使わない方向で書いて、勉強してみるといいかもです。

回答
投稿日時: 17/12/19 19:31:22
投稿者: もこな2

mattuwan44 さんの引用:
>、たぶん、印刷(コピー)したいのは、A〜Fのシートであって、印刷設定シートはいらないんだろうなと思います。
 
そかぁ。。。
了解です。
自分はトピ主(=vicky_Tさん)ではないので、あくまで推測ですが、質問時に提示のあったコードを読むと、Sheets("印刷設定") をSelectしてますが、そのあとで、1個前の私の回答のとおりA〜Fに対してFalseが付いてないselectが実行されるので、その時点でSheets("印刷設定") のSelectは解除される(=印刷対象から外れる)みたいです。
 
本当に要らないのかはトピ主のみぞ知るですね

投稿日時: 17/12/21 01:39:32
投稿者: vicky_T

もこな2様 mattuwan44様
説明が不足していて申し訳ありません。
ご想像の通り、印刷設定シートは新規ブックには不要です。
 
使う人が不特定のため、見た目分かりやすいよう
印刷設定シートを設け
セルC3に開始番号[会社]、セルE3に終了番号[会社] を入力し
印刷ボタンを押下すると、A〜Fのうち○のシートが印刷されるという仕組みで運用しています。
 
同じように、会社毎に新規ブックを作成するボタンを追加して運用したいと考えています。
(印刷と新規ブック作成は別々に運用します。)
それからA〜Fに入っている計算式は残したくないので、
新規ブック作成時に値貼り付けをしたいのですが、
どのように記述すればよいでしょうか?
 
どうぞよろしくお願いします。

回答
投稿日時: 17/12/21 09:58:50
投稿者: もこな2

vicky_T さんの引用:
それからA〜Fに入っている計算式は残したくないので、新規ブック作成時に値貼り付けをしたいのですが、どのように記述すればよいでしょうか?
いきなりコードを答えてもなかなか理解しづらいかとおもいますので、考え方を回答します。
 
まず、手動でやろうとしたらどんな処理をするか考えてみましょう
いろんなアプローチある思いますけど、方法の一つとして
 
新規に作成されたブックの各シートそれぞれで
 @全セルを選択してコピー
 Aそのまま(全セルに対して)「形式を選択して貼り付け」−「値」
ってやり方もありますよね
 
これをコード化する方法を考えてみてはどうでしょうか。
ヒント:
For Each 「ワークシート」 in 新規に作成されたブックのシートコレクション
 「ワークシート」の全セルをコピー
 「ワークシート」の全セルに「形式を選択して貼り付け」−「値」
Next

トピックに返信