Replace the last line

<<rngTemp.Copy wsDest.Range("A" & lngRow)

with

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Morning, below you will see the code for when a button is pressed it copies
the range of the cells into an external excel sheet, however the problem I
face now is column A and B need to be pasted as values and its not. Any help
on the revised code will be much appreciated.

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A12:Q75")

'Range("A12:Q75").Select

Selection.Copy
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1
rngTemp.Copy wsDest.Range("A" & lngRow)


wbBook.Close True