teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

1

追記

2020/05/19 12:32

投稿

sinzou
sinzou

スコア392

answer CHANGED
@@ -27,4 +27,45 @@
27
27
  Worksheets("Sheet1").Range("A1").AutoFilter
28
28
  End Sub
29
29
  ```
30
- と必要か所に``` Worksheets("Sheet1").Range("A1").AutoFilter ```を
30
+ と必要か所に``` Worksheets("Sheet1").Range("A1").AutoFilter ```を
31
+
32
+ 追記
33
+ ```vb
34
+ Private Sub CommandButton1_Click()
35
+ Dim Control1 As Control, Control2 As Control, Control3 As Control
36
+ Dim Sh1 As Worksheet, Sh2 As Worksheet
37
+ Set Sh1 = Worksheets("Sheet1")
38
+ Set Sh2 = Worksheets("Sheet2")
39
+ For Each Control1 In Frame1.Controls
40
+ If Control1.Value = True Then
41
+ For Each Control2 In Frame2.Controls
42
+ If Control2.Value = True Then
43
+ For Each Control3 In Frame3.Controls
44
+ If Control3.Value = True Then
45
+ Dim keyWord As String
46
+ keyWord = Control1.Caption & "_" & Control2.Caption & "_" & Control3.Caption
47
+ 'Sh1のデータをオートフィルターする
48
+ With Sh1.Range("A1")
49
+ .AutoFilter field:=1, Criteria1:=keyWord
50
+ '.CurrentRegion.Offset(1, 0).Columns("A:D").Copy
51
+ .CurrentRegion.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 4).Copy
52
+ End With
53
+ 'Sheet2の2列目の最後の行の下に貼付
54
+ With Sh2.Range("A1").CurrentRegion
55
+ .Cells(.Rows.Count + 1, 1).PasteSpecial
56
+ End With
57
+ Exit Sub
58
+ End If
59
+ Next
60
+ MsgBox "産地未選択"
61
+ Exit Sub
62
+ End If
63
+ Next
64
+ MsgBox "サイズ_産地_未選択"
65
+ Exit Sub
66
+ End If
67
+ Next
68
+ MsgBox "商品_サイズ_産地_未選択"
69
+ End Sub
70
+
71
+ ```