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" wrote:

>
> 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

[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


"DS" wrote:

> 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" wrote:
>
> >
> > 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

[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
--
dr chuck


"DS" wrote:

> 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" wrote:
>
> >
> > 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