No. When you use a With statement the "." before the Cells or Range links
them back to the element referred to in the With statement. An unqualified
range will refer to the Active Worksheet.

Regards

Trevor


"Neal Zimm" wrote in message
...
Hi Trevor -
You are right. Code below worked. I think the lesson learned is that
when a fully qualified range is like 'a1:z20' excel 'knows' the cells
are
in the qualifying wbk and ws.

When cells() substitute for 'a1', and the range is
xxxxx.range(cells(x,y), cells(xx,yy)) then the cells STILL have to be
qualified if not in the active sheet. Oh well, the proof is in the
testing.
Thanks again.

With FmWs.Range(FmWs.Cells(TmpltRngIdOrFmLOrow, FmLOcol), _
FmWs.Cells(FmHIrow, FmHIcol))

.Copy _
Destination:=Workbooks(ToWbkNa).Sheets(ToWsNa) _
.Cells(ToRow, ToCol)
End With


--
Neal Z


"Trevor Shuttleworth" wrote:

possibly this line should be:

(Cells(TmpltRngIdOrFmLOrow, FmLOcol), Cells(FmHIrow, FmHIcol))

(.Cells(TmpltRngIdOrFmLOrow, FmLOcol), .Cells(FmHIrow, FmHIcol))

Regards

Trevor


"Neal Zimm" wrote in message
...
HI - I'm still fuzzy on with stmts and ranges. This sub is used to
copy pieces of an addin ws elsewhere.

top half works fine. I got bottom half to work with the FmWs object,
but I don't understand why the commented out With stmt got the error.
I thought I followed the MSo help docum pretty closely. Guess not 'tho.
Thanks,
Neal Z.



Sub CopyAItmpltCstmRng(TmpltRngIdOrFmLOrow As Variant, FmLOcol As
Integer,
_
FmHIrow As Long, FmHIcol As Integer, _
ToRow As Long, ToCol As Integer, _
Optional ToWbkNa As String = "", _
Optional ToWsNa As String = "")

' Copies a range of cells from the AddIn TmpltCstm ws to another wbk
and
ws.
'Default To loca is the active sheet.
'NO error checking is done on these args;
' TmpltRngIdOrFmLOrow is a string of rng to be copied, e.g. "a1"
"f2:zz23"
' FmLOcol, FmHIrow, FmHIcol are ignored.
' TmpltRngIdOrFmLOrow is numeric, the lower left row of the copy.
' FmLOcol, FmHIrow, FmHIcol s/b valued to complete the range to
' be copied.

Dim FmWs As Worksheet


' value null optionals
If ToWbkNa = "" Then ToWbkNa = ActiveWorkbook.Name
If ToWsNa = "" Then ToWsNa = ActiveSheet.Name

' do the copy

If VarType(TmpltRngIdOrFmLOrow) = vbString Then

With
Workbooks(gAddInNa).Sheets("TmpltCstm").Range(Tmpl tRngIdOrFmLOrow)

.Copy Destination:=Workbooks(ToWbkNa).Sheets(ToWsNa) _
.Cells(ToRow, ToCol)

End With

Else

'' With stmt crapped out with a 1004 error ??
'' With Workbooks(gAddInNa).Sheets("TmpltCstm").Range _
'' (Cells(TmpltRngIdOrFmLOrow, FmLOcol), Cells(FmHIrow, FmHIcol))
''
'' .Copy Destination:=Workbooks(ToWbkNa).Sheets(ToWsNa) _
'' .Cells(ToRow, ToCol)
'' End With

' below works
Set FmWs = Workbooks(gAddInNa).Sheets("TmpltCstm")

FmWs.Range(FmWs.Cells(TmpltRngIdOrFmLOrow, FmLOcol), _
FmWs.Cells(FmHIrow, FmHIcol)).Copy _
Destination:=Workbooks(ToWbkNa).Sheets(ToWsNa) _
.Cells(ToRow, ToCol)

End If
End Sub

--
Neal Z