koi
Member
Hi All,
first of all i want to explain about below macro, that macro will look for status/word "Left" in sheets "apple", "banana" and "lemon" from column B and when its found "Left" then it will do autofilter on it then it will cut & paste to sheet "Left" starting on Column B as well.
it doesn't matter when you have only 1 line of Left or 10 line, it will cut & paste all of them.
but today I've found very strange things related to this macro, it says that command cannot be used on multiple selection, but i found out that it is only cannot work on 1 line.
this macro do the autofilter on sheet "apple", there it found 10 lines of "Left" but when i change 1 line to "right" then suddenly the macro is working, and when i change that 1 line back to "left" again... then it is still working.
i dont know what is happen, can somebody give me a clue here?
thanks guys
first of all i want to explain about below macro, that macro will look for status/word "Left" in sheets "apple", "banana" and "lemon" from column B and when its found "Left" then it will do autofilter on it then it will cut & paste to sheet "Left" starting on Column B as well.
it doesn't matter when you have only 1 line of Left or 10 line, it will cut & paste all of them.
but today I've found very strange things related to this macro, it says that command cannot be used on multiple selection, but i found out that it is only cannot work on 1 line.
this macro do the autofilter on sheet "apple", there it found 10 lines of "Left" but when i change 1 line to "right" then suddenly the macro is working, and when i change that 1 line back to "left" again... then it is still working.
i dont know what is happen, can somebody give me a clue here?
thanks guys
Code:
Sub left()
Dim ws As Worksheet
Dim copyRange As Range
Const myCrit As String = "Left"
Application.ScreenUpdating = False
'We'll use a loop so we don't have to repeat our code
For Each ws In Worksheets(Array("apple", "banana", "lemon"))
'Reset our variable
Set copyRange = Nothing
'Filter the range
ws.Range("A2:AQ65000").AutoFilter Field:=2, Criteria1:=myCrit
'In case no visible cells
On Error Resume Next
Set copyRange = ws.Range("B3:AQ65000").SpecialCells(xlCellTypeVisible)
Set copyRange = copyRange.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
'Check if there were any cells
If Not copyRange Is Nothing Then
copyRange.Copy
Worksheets("Left").Range("B65536").End(xlUp).Offset(1, 0). _
PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
copyRange.Delete Shift:=xlUp
End If
ws.AutoFilterMode = False
Next ws
Worksheets("Left").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: