Error when trying to paste

[dgrimm]

I have the following code:

Sheets(wsRenew).Select
Range("A1:ca10000").Select
Selection.Copy
Workbooks.Add.Activate
ChDir "\\ovpfs1.swissre.com\legacy\corp\hea\apps\STOP LOSS\Renewal Strategy"
ActiveWorkbook.SaveAs Filename:="\\ovpfs1.swissre.com\legacy\corp\hea\apps\STOP LOSS\Renewal Strategy\" & Producer & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Sheets("Sheet1").Select
Range("a1").Select
Selection.Paste

and I have done variations on the paste part (the last line) but I am getting an error everytime. Object not supported or paste method error, etc....it does not like how I am trying to paste for some reason.

Any help would be greatly appreciated.

Thank you in advance

David

 

[VoG]

Try

ActiveSheet.Paste

 

[dgrimm]

I tried activesheet.paste and I get "paste method of worksheet class failed"

 

[VoG]

I think that opening a new workbook clears the contents of the clipboard.

Try like this

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="\\ovpfs1.swissre.com\legacy\corp\hea\apps\STOP LOSS\Renewal Strategy\" & Producer & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ThisWorkbook.Sheets(wsRenew).Range("A1:ca10000").Copy Destination:=ActiveWorkbook.Sheets("Sheet1").Range("a1")

 

[dgrimm]

VoG thanks for the help.....the last one errorred me also but I went off of what you said about the clipboard might get erased when opening a new workbook. So I came up with this code and it worked.

Workbooks.Add
ChDir "\\ovpfs1.swissre.com\legacy\corp\hea\apps\STOP LOSS\Renewal Strategy"
ActiveWorkbook.SaveAs Filename:="\\ovpfs1.swissre.com\legacy\corp\hea\apps\STOP LOSS\Renewal Strategy\" & Producer & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ThisWorkbook.Sheets(wsRenew).Activate
Range("a1:da100000").Select
Selection.Copy
Windows(Producer & ".xlsx").Activate
Sheets("sheet1").Select
Range("a1").Select
ActiveSheet.Paste

Thanks a bunch

Dave