|
Sub シート1を検索()
Dim KSh As Worksheet, a As String, aa As String
Dim Sh2EdR As Long, Sh2EdC As Long, CCR As Long
Sheets("検索結果").Cells.Clear
a = Application.InputBox("検索する文書のキーワードを入力(全角、半角、大文字、小文字全て入力可)", "検索")
If a = False Then
Sheets("検索結果").Select
Range("A1").Select
End
End If
aa = "*" & StrConv(a, vbWide) & "*"
For Each KSh In Worksheets
If KSh.Name <> "検索結果" Then
Sh2EdR = KSh.UsedRange.Cells(KSh.UsedRange.Count).Row
Sh2EdC = KSh.UsedRange.Cells(KSh.UsedRange.Count).Column
KSh.Range("E4:E" & Sh2EdR).AutoFilter Field:=1, Criteria1:=aa
KSh.Range("A5:A" & Sh2EdR).Resize(, Sh2EdC).SpecialCells(xlCellTypeVisible).Copy
CCR = Sheets("検索結果").UsedRange.Cells(Sheets("検索結果").UsedRange.Count).Row
Sheets("検索結果").Range("A" & CCR).Offset(1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
KSh.AutoFilterMode = False
End If
Next
End Sub
|
|