CODEWITHSUNDEEP
vbaexcelerror-handling
Liz
Liz

Reputation: 117

Type Mismatch: Excel VBA

My program is erroring on this line with a 'Type Mismatch' error. I am trying to copy a range of cells onto a new sheet. I checked the dimensions of the old and new ranges and they are the same, so that is not the problem.

CSht.Range(CSht.Cells(2, 1), CSht.Cells(LstRow2, LstCol2)).Copy Destination:=Sheets(WS_New).Range(WS_New.Cells(1, 1), WS_New.Cells(LstRow2 - 1, LstCol2))

Upvotes: 1

Views: 219

Answers (3)

user5889203
user5889203

Reputation:

Try:

Destination:=Sheets(WS_New).Range(WS_New.Cells(1, 1).address, WS_New.Cells(LstRow2 - 1, LstCol2).address)

You are getting the "Type mismatch" because Excel uses the address for ranges, not the row/column format. So when you type in 

Destination:=Sheets(WS_New).Range(WS_New.Cells(1, 1), _ 
WS_New.Cells(LstRow2 - 1, LstCol2))

Excel is expecting to see:

Destination:=Range("A1:B1") format

By changing the notation from 

Destination:=Sheets(WS_new).range(Ws_new.Cells(1,1), Ws_new.cells. (lstRow2-1, lstCol2)

to 

Destination:=Sheets(WS_New).Range(WS_New.Cells(1, 1).Address, _ 
WS_New.Cells(LstRow2 - 1, LstCol2).address)

you will be sending Excel the format it is expecting. the .address grabs the ("A1:B1") format that excel is lookijg for.

Upvotes: 1

David G
David G

Reputation: 2355

To make a Range from 2 Ranges: How to create a range from 2 ranges in VBA

CSht.Range(CSht.Cells(2, 1).address & ":" & CSht.Cells(LstRow2, LstCol2).address).Copy Destination:=Sheets(WS_New).Range(WS_New.Cells(1, 1).address & ":" & WS_New.Cells(LstRow2 - 1, LstCol2).address)

Note that Sheets(WS_New) will only work if you have a string called WS_New. I think you want to use Sheets("WS_New") if WS_New is the name of the sheet.

When you use WS_New.Cells, this time you are using the codename of the sheet.

Acceptable examples for a sheet named WS_New codenamed wsNewSheet.

Sheets("WS_New").Range("A1")
Sheets("WS_New").Cells(1,1)
wsNewSheet.Range("A1")
wsNewSheet.Cells(1,1)

enter image description here

In the above example, "Shell" is the name of the worksheet and "f_Shell" is its codename.

Upvotes: 1

user4039065
user4039065

Reputation:

Try it as,

With csht
    .Range(.Cells(2, 1), .Cells(LstRow2, LstCol2)).Copy _
      Destination:=WS_New.Range(WS_New.Cells(1, 1), WS_New.Cells(LstRow2 - 1, LstCol2))
End With

In the destination, you had both Sheets(WS_New).Range and WS_New.Cells. It is one or the other; either WS_New is the name of the worksheet as a string or the worksheet as an object, not a mashup of methods.

BTW, you really only need the top left cell for a paste. That could have just as easily been,

With csht
    .Range(.Cells(2, 1), .Cells(LstRow2, LstCol2)).Copy _
      Destination:=WS_New.Cells(1, 1)
End With

Upvotes: 3

Related Questions