copying and pasting to a different xls file in a different directo

[dr chuck]

the following syntax.. creates the apporpriate file "s" and opens it and
gets me to the correct range. It however will not paste the information that
was selected and copied. It gives me an error every time on
ACTIVESHEET.PASTE. I get "run time error 1004".. paste method of worksheet
class failed.

Private Sub test2_Click()
Range("C40:E40").Select
Selection.Copy

Dim s As String, bk As Workbook

s = Range("othermanufact").Value & "-" & Range("lot").Value & "-" &
Format(Range("exp").Value, "mmddyyyy") & ".xls"
Set bk = Workbooks.Add()
bk.SaveAs "C:\Program Files\PanelSelect\panels\" & s


Workbooks.Open Filename:= _
"C:\Program Files\PanelSelect\panels\" & s

ActiveWindow.Visible = True


Windows(s).Activate

Sheets("Sheet1").Range("c2").Activate

ActiveSheet.paste <===error here every time

End Sub




help
dr chuck

 

[DS]

Hi dr chuck,

As far as I can tell, the Paste is failing because there's nothing on the
clipboard at that point - the Copy you performed earlier has been cleared by
the tasks you're performing between the two.

If you change round to:

Private Sub test2_Click()

Dim s As String, bk As Workbook

s = Range("othermanufact").Value & "-" & Range("lot").Value & "-" &
Format(Range("exp").Value, "mmddyyyy") & ".xls"
Set bk = Workbooks.Add()
bk.SaveAs "C:\Program Files\PanelSelect\panels\" & s


Workbooks.Open Filename:= _
"C:\Program Files\PanelSelect\panels\" & s

ActiveWindow.Visible = True

ThisWorkbook.Activate
Range("C40:E40").Copy

Windows(s).Activate

Sheets("Sheet1").Range("c2").Activate

ActiveSheet.paste

End Sub

Then this should work for you. It's a pretty crude method, but functions.
HTH
DS

 

[dr chuck]

Hey DS,
Thanks for your help i will try that. Sorry if my macro is crude. I am just
learning how to use visual basic with excel, so for me crude and functional
is a good thing.
Once again thanks for all of your help.

 

[dr chuck]

just tried your suggestion .. it worked for me.

Is it complicated to explain to me why...

"the Copy you performed earlier has been cleared by
the tasks you're performing between the two"

thanks

 

[DS]

Hi Dr Chuck,

apologies for the stupidly late reply, but hey, better late than never, and
hopefully you forgot to cancel the "notify of replies"!!

Certain functions in Excel clear the clipboard of any data held on it.
Examples include clearing a cell, protecting or unprotecting a worksheet etc.

If you do this manually, it works in the same way. Try it! Copy a cell, then
do some random stuff, and see if it's still on the clipboard. I don't have a
list of functions that clear the clipboard, but if it disappears between one
function and the next, then it's a safe bet!

re your earlier code, the action that cleared the clipboard was the "SaveAs"
- any save command will clear the clipboard, even if cancelled. Try copying a
cell, then save or open the save as dialog, and even if you cancel, it'll
still clear the clipboard, and you'll need to copy again before pasting.

HTH, and again, apologies for the 3 months delay in answering!! (*shame*)
DS