Results 1 to 2 of 2

Macro coming back with Run Time Error

Threaded View

  1. #1
    Registered User
    Join Date
    02-08-2012
    Location
    Stamford, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    9

    Macro coming back with Run Time Error

    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
    Last edited by amasulli; 05-14-2012 at 10:32 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1