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!

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" wrote in message
...
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!

On Apr 25, 8:09*am, "Bob Phillips" wrote:
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" wrote in message

...



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!- Hide quoted text -

- 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?

On Apr 25, 8:31*am, davegb wrote:
On Apr 25, 8:09*am, "Bob Phillips" wrote:





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" wrote in message

...

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!- Hide quoted text -

- 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?- 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

On Apr 25, 8:43*am, davegb wrote:
On Apr 25, 8:31*am, davegb wrote:





On Apr 25, 8:09*am, "Bob Phillips" wrote:

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" wrote in message

....

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!- Hide quoted text -

- 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?- 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- 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

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.)


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?

--

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

davegb wrote:

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!

--

Dave Peterson

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)

"davegb" wrote in message
...
On Apr 25, 8:31 am, davegb wrote:
On Apr 25, 8:09 am, "Bob Phillips" wrote:





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" wrote in message

...

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!- Hide quoted text -

- 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?- 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

On Apr 25, 8:58*am, Dave Peterson wrote:
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





davegb wrote:

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!

--

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?

On Apr 25, 9:45*am, "Bob Phillips" wrote:
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)

"davegb" wrote in message

...
On Apr 25, 8:31 am, davegb wrote:





On Apr 25, 8:09 am, "Bob Phillips" wrote:

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" wrote in message

....

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!- Hide quoted text -

- 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?- 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- Hide quoted text -

- Show quoted text -

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

On Apr 25, 9:46*am, davegb wrote:
On Apr 25, 8:58*am, Dave Peterson wrote:





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

davegb wrote:

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!

--

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?- 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!

I didn't notice that in your code.

(Eyeballs getting old!!!)

davegb wrote:

On Apr 25, 9:46 am, davegb wrote:
On Apr 25, 8:58 am, Dave Peterson wrote:





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

davegb wrote:

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!

--

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?- 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

On Apr 25, 12:31*pm, Dave Peterson wrote:
I didn't notice that in your code.

(Eyeballs getting old!!!)





davegb wrote:

On Apr 25, 9:46 am, davegb wrote:
On Apr 25, 8:58 am, Dave Peterson wrote:

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

davegb wrote:

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!

--

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?- 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- Hide quoted text -

- Show quoted text -

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

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.



davegb wrote:

<<snipped

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

--

Dave Peterson