Mismatch Error

  • Thread starter Thread starter Sarah (OGI)
  • Start date Start date
S

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top