Hi Guys,
I'm getting a run time error when I run the Macro below. Fortunately, the Macro still works but it comes back with the error. Do you guys see any reasons why this is happening? The error is the following:
"Run-time error '1004': The command could not be completed by using the range specified. Select a single cell within the range and try the command again."
It's definitely something to do with the auto filter but I don't understand what it is...
Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet, LR As Long
Application.ScreenUpdating = False
If MsgBox("Update this sheet?", vbYesNo) = vbNo Then Exit Sub
Range("A3:A" & Rows.Count).EntireRow.Clear
For Each ws In Worksheets
If ws.Name <> Me.Name Then
ws.Rows.AutoFilter Field:=1, Criteria1:=ActiveSheet.Range("B2").Value
LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
If LR > 2 Then
ws.Range("B3:K" & LR).Copy
Range("A" & Rows.Count).End(xlUp).Offset(3, 0).PasteSpecial xlPasteValuesAndNumberFormats
End If
ws.AutoFilterMode = False
End If
Next ws
Application.ScreenUpdating = True
Range("A3").Select
End Sub
Bookmarks