Macro won't run when other workbook is active

Hello experts,
I have a delima.  The following code works fine as long as the worbook that it is in is active.  If I have 2 workbooks open, and the other one is active, it will not work. I get an "Application defined or Object defined error" (Runtime error 1004)
on this line:
Set CopyRange = SearchRange.Cells(x, 1).EntireRow.Range(Cells(1, 1), Cells(1, 11))
I want this macro to run even if a different workbook is active.  
I have tried explicitly naming and referring everything also, but I must be missing something.

Can anyone see what I am missing here?

I tried to rewrite this code so that if you want to duplicate this error, all you need to do is open a new workbook and in Sheet1 put 1's and 2's in column M3 starting in row 3 to whatever row you want.

Like I said it works fine if the workbook is active, but if a different one is active, it will not work.


Sub CopyRows()
Dim ThisWb As Workbook
Dim SearchRange As Range
Dim CopyRange As Range
Dim RowCount As Long
Dim x As Long


Set ThisWb = ThisWorkbook 'I tried changing this to the name of the workbook this code is in, but that didn't work either

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Set SearchRange = ThisWb.Sheets("Sheet1").Range("A1:M36").Columns(13)
    RowCount = SearchRange.Rows.Count
        For x = 3 To RowCount
            If SearchRange.Cells(x, 1).Value = "1" Then
                    If Not CopyRange Is Nothing Then
                        Set CopyRange = Union(CopyRange, SearchRange.Cells(x, 1).EntireRow.Range(Cells(1, 1), Cells(1, 11)))
                    Else
                        Set CopyRange = SearchRange.Cells(x, 1).EntireRow.Range(Cells(1, 1), Cells(1, 11))
                    End If
            End If
        Next x
       
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Any ideas?

:-)
ah
Start Free Trial