Run-time error '13' Type mismatch

Question

We are trying to create a macro in Excel that will allow us to save an Excel Workbook, with multiple worksheets, without the formulas. When we try to run this macro, we get a Run-time error '13' Type mismatch message. The line that is in bold in the code below is where the debugger stops the run. Please help.

Sub NewWB()

Dim wbNew As Workbook

Dim wbThis As Workbook

     Set wbThis = ThisWorkbook

     Set wbNew = Workbooks.Add(xlWBATWorksheet)

     wbThis.Worksheets("Crittenden").Range("A1:H1").CurrentRegion.Copy

     With wbNew

          .Worksheets(1).Range("A1:H1").PasteSpecial xlPasteValues

          .Worksheets(1).Range("A1:H1").PasteSpecial xlPasteFormats

          .SaveAs "C:" & wbThis.Worksheets("Crittenden").Range("A1:H1")

          .Close True

     End With

     Set wbThis = ThisWorkbook

     Set wbNew = Workbooks.Add(xlWBATWorksheet)

     wbThis.Worksheets("Cross").Range("A1:H1").CurrentRegion.Copy

     With wbNew

          .Worksheets(1).Range("A1:H1").PasteSpecial xlPasteValues

          .Worksheets(1).Range("A1:H1").PasteSpecial xlPasteFormats

          .SaveAs "C:" & wbThis.Worksheets("Cross").Range("A1:H1")

          .Close True

     End With

End Sub

Answer 1

Pls share the sample to clear all your issues!!!

Answer 2

We've got the syntax on the line that saves the file correct, but now it just overrides the file after each "End With". I need "Crittenden" and "Cross" to be separate tabs (worksheets) in the workbook "District3". All I'm getting now is a workbook that says District3, but with one worksheet named "Sheet1". It reads "Crittenden", writes it, and then reads "Cross and overwrites "Crittenden". PLease help.

Sub District3()

    Application.Goto Reference:="District3"

 Dim wbNew As Workbook

 Dim wbThis As Workbook

     Set wbThis = ThisWorkbook

      Set wbNew = Workbooks.Add(xlWBATWorksheet)

     wbThis.Worksheets("Crittenden").Range("H2").CurrentRegion.Copy

     With wbNew

          .Worksheets(1).Range("A1").PasteSpecial xlPasteValues

           .Worksheets(1).Range("A1").PasteSpecial xlPasteFormats

           .SaveAs "C:" & ("District3.xlsx")

           .Close True

      End With

     Set wbThis = ThisWorkbook

      Set wbNew = Workbooks.Add(xlWBATWorksheet)

     wbThis.Worksheets("Cross").Range("H2").CurrentRegion.Copy

     With wbNew

          .Worksheets(1).Range("A1").PasteSpecial xlPasteValues

           .Worksheets(1).Range("A1").PasteSpecial xlPasteFormats

           .SaveAs "C:" & ("District3.xlsx")

           .Close True

      End With

Answer 3

I get a compiler error when I do the following:

.SaveAs wbThis.Path& "C:\District3.xlsx"

Wrong syntax?

Answer 4

Hi,

In addition to Jan Karel Pieterse reply if you wish to save new workbook in same directory then use this

.SaveAs wbThis.Path&"ABCD.xlsx"

or Explain more/share a sample.

Answer 5

Range("A1:H1") is a set of 8 cells so the macro tries to pass an array of 8 values to a string concatenation. This is not allowed. In other words:

You cannot use the value of more than one cell if you want to join together multiple strings. What is the cell containing the filename you need?

What is in A1:H1?