Mismatch Error
 

I'm getting a type mismatch error on the following section of code - any ideas?

========
Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date,
"yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _
"PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _
"HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _
"CV (Chart)-NI-YTD", "CV (Chart)-NI-R12"))
========

Thanks in advance

Mismatch Error
 

I don't believe that you can set your wsDest variable (which I assume is
declared As Worksheet) to an array of multiple worksheets. You can assign
only a single worksheet object to a Worksheet typed object variable.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Sarah (OGI)" wrote in message
...
I'm getting a type mismatch error on the following section of code - any
ideas?

========
Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date,
"yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _
"PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _
"HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _
"CV (Chart)-NI-YTD", "CV (Chart)-NI-R12"))
========

Thanks in advance

Mismatch Error
 

Hi Sarah.

Perhaps you have dimmed the variable
wsDest as Worksheets? Note that a
Chart is included in the Sheets collection;
it is not a worksheet and, therefore is not
included in the Worksheets collection.

Try:

Dim wsDest as Sheets


Additionally, try changing:

Worksheets(Array....

to

Sheets(Array


---
Regards.
Norman


"Sarah (OGI)" wrote in message
...
I'm getting a type mismatch error on the following section of code - any
ideas?

========
Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date,
"yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _
"PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _
"HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _
"CV (Chart)-NI-YTD", "CV (Chart)-NI-R12"))
========

Thanks in advance

Mismatch Error
 

Hi Sarah,

Try:

Dim wsDest as Sheets

Or, if all the sheets of interest are charts:


Dim wsDest as Charts



---
Regards.
Norman

Mismatch Error
 

Dim wsDest as Sheets

I don't think that will solve anything.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Norman Jones" wrote in message
...
Hi Sarah.

Perhaps you have dimmed the variable
wsDest as Worksheets? Note that a
Chart is included in the Sheets collection;
it is not a worksheet and, therefore is not
included in the Worksheets collection.

Try:

Dim wsDest as Sheets


Additionally, try changing:

Worksheets(Array....

to

Sheets(Array


---
Regards.
Norman


"Sarah (OGI)" wrote in message
...
I'm getting a type mismatch error on the following section of code - any
ideas?

========
Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date,
"yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _
"PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _
"HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _
"CV (Chart)-NI-YTD", "CV (Chart)-NI-R12"))
========

Thanks in advance

Mismatch Error
 

Hi Chip,

============
Dim wsDest as Sheets

I don't think that will solve anything.
============

The following appeared to work for me:

'==========
Public Sub Tester()
Dim wsDest As Sheets
Dim i As Long
Dim sStr As String

sStr = "NI Insurer Market Share as at " _
& Format(Date, "yyyy-mmmm")

Set wsDest = Workbooks(sStr).Sheets(Array( _
"PC (Chart)-NI-MONTH", _
"PC (Chart)-NI-YTD", _
"PC (Chart)-NI-R12", _
"HH (Chart)-NI-MONTH", _
"HH (Chart)-NI-YTD", _
"HH (Chart)-NI-R12", _
"CV (Chart)-NI-MONTH", _
"CV (Chart)-NI-YTD", _
"CV (Chart)-NI-R12"))

For i = 1 To wsDest.Count
Debug.Print wsDest(i).Name & vbTab _
& TypeName(wsDest(i))
Next i
End Sub
'<<=========


---
Regards.
Norman

Mismatch Error
 

I tried the same thing and got a error. Go figure.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Norman Jones" wrote in message
...
Hi Chip,

============
Dim wsDest as Sheets

I don't think that will solve anything.
============

The following appeared to work for me:

'==========
Public Sub Tester()
Dim wsDest As Sheets
Dim i As Long
Dim sStr As String

sStr = "NI Insurer Market Share as at " _
& Format(Date, "yyyy-mmmm")

Set wsDest = Workbooks(sStr).Sheets(Array( _
"PC (Chart)-NI-MONTH", _
"PC (Chart)-NI-YTD", _
"PC (Chart)-NI-R12", _
"HH (Chart)-NI-MONTH", _
"HH (Chart)-NI-YTD", _
"HH (Chart)-NI-R12", _
"CV (Chart)-NI-MONTH", _
"CV (Chart)-NI-YTD", _
"CV (Chart)-NI-R12"))

For i = 1 To wsDest.Count
Debug.Print wsDest(i).Name & vbTab _
& TypeName(wsDest(i))
Next i
End Sub
'<<=========


---
Regards.
Norman