Input Message
Hi Excel Help.
Dim two new variables:
Dim Res1 As String
Dim Res2 As String
and try replacing:
rng.AutoFilter Field:=4, Criteria1:="=XXXXX", Operator:=xlOr,
Criteria2:="=XXXXXXXXX"
with:
Res1 = Application.InputBox( _
Prompt:="Enter first criterion")
Res2 = Application.InputBox( _
Prompt:="Enter second criterion")
If Not Res1 = vbNullString _
And Res2 = vbNullString Then
rng.AutoFilter Field:=4, _
Criteria1:=Res1, _
Operator:=xlOr, _
Criteria2:=Res2
End If
---
Regards.
Norman
"Excel Help!" wrote in message
...
I'd like to know how to write in an input-message box so that users can
input
the search requirement "Criteria" . The code below, I have to include the
Criteria into the code. However, I'd like for the user to input (prompt)
without accessing the code? Thanks for any help in advance.
Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set WS = Sheets("XX") '<<< Change
Set rng = WS.Range("A1:J" & Rows.Count)
WS.AutoFilterMode = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("XXXX").Delete
Application.DisplayAlerts = True
On Error GoTo 0
rng.AutoFilter Field:=4, Criteria1:="=XXXXX", Operator:=xlOr,
Criteria2:="=XXXXXXXXX"
Set WSNew = Worksheets.Add
WSNew.Name = "XX"
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
|