paste special error

[Guest]

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).Offset(1, 0)
..PasteSpecial xlValues ....> it stops here! cannot paste due to range class
failure...
End With
Application.CutCopyMode = False

 

[Nelly]

Move Windows("PRCDTEMP.XLS").Close to the end and all should be ok
or just use paste. When you close original workbook you cannot use the paste
special command!

Rgds
Nelly

 

[Dave Peterson]

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.

 

[Dave Peterson]

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

 

[Guest]

Hi Dave,

Thanks for your code. It works perfectly.

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