VBA オートフィルターでデータ取得

Question

Private Sub CommandButton1_Click()
Dim Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Worksheets("Sheet1")
Set Sh2 = Worksheets("Sheet2")
Dim keyWord As String
keyWord = Control1.Caption & "_" & Control2.Caption & "_" & Control3.Caption

'Sh1のデータをオートフィルターする
        With Sheets("Sheet1").Range("A1")
        .AutoFilter field:=1, Criteria1:=keyWord
        .Offset(1).Columns("A:D").Copy
    End With
    'Sheet2の２列目の最後の行の下に貼付
    With Sh2.Range("A1").CurrentRegion
        .Cells(.Rows.Count + 1, 1).PasteSpecial
    End With    

    Sh1.Range("A:D").AutoFilter
    Sh1.Activate
End Sub

＜実現したいこと＞
以前、「VBA 複数のCheckBoxの組み合みせについて」でユーザーフォームのオプションボタンを作成の仕方を教えてもらい、その選択したボタンの結果をkeyWordとしてSheet1のシートをオートフィルターし、Sheet2に転記、データの抽出と貼り付けを複数回繰り返しデータ一覧を作成しようとしようとしています。

＜エラー箇所＞
1.コマンドボタン１を押したら、keyWordで検索しようとしていますが、検索してくれません。ユーザーフォームで押したボタンの値をオートフィルターをしようとしています、コードの修正箇所についてご教示ください。

2.コマンドボタン２を押したら、ユーザーフォームのボタンの値を一度にクリア（初期状態）にする方法を教えてください。

＜修正しました＞

Private Sub CommandButton1_Click()
  Dim Control1 As Control
  Dim Control2 As Control
  Dim Control3 As Control
  Dim keyWord As String
　keyWord = Control1.Caption & "_" & Control2.Caption & "_" & Control3.Caption
  
  For Each Control1 In Frame1.Controls
    If Control1.Value = True Then Exit For
  Next
  For Each Control2 In Frame2.Controls
    If Control2.Value = True Then Exit For
  Next
  For Each Control3 In Frame3.Controls
    If Control3.Value = True Then
      MsgBox Control1.Caption & "_" & Control2.Caption & "_" & Control3.Caption
      Exit For
    End If
  Next
  
   With Sheets("Sheet1").Range("A1")
        .AutoFilter field:=1, Criteria1:=keyWord
        .CurrentRegion.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 4).Copy
   End With
  
  'Sheet2の２列目の最後の行の下に貼付
    With Sheets("Sheet2").Range("A1").CurrentRegion
        .Cells(.Rows.Count + 1, 1).PasteSpecial
    End With
    
End Sub
コード

Answer 1

１．　.Offset(1).Columns("A:D").Copy　
は、　.CurrentRegion.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 4).Copy　でよいと思います。

コマンドボタン２は

Private Sub CommandButton2_Click()
  Dim trgControl As Control
  For Each trgControl In Frame1.Controls
     trgControl.Enabled = True
     trgControl.Value = False
  Next
  For Each trgControl In Frame2.Controls
     trgControl.Enabled = False
     trgControl.Value = False
  Next
  For Each trgControl In Frame3.Controls
     trgControl.Enabled = False
     trgControl.Value = False
  Next
  Worksheets("Sheet1").Range("A1").AutoFilter
End Sub

２．フイルターの解除を

Private Sub UserForm_Initialize()
  Worksheets("Sheet1").Range("A1").AutoFilter
End Sub

と必要か所に　Worksheets("Sheet1").Range("A1").AutoFilter　を

追記

Private Sub CommandButton1_Click()
  Dim Control1 As Control, Control2 As Control, Control3 As Control
  Dim Sh1 As Worksheet, Sh2 As Worksheet
  Set Sh1 = Worksheets("Sheet1")
  Set Sh2 = Worksheets("Sheet2")
  For Each Control1 In Frame1.Controls
    If Control1.Value = True Then
      For Each Control2 In Frame2.Controls
        If Control2.Value = True Then
          For Each Control3 In Frame3.Controls
            If Control3.Value = True Then
              Dim keyWord As String
              keyWord = Control1.Caption & "_" & Control2.Caption & "_" & Control3.Caption
              'Sh1のデータをオートフィルターする
              With Sh1.Range("A1")
                .AutoFilter field:=1, Criteria1:=keyWord
                '.CurrentRegion.Offset(1, 0).Columns("A:D").Copy
                .CurrentRegion.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 4).Copy
              End With
              'Sheet2の２列目の最後の行の下に貼付
              With Sh2.Range("A1").CurrentRegion
                .Cells(.Rows.Count + 1, 1).PasteSpecial
              End With
              Exit Sub
            End If
          Next
          MsgBox "産地未選択"
          Exit Sub
        End If
      Next
      MsgBox "サイズ_産地_未選択"
      Exit Sub
    End If
  Next
  MsgBox "商品_サイズ_産地_未選択"
End Sub

Comments

[koko2]

sinzou様、回答ありがとうございました。
コマンドボタン２は所望の通りできました。
コマンドボタン１は、 keyWord = Control1.Caption & "_" & Control2.Caption & "_" & Control3.Captionがデバックとなりました。ここを消してみましたら、データすべてがコピーされてシート２に貼り付けられました。
オートフィルター用の別のkeyWordが必要なのか、私の書き方に問題があるのかもしれません。

[sinzou]

追記しました。

[koko2]

無事に完成出来ました！ありがとうございます。
この追記ようなが必要なのですね。すごいです。
アドバイス本当にありがとうございました。

[koko2]

すみません、関連して教えてください。Sheet1にデータがあり、抽出したデータをSheet２に、ユーザーフォームのオプションボタンを表示するのをSheet3とすることはできますでしようか？どうしてもデータのあるシートでしかオプションボタンが反応しません。
新たにteratailのほうに質問を登録したほうがよろしいでしょうか？

[sinzou]

ユーザーフォーム表示した時の背景をSheet3にするには
Sheets("Sheet3").Activate　　で済みますが
シート上に並べるとなると一から組みなおすことになります。

追記
下記あれば、フォーム表示時にフイルターリセットされます。
Private Sub UserForm_Initialize()
Worksheets("Sheet1").Range("A1").AutoFilter
End Sub

[koko2]

回答ありがとうございました。
Sheet3にユーザー用にボタンを設置して、そのボタンでユーザーフォームを表示して、教えて頂いたオプションボタンで順番にkeywordを抽出しようとしていました CommandButton1_Click()にSheets("Sheet3").Activate　を追記してテストしてみました。この追記の場所がよくなかったのか、
結果としてやはりkeywordがあるシートとユーザーフォームが同じシートにないと、オプションボタンがうまく連動していかないのですね。ボタンの設置場所がよくなかったのかもしれません。
初めてユーザーフォームを作成したのでこのまま使用したかったのですが、うまくVBA ユーザーフォームでシート名を取得する方法があればよかったです。