Copy -> Paste Special -> Values (PROBLEM)

[davehunter]

Hi Guys.

I have a problem with a copy > paste special > values function I am using in a macro.

I have a button that opens a popup to allow a user to select an excel file.

The complete file is then copied using:

Range("A1:IV65536").Copy

It is then pasted into the other workbook using:

ThisWorkbook.Activate
Sheets("Sheet2").Activate
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False

The above code seems to be leaving formulas in my sheet, which then leads to a 'Type Mismatch' error.

Is their something else I should be doing, or is there a way I can guarentee that no formulas are pasted?

Thanks!
Dave

[royUK]

This should copy the cells containing formulas as values to the main workbook

 Dim curWb  As Workbook
    Dim tarWb  As Workbook
    'wb containing formulas, should be the active one
    Set curWb = ActiveWorkbook
    'wb to copy to, contains the code
    Set tarWb = ThisWorkbook
    curWb.ActiveSheet.Cells.Copy
    tarWb.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                      :=False, Transpose:=False