mzsuga
02-23-2010, 09:20 AM
I have the following macro that searches for the word in a column then paste into a new sheet, but I want it to delete the original rows in the original sheet.
Dim wkss As Worksheet
Dim intcell As Range
Const Criteria As String = "*International*"
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
With Worksheets("domestic").UsedRange
.AutoFilter field:=2, Criteria1:=Criteria
On Error Resume Next
Set intcell = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not intcell Is Nothing Then
Set wkss = Worksheets.Add
wkss.Name = "International"
.Copy Destination:=wkss.Cells(1, 1)
Else
End If
.AutoFilter
End With
If i do .cutDestination:=wkss.Cells(1, 1) then it will cut the entire sheet as oppose to just ones with the word international
Dim wkss As Worksheet
Dim intcell As Range
Const Criteria As String = "*International*"
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
With Worksheets("domestic").UsedRange
.AutoFilter field:=2, Criteria1:=Criteria
On Error Resume Next
Set intcell = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not intcell Is Nothing Then
Set wkss = Worksheets.Add
wkss.Name = "International"
.Copy Destination:=wkss.Cells(1, 1)
Else
End If
.AutoFilter
End With
If i do .cutDestination:=wkss.Cells(1, 1) then it will cut the entire sheet as oppose to just ones with the word international