CODEWITHSUNDEEP
excelruntime-errorvba
David Van der Vieren
David Van der Vieren

Reputation: 275

Run Time Error 13: Type Mismatch

I am having a problem with the following code and am getting a type mismatch error on the bolded line of code:

 Private Sub CommandButton3_Click()
     Application.ScreenUpdating = False
     Dim p
     Dim ActivePrinter
     Dim Sheets

     p = Application.ActivePrinter
     ActivePrinter = ("Send to OneNote 2010")

     **Sheets(Array("R-Overview", "R-Savings", "R-Table")).PrintOut , , 1**

  End Sub

Upvotes: 1

Views: 437

Answers (2)

Santosh
Santosh

Reputation: 12353

As said here your code is working.

You get Type Mismatch error in the modified code because Sheets variable is declared as variant. Simply remove it and your code will work again.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

You cannot create/pass the array like this. Try this (TRIED AND TESTED)

Private Sub CommandButton3_Click()
    Application.ScreenUpdating = False

    Dim p
    Dim ActivePrinter
    Dim shtsArray(1 To 3) As String

    p = Application.ActivePrinter
    ActivePrinter = ("Send to OneNote 2010")

    shtsArray(1) = "R-Overview"
    shtsArray(2) = "R-Savings"
    shtsArray(3) = "R-Table"

    Sheets(shtsArray).PrintOut , , 1

    Application.ScreenUpdating = True
End Sub

ONE MORE WAY

Private Sub CommandButton3_Click()
    Application.ScreenUpdating = False

    Dim p
    Dim ActivePrinter
    Dim shtsArray
    Dim sheetNames As String

    p = Application.ActivePrinter
    ActivePrinter = ("Send to OneNote 2010")

    sheetNames = "R-Overview,R-Savings,R-Table"
    shtsArray = Split(sheetNames, ",")

    Sheets(shtsArray).PrintOut , , 1
End Sub

Upvotes: 2

Related Questions