Range("A1,F96").Select - What's wrong with that?

[Jennifer]

It's been years since I tried to do anything with excel that involved
any more than a simple macro, so this project is much harder that it
would've been back then.

I'm trying to copy the contents of a range of cells from one worksheet
to another. My code looks like this:

Private Sub Jenn_Qte()

Application.ScreenUpdating = False

ActiveSheet.Unprotect Password:="1234" 'unprotects the
destination worksheet

Sheets("Items, Cat").Select 'select source
worksheet
ActiveSheet.Unprotect Password:="1234" 'unprotect source
worksheet
Selection.AutoFilter Field:=1, Criteria1:="<>" 'filter data,
hide all blanks
Range("A1,F96").Select 'THIS IS WHERE
IT STOPS - supposed to select the filtered
data
Selection.Copy 'copy data

Sheets("Quote").Select 'select
destination worksheet
Range("A14").Select 'select cell
where data should be pasted
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Sheets("Items, Cat").Select 'back to the
source worksheet
Selection.AutoFilter Field:=1 'un-filter it
Range("C3").Select 'cursor in
this cell
ActiveSheet.Protect Password:="1234" 'protect the worksheet

Sheets("Quote Body").Select 'to the
destination sheet again
Range("F6").Select 'cursor in
F6 please
ActiveSheet.Protect Password:="1234" 'lock it back up

Application.ScreenUpdating = True

End Sub

Is there a glaring problem there that I just don't see? Does anyone
have any suggestions? Anything will be very appreciated.

Thanks in advance,
Jenn

 

[Lisab]

I may be wrong but shouldn't each value in the range be in quotes:

Range("A1", "F96")

 

[JLGWhiz]

If you want cells A1 and F96 then Range("A1", "F96")
If you want cells A1 thru F96 then Range("A1:F96")

 

[Dave Peterson]

I think both your examples will be A1:F96 (all 576 cells).

Range("a1,f96")
will be just two cells.

 

[JLGWhiz]

Yeah, sometimes I have these Senior moments.:-(

I think both your examples will be A1:F96 (all 576 cells).

Range("a1,f96")
will be just two cells.

 

[Jennifer]

Yeah, sometimes I have these Senior moments.:-(

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]

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]

Dave:

Wow! I deleted everything I had and pasted in your code and - ta da -
it works! Thank you so much!

I didn't know about the unqualified range - "Range("B3").Select will
refer to the worksheet that owns the
code--not the activesheet." That's important info that is now
permanently burned into my memory. Thanks!

I really appreciate your help!

Jennifer

 

[Dave Peterson]

Glad you got it working!

Dave:

Wow! I deleted everything I had and pasted in your code and - ta da -
it works! Thank you so much!

I didn't know about the unqualified range - "Range("B3").Select will
refer to the worksheet that owns the
code--not the activesheet." That's important info that is now
permanently burned into my memory. Thanks!

I really appreciate your help!

Jennifer