Hi Dave,

Thanks for your code. It works perfectly.

"Dave Peterson" wrote:

I left out the .close statement:

......same as before

'now do the copy & paste
rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

'now close
prcdwkbk.close savechanges:=false '<--added

Application.CutCopyMode = False

Dave Peterson wrote:

Sometimes, you can lose the clipboard when you open a workbook.

I'd keep that other workbook open. And I'd be more specific about what sheets
are being used--instead of relying on whatever the activesheet is at the time.

dim PRCDWkbk as workbook
dim OtherWkbk as workbook
dim RngToCopy as range
dim strName as string
dim DestCell as range

strName = "something here.xls"

set prcdwkbk = Workbooks.Open(filename:="H:\My WorkStation\PRCD\PRCDTEMP.XLS")
with prcdwkbk.worksheets(1) '.worksheets("sheet9999") 'if you know the name
set rngtocopy = .range("a2",.cells.specialcells(xlcelltypelastcell ))
end with

'don't copy and don't close yet.

'+ is used to add numbers, & is used to concatenate strings.
'VBA can be forgiving, but sometimes, you may be surprised!
set otherwkbk = Workbooks.Open(filename:="H:\My WorkStation\PRCD\" & strName)

with otherwkbk.Worksheets("Sheet1")
'more than 64k rows in xl2007
set destcell = .cells(.rows.count,"B").End(xlUp).Offset(1, 0)
end with

'now do the copy & paste
rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

Application.CutCopyMode = False

===========

Untested, uncompiled. Watch out for typos.

Junior728 wrote:

Hi, this is my code, but i got a runtime error of 1004 when i run below:
Can someone help pls?

Workbooks.Open ("H:\My WorkStation\PRCD\PRCDTEMP.XLS")
Windows("PRCDTEMP.XLS").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("PRCDTEMP.XLS").Close

Workbooks.Open ("H:\My WorkStation\PRCD\" + strName)
Windows(strName).Activate
With Worksheets("Sheet1").Range("B65536").End(xlUp).Off set(1, 0)
.PasteSpecial xlValues .... it stops here! cannot paste due to range class
failure...
End With
Application.CutCopyMode = False

--

Dave Peterson

--

Dave Peterson