CODEWITHSUNDEEP
excelvba
srt
srt

Reputation: 541

Type mismatch error in VBA

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??...

Upvotes: 1

Views: 8173

Answers (3)

chewmewaba4
chewmewaba4

Reputation: 75

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.

Upvotes: 1

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

You should change + to &

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

Upvotes: 2

rory.ap
rory.ap

Reputation: 35260

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.

Upvotes: 1

Related Questions