E
Excel Help!
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
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