VBA Runtime Error 1004 on Range.Clear

Question

There are a lot of threads about this error, but I can't get this to work no matter what I try. Most people say it occurs when you try to invoke a method on an inactive sheet, but you shouldn't have to do that. Error is on line 28. Thanks.

            Private Sub CommandButton1_Click()

            Dim x As Integer 
            Dim boisePaste As Integer
            Dim jrgPaste As Integer
            Dim master As Integer
            Dim lastRow As Integer
            Dim bookCount As Integer
                bookCount = Application.Workbooks.Count


            For x = 1 To bookCount
             If Left(Application.Workbooks(x).Name, 14) = "ITEM_INVENTORY" Then
                boisePaste = x
             ElseIf Left(Application.Workbooks(x).Name, 6) = "report" Then
                jrgPaste = x
             ElseIf Left(Application.Workbooks(x).Name, 8) = "Portland" Then
                master = x
             End If
            next x


            'Unhide sheets and delete Boise range'

            Application.ActiveWorkbook.Sheets("BoisePaste").Visible = True
            Sheets("JRGpaste").Visible = True
            lastRow = Sheets("BoisePaste").Cells(Rows.Count, "B").End(xlUp).Row
            Sheets("BoisePaste").Range(Cells(1,2), Cells(lastRow, 23)).Clear


            'Open Boise file and copy range, paste in master'

            Application.Workbooks(boisePaste).Activate
                With ActiveSheet
                    .Range(.Cells(1,1), .Cells((.Cells(Rows.Count, "A").End(xlUp).Row),22)).Copy
                End With

            Application.Workbooks(master).Sheets("BoisePaste").Range(B1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False


            'Open JRG report and copy range, paste in master'

            Application.Workbooks(jrgPaste).Activate
            ActiveSheet.Cells.Copy
            Application.Workbooks(master).Sheets("JRGpaste").Range(A1).Paste
            Application.CutCopyMode = False


            'Refresh pivot tables; hide sheets'
            Application.Workbooks(master).Activate

            With ActiveWorkbook
                .RefreshAll
                .RefreshAll
                .Sheets("BoisePaste").Visible = False
                .Sheets("BoisePaste").Visible = False
            End With

            End Sub

Answer 1

You need to explicitly state which sheet you want the Rows.Count and other such Range uses (Columns,Rows,Cells,etc.) will be on.

Try this:

Sheets("BoisePaste").Range(Sheets("BoisePaste").Cells(1,2), Sheets("BoisePaste").Cells(lastRow, 23)).Clear

So, go through your code and make sure you do this everywhere...i.e. in .Range(.Cells(1,1), .Cells((.Cells(Rows.Count, "A").End(xlUp).Row),22)).Copy, you didn't do it to Rows.Count, so add the sheet there too, to prevent any unexpected actions.

Think of it like this perhaps, with the line
myVariable = Sheets("mySheet").Range(Cells(1,1),Cells(1,2)).Value

VBA is reading that as

In mySheet, look for a range. What range? Hm, the user says Cells(1,1) and Cells(1,2), but what sheet does he want that? The current activesheet is called yourSheet...He specified where the Range should be (sheet called mySheet), but he didn't on Cells(), so I don't know what he wants! mySheet cells(1,1) or yourSheet cells(1,1) ??

(and yes, that's exactly how a computer thinks :P)

Edit: I went through and tried to help tighten up your code. But, as you can see perhaps, I'm not quite positive as to what you want to do, but this should give you some help/insight:

Private Sub CommandButton1_Click()

Dim x       As Integer
Dim boisePaste As Integer
Dim jrgPaste As Integer
Dim master  As Integer
Dim lastRow As Integer
Dim bookCount As Integer
bookCount = Application.Workbooks.Count

' Create variables to hold the workbook and sheet names.
Dim jrgWS As Worksheet, boiseWS As Worksheet
Dim masterWB As Workbook
Set masterWB = Workbooks(master)
Set jrgWS = Sheets("JRGPaste")
Set boiseWS = Sheets("BoisePaste")

For x = 1 To bookCount
    If Left(Application.Workbooks(x).Name, 14) = "ITEM_INVENTORY" Then
        boisePaste = x
    ElseIf Left(Application.Workbooks(x).Name, 6) = "report" Then
        jrgPaste = x
    ElseIf Left(Application.Workbooks(x).Name, 8) = "Portland" Then
        master = x
    End If
Next x


'Unhide sheets and delete Boise range'

Application.ActiveWorkbook.Sheets("BoisePaste").Visible = True
jrgWS.Visible = True
With boiseWS
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    .Range(.Cells(1, 2), .Cells(lastRow, 23)).Clear
End With

'Open Boise file and copy range, paste in master'
'' DONT USE ACTIVE SHEET! Use your variables instead
'Application.Workbooks(boisePaste).Activate
With boiseWS
    'Since you want values (xlPasteValues), just set the two ranges equal instead of copy/paste
    .Range("B1").Value = .Range(.Cells(1, 1), .Cells((.Cells(.Rows.Count, "A").End(xlUp).Row), 22)).Value
End With

'Open JRG report and copy range, paste in master'
' The below just pastes into the same sheet, no??
jrgWS.Cells.Copy
jrgWS.Range("A1").Paste
Application.CutCopyMode = False


'Refresh pivot tables; hide sheets'
Application.Workbooks(master).Activate

With ActiveWorkbook
    .RefreshAll
    .RefreshAll
    .Sheets("BoisePaste").Visible = False
End With

End Sub