Patrick, that works thanks but i need it to keep adding information, not
overwrite the previous information.

Thanks alot.

"Patrick Molloy" wrote:

change
WsDest.Range("A1").Resize(.Rows.Count).Value = .Value
to
WsDest.Range("A1").Resize( .Rows.Count, .Columns.Count).Value = .Value

"Patrick Molloy" wrote:

you're pasteing to the same range that you're copying from and while you're
tried to use objects, you've a few errors.

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

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

Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
WITH rngTemp
WsDest.Range("A1").Resize(.Rows.Count).Value = .Value
END WITH
wbBook.Close True



"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