Mismatch Error

[Sarah (OGI)]

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

 

[Chip Pearson]

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)

 

[Norman Jones]

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

 

[Norman Jones]

Hi Sarah,

Try:

Dim wsDest as Sheets

Or, if all the sheets of interest are charts:


Dim wsDest as Charts

 

[Chip Pearson]

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]

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
'<<=========

 

[Chip Pearson]

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)