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))
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
Sheets() is a reference to a collection of all worksheets in ActiveWorkbook, so the number or name in the WS_New variable is supposed to select one of them. Use Option Explicit at the top of your code module to minimize this type of error.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)
In the above example, "Shell" is the name of the worksheet and "f_Shell" is its codename.
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.
