Type mismatch error in VBA

Question

1.Dim destbook As Workbook

2.Dim destsheet As Worksheet

3.Set destbook = Workbooks("Book1")

4.Set destsheet = destbook.Sheets(1)

5.Workbooks("Book1").Sheets("Sheet1").Range("C6").Select

6.ct = Range(Selection, Selection.End(xlDown)).count + 1

7.destbook.Activate

8.Workbooks(destbook).Sheets(destsheet).Range("A" + ct).Select

9.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Here, when i execute this code, it shows an error saying "type mismatch" on line 8.

Can u help??...

Main problem is you're using .Select..What are you trying to copy exactly? — Joe Laviano, Sep 19 '13 at 14:48
You might also want to see [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Sep 19 '13 at 16:48

score 2 · Answer 1 · answered Sep 19 '13 at 14:30

2

You should change + to &

Workbooks(destbook).Sheets(destsheet).Range("A" & ct).Select

answered Sep 19 '13 at 14:30

Sathish Kothandam

1,530
3
16
34

yea, this looks like the issue. +1 – Stepan1010 Sep 19 '13 at 14:32

score 1 · Accepted Answer · answered Sep 19 '13 at 13:47

1

You are using "destbook" and "destsheet" as the indexes for "Workbooks" and "Sheets", but they are actually themselves of type "Workbook" and "Worksheet" as you've defined them in lines 1 and 2. Change line 8 to: destsheet.Range("A" + ct).Select.

answered Sep 19 '13 at 13:47

rory.ap

34,009
10
83
174

You should also change line 5 to "destsheet.Range("C6").Select" – rory.ap Sep 19 '13 at 13:48
it again shows the same error.Do u think there is a problem with the range which i provided??... – srt Sep 19 '13 at 13:59
You should use "&" instead of "+" to concatenate strings. It may be thinking you're trying to add the numeric value ct to the string "A". Try "A" & ct instead. – rory.ap Sep 19 '13 at 14:29
Actually, what am I talking about, you can't reference a range like that. Try this: destsheet.Columns(, ct + 1).Select. 1 represents the first column, i.e. "A". – rory.ap Sep 19 '13 at 14:32

score 1 · Answer 3 · answered Sep 19 '13 at 20:06

As most folks have already pointed out, you need to change the way you are referencing the desired destination cell. Either you can switch over to an ampersand (&), or change to just a Cells(row,col) reference as you are only updating a single cell (see code below). You should also consider slimming down your code to make it a bit more efficient.

Dim destbook As Workbook
Dim destsheet As Worksheet

Set destbook = Workbooks("Book1")
Set destsheet = destbook.Sheets(1)

'See my note below
destbook.Activate
destsheet.Range("C6").Select
ct = Range(Selection, Selection.End(xlDown)).Count + 1
destsheet.Cells(ct, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Notes: - Line 5 should be changed to use your variables destbook and destsheet. Note that you'll need to move line 7 up to initially Activate your Workbook and then you can reference your Worksheet destsheet. - At the "see my note below", you should probably be copying some value from somewhere, otherwise you'll run into a new error upon your PasteSpecial command. - You should combine line 8 and line 9 together, unless you are planning on reusing the selection from line 8 in some other code (that you have not provided here).

Hope this helps.

Type mismatch error in VBA

3 Answers3

Linked

Related