I looked at your original post and got kind of confused. You refer to the
activesheet. Is that one of the sheets that you refer to by name?

And sometimes, your code used
Sheets("Quote")...
and sometimes
Sheets("Quote Body")...

Is that a typo in your post or code or do you really have different worksheets.

Anyway, maybe you can do the work without selecting anything.

This compiled, but I didn't test it. It also expects that the autofilter is
already applied to your data (starting in column A).


Option Explicit
Sub Jenn_Qte2()

Dim ItemWks As Worksheet
Dim QuoteWks As Worksheet
Dim RngToCopy As Range
Dim DestCell As Range

Set ItemWks = Worksheets("Items, Cat")
Set QuoteWks = Worksheets("Quote")

Application.ScreenUpdating = False

With ItemWks
.Unprotect Password:="1234"
'show all the data
If .FilterMode Then
.ShowAllData
End If
'hide the blanks
.AutoFilter.Range.Columns(1).AutoFilter Field:=1, Criteria1:="<"
If .AutoFilter.Range.Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'only the headers are shown
'what should happen
Else
With .AutoFilter.Range
'header row included -- A:F (6 columns in the .resize portion)
Set RngToCopy = .Resize(.Rows.Count, 6) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

Set DestCell = QuoteWks.Range("A14")

QuoteWks.Unprotect Password:="1234"

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

QuoteWks.Protect Password:="1234"
End If

.Protect Password:="1234"

End With

Application.ScreenUpdating = True

End Sub

=================
And just a note about your problem. If your code is behind a worksheet, it
could fail. The unqualified range:

Range("B3").Select
will refer to the worksheet that owns the code--not the activesheet.

This is different than the behavior you see when your code is in a General
module.


Jennifer wrote:

<<snipped

Thank you all for your suggestions. I did change the statement to
Range("A1:F96"). Select but it didn't help. I'm still getting the
'Run-Time: Application-defined or object-defined error'. Still
stumped, I tried to accomplish the same task another way, so now my
code looks like this:

Sheets("Items, Cat").Select
ActiveSheet.Unprotect Password:="1234"
Selection.AutoFilter Field:=1, Criteria1:="<"

Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Now I get the 'Run-time: Selection method of Range class failed'
error. Can you help again, please?

Thanks in advance (again) -
Jenn

--

Dave Peterson