Method range failed

[davegb]

Variable declared, set, so why is range failing?

Sub CopyCoVR2CtySht()
Dim wbSource As Workbook
Dim wsCoVR As Worksheet
Dim lMosColCount As Long
Dim lCtyRow As Long
Dim lColStart As Long
Dim wbCty As Workbook
Dim sCty As String

Set wbSource = ThisWorkbook
Set wsCoVR = wbSource.Sheets("CoVR_ModelImportDataBOS proj")
lColStart = 2
lCtyRow = 4
lMosColCount = 14

'start some kind of loop
sCty = wsCoVR.Range("A" & lCtyRow)
'MsgBox sCty

Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty

wsCoVR.Range(Cells(lCtyRow, lColStart), Cells(lCtyRow, lColStart +
lMosColCount)).Copy <---RANGE FAILED

Any suggestions? Thanks!

 

[Bob Phillips]

A thought

Sub CopyCoVR2CtySht()
Dim wbSource As Workbook
Dim wsCoVR As Worksheet
Dim lMosColCount As Long
Dim lCtyRow As Long
Dim lColStart As Long
Dim wbCty As Workbook
Dim sCty As String

Set wbSource = ThisWorkbook
Set wsCoVR = wbSource.Sheets("CoVR_ModelImportDataBOS proj")
lColStart = 2
lCtyRow = 4
lMosColCount = 14

'start some kind of loop
sCty = wsCoVR.Range("A" & lCtyRow)
'MsgBox sCty

Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty

With wsCoVR
.Range(.Cells(lCtyRow, lColStart), .Cells(lCtyRow, lColStart
+lMosColCount)).Copy
End With
--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[davegb]

A thought

Sub CopyCoVR2CtySht()
Dim wbSource As Workbook
Dim wsCoVR As Worksheet
Dim lMosColCount As Long
Dim lCtyRow As Long
Dim lColStart As Long
Dim wbCty As Workbook
Dim sCty As String

Set wbSource = ThisWorkbook
Set wsCoVR = wbSource.Sheets("CoVR_ModelImportDataBOS proj")
lColStart = 2
lCtyRow = 4
lMosColCount = 14

'start some kind of loop
sCty = wsCoVR.Range("A" & lCtyRow)
'MsgBox sCty

Workbooks.Add.Activate
    Set wbCty = ActiveWorkbook
    wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty

With wsCoVR
    .Range(.Cells(lCtyRow, lColStart), .Cells(lCtyRow, lColStart
+lMosColCount)).Copy
End With
--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)












- Show quoted text -

Thanks for the help, Bob. That works! But I'm still not clear as to
when I have to use the With/End with. Can anyone explain why it's
required here or what the rules are for when you have to use it?

 

[davegb]

Thanks for the help, Bob. That works! But I'm still not clear as to
when I have to use the With/End with. Can anyone explain why it's
required here or what the rules are for when you have to use it?- Hide quoted text -

- Show quoted text -

I've added a loop to the macro, but it won't run either. I've tried
various combinations of "With/end with" to get it to work, but no
sucess. There seems to be a problem with the with/end with and the
loop not cooperating. Can a "With/end with" start before the loop and
end before the loop ends? I'm hoping there's some logic to when, where
and how you use them, but right now it feels like when I get this kind
of message, just put in the With/end with and see if that helps. I
don't see any pattern here.
Thanks as always.
Dave

 

[davegb]

I've added a loop to the macro, but it won't run either. I've tried
various combinations of "With/end with" to get it to work, but no
sucess. There seems to be a problem with the with/end with and the
loop not cooperating. Can a "With/end with" start before the loop and
end before the loop ends? I'm hoping there's some logic to when, where
and how you use them, but right now it feels like when I get this kind
of message, just put in the With/end with and see if that helps. I
don't see any pattern here.
Thanks as always.
Dave- Hide quoted text -

- Show quoted text -

Sorry, forgot to post the latest iteration:

Sub CopyCoVR2CtySht()
Dim wbSource As Workbook
Dim wsCoVR As Worksheet
Dim lMosColCount As Long
Dim lCtyRow As Long
Dim lColStart As Long
Dim wbCty As Workbook
Dim sCty As String

Set wbSource = ThisWorkbook
Set wsCoVR = wbSource.Sheets("CoVR_ModelImportDataBOS proj")
lColStart = 2
lCtyRow = 4
lMosColCount = 14


With wsCoVR
Do Until .Range(Cells(lCtyRow, lColStart)) = ""
sCty = .Range("A" & lCtyRow)
End With
Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty
With wsCoVR
.Range(.Cells(lCtyRow, lColStart), .Cells(lCtyRow, lColStart +
lMosColCount)).Copy
End With

Loop

End Sub

 

[Dave Peterson]

When you have an unqualified range in a general module, it will refer to the
activesheet.

(When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code. I'm assuming that your code is in a general module.)

So your code:

wsCoVR.Range(Cells(lCtyRow, lColStart), _
Cells(lCtyRow, lColStart + lMosColCount)).Copy

is like writing
wsCoVR.Range(activesheet.Cells(lCtyRow, lColStart),
activesheet.Cells(lCtyRow, lColStart + lMosColCount)).Copy

Unless wsCoVR is the activesheet, then this will fail.

So Bob did this:

With wsCoVR
.Range(.Cells(lCtyRow, lColStart), _
.Cells(lCtyRow, lColStart +lMosColCount)).Copy
End With

The With/End with isn't ever _required_. It's just very useful and adds clarity
to the code. Bob could have typed:

wsCoVR.Range(wsCoVR.Cells(lCtyRow, lColStart), _
wsCoVR.Cells(lCtyRow, lColStart +lMosColCount)).Copy

But that's too much work (and too hard to read!) for most people.

(Any object/property/method that starts with a dot belongs to the object in the
previous With statement.)

 

[Dave Peterson]

Maybe...

Option Explicit
Sub CopyCoVR2CtySht()
Dim wbSource As Workbook
Dim wsCoVR As Worksheet
Dim lMosColCount As Long
Dim lCtyRow As Long
Dim lColStart As Long
Dim wbCty As Workbook
Dim sCty As String

Set wbSource = ThisWorkbook
Set wsCoVR = wbSource.Sheets("CoVR_ModelImportDataBOS proj")
lColStart = 2
lCtyRow = 4
lMosColCount = 14


With wsCoVR
'watch your dots here, too!!!
Do Until .Range(.Cells(lCtyRow, lColStart)) = ""
sCty = .Range("A" & lCtyRow)

set wbCty = Workbooks.Add

wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty

.Range(.Cells(lCtyRow, lColStart), _
.Cells(lCtyRow, lColStart + lMosColCount)).Copy

'where's the destination or paste line????

Loop
End With
End Sub

This is a guess!

With wsCoVR
'watch your dots here, too!!!
Do Until .Range(.Cells(lCtyRow, lColStart)) = ""
sCty = .Range("A" & lCtyRow)

set wbCty = Workbooks.Add

.Range(.Cells(lCtyRow, lColStart), _
.Cells(lCtyRow, lColStart + lMosColCount)).Copy _
destination:=wbCty.worksheets(1).range("A1")

wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty
wbCty.close
Loop
End With

 

[Bob Phillips]

It is not the With that does it, but qualifying Each of the Cells properties
with the sheet as well as the Range object.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks for the help, Bob. That works! But I'm still not clear as to
when I have to use the With/End with. Can anyone explain why it's
required here or what the rules are for when you have to use it?- Hide
quoted text -

- Show quoted text -

I've added a loop to the macro, but it won't run either. I've tried
various combinations of "With/end with" to get it to work, but no
sucess. There seems to be a problem with the with/end with and the
loop not cooperating. Can a "With/end with" start before the loop and
end before the loop ends? I'm hoping there's some logic to when, where
and how you use them, but right now it feels like when I get this kind
of message, just put in the With/end with and see if that helps. I
don't see any pattern here.
Thanks as always.
Dave

 

[davegb]

Maybe...

Option Explicit
Sub CopyCoVR2CtySht()
    Dim wbSource As Workbook
    Dim wsCoVR As Worksheet
    Dim lMosColCount As Long
    Dim lCtyRow As Long
    Dim lColStart As Long
    Dim wbCty As Workbook
    Dim sCty As String

    Set wbSource = ThisWorkbook
    Set wsCoVR = wbSource.Sheets("CoVR_ModelImportDataBOS proj")
    lColStart = 2
    lCtyRow = 4
    lMosColCount = 14

    With wsCoVR
        'watch your dots here, too!!!
        Do Until .Range(.Cells(lCtyRow, lColStart)) = ""
            sCty = .Range("A" & lCtyRow)

            set wbCty = Workbooks.Add

            wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" &sCty

            .Range(.Cells(lCtyRow, lColStart), _
                    .Cells(lCtyRow, lColStart + lMosColCount)).Copy

            'where's the destination or paste line????

        Loop
    End With
End Sub

This is a guess!

    With wsCoVR
        'watch your dots here, too!!!
        Do Until .Range(.Cells(lCtyRow, lColStart)) = ""
            sCty = .Range("A" & lCtyRow)

            set wbCty = Workbooks.Add

            .Range(.Cells(lCtyRow, lColStart), _
                    .Cells(lCtyRow, lColStart + lMosColCount)).Copy _
                destination:=wbCty.worksheets(1).range("A1")

            wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" &sCty
            wbCty.close
        Loop
    End With












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for your reply, Dave. I tried both of your suggested solutions,
but am still getting range method failure on the Do until line. Any
other ideas?

 

[davegb]

It is not the With that does it, but qualifying Each of the Cells properties
with the sheet as well as the Range object.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)






I've added a loop to the macro, but it won't run either. I've tried
various combinations of "With/end with" to get it to work, but no
sucess. There seems to be a problem with the with/end with and the
loop not cooperating. Can a "With/end with" start before the loop and
end before the loop ends? I'm hoping there's some logic to when, where
and how you use them, but right now it feels like when I get this kind
of message, just put in the With/end with and see if that helps. I
don't see any pattern here.
Thanks as always.
Dave- Hide quoted text -

- Show quoted text -

Thanks for the help, Bob. I took a guess based on your comment and now
it's running.

 

[davegb]

Thanks for your reply, Dave. I tried both of your suggested solutions,
but am still getting range method failure on the Do until line. Any
other ideas?- Hide quoted text -

- Show quoted text -

I got it! I changed the loop line to:
Do Until .Cells(lCtyRow, lColStart) = "" (no .range in there)
and it works.
Thanks for all your help!

 

[Dave Peterson]

I didn't notice that in your code.

(Eyeballs getting old!!!)

 

[davegb]

I didn't notice that in your code.

(Eyeballs getting old!!!)







--

Dave Peterson- Hide quoted text -

- Show quoted text -

It's good to know even you pros make mistakes.

 

[Dave Peterson]

Hey, making a mistake and not catching yours are two different things <hehehe>.

But it does happen all the time.

Someone posts a few lines of code and you see the "important" error and fix it.
But son of a gun, you miss the error right above it.

That's really one of the nicest things about the newsgroups. Lots of eyeballs.
Lots of chances for corrections.