With stmt and Range

[Neal Zimm]

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(TmpltRngIdOrFmLOrow)

.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

 

[Joel]

I 'm guessing. Looking at the code the with statement that did not work hads
to CELLS objects where excel did not know which workbook or worksheet they
where located on. The 2nd method that worked you had .CELLS where excel did
know precisly which workbook and worksheet they came from.

 

[Trevor Shuttleworth]

possibly this line should be:

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

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

Regards

Trevor

 

[Neal Zimm]

Hi Joel -
Your guess is right on. This 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 the range is xxxxx.range(cells(x,y), cells(xx,yy)) then the cells
STILL have to be qualified as well. 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 Zimm]

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

 

[Trevor Shuttleworth]

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]

Hi Trevor,
Well your last 'No' puts me back to being 'fuzzy' so I'm going to test
what's below.
I hope my language was correct in the 'lesson' learned, I hope we're saying
the same thing.

Assume just working with the active Wbk.
Assume aaa is not the active sheet, I have NOT had a problem re:
set ws = sheets("aaa")
with ws.range("a1:b10") ' the hard coded range clearly 'belongs' to
ws.range
.copy destination:=Whatever
end with

As soon as I substitute cells(),cells() for "a1:b10" then the cells had
better be fully qualified or the data's coming from the active sheet, such as:

set ws = sheets("aaa") ' aaa still not the active sheet

with ws.range(ws.cells(RowVarA,ColVarA), ws.cells(RowVarB,ColVarB))

' it just seemed "odd" to me, that MSo requires the above syntax,
' 'cuz to my way of thinking, clearly wrong, the above "ws.range"
' ought to be enough, it is enough when "a1:b10" is used.

.copy destination:=Whatever
end with

Thanks again,