Re: Code after Application.Run not working

[Don Guillett <donaldb@281.COM>]

I couldn't figure out how w/o opening, so this will open>copy>close

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
 Set Wb1 = ActiveWorkbook
 Set Wb2 = Workbooks.Open("F:\PERSONAL\111502.xls")
    Wb2.Sheets("Sheet5").Range("a1:E1").Copy _
    Wb1.Sheets("sheet8").Range("B3")
Wb2.Close False
Application.ScreenUpdating = True
End Sub

Don Guillett
SalesAid Software
Granite Shoals, TX
donaldb@281.com
----- Original Message -----
From: "Jim ." <Escojpoer@AOL.COM>
To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
Sent: Wednesday, July 30, 2003 1:15 PM
Subject: Re: Code after Application.Run not working


> Don-
>        I tried to modify, but got script out of range. I am trying to open a
> lotus file stored as ITE11.wk3. If I open it in excel it gives a sheet name of
> "A". I used your code modified (I thought ) to fit, but it does not. I would
> like to be able to copy from closed wbs as I do alot of that. Here is what I
> used::
>
> Range("B8") =
> Workbooks("C:\ftpfiles\ITE11.wk3").Sheets("A").Range("V8:AG152")
>
> Now I know that I will probably have a problem because I am trying to put
> V8:AG152 into B8, but how else would I arrange this so that the supposed
selected
> area(V8:AG152)can be pasted to B8?? And can I have the entire path inside
> Workbooks(blahblah)??
>   Jim
>
> In a message dated 7/30/2003 12:33:15 PM Eastern Daylight Time,
> donaldb@281.COM writes:
>
> > OOPs.
> > I didn't notice that you were copying from a DIFFERENT workbook.
> > No need to goto it at all. You also do NOT need to select
> >
> > try to modify this line to copy from closed workbooks.
> > range("a8") = Workbooks("021303.xls").Sheets("sheet8").Range("c1")
> > ====
> > ----- Original Message -----
> > From: "Don Guillett" <donaldb@281.COM>
> > To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
> > Sent: Wednesday, July 30, 2003 11:10 AM
> > Subject: Re: Code after Application.Run not working
> >
> >
> > >See if this is better
> > >Sub Macro4()
> > >  Workbooks.Open FileName:="C:\yourfolder\yourfile.xls"
> > >With ActiveWorkbook
> > > .Sheets("sheet2").Range("a1") = _
> > > .Sheets("sheet1").Range("b2")
> > > .Save
> > >End With
> > > ActiveWindow.Close
> > >End Sub
> > >
> > >Don Guillett
> > >SalesAid Software
> > >Granite Shoals, TX
> > >donaldb@281.com
> > >----- Original Message -----
> > >From: "Jim ." <Escojpoer@AOL.COM>
> > >To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
> > >Sent: Wednesday, July 30, 2003 10:33 AM
> > >Subject: Code after Application.Run not working
> > >
> > >
> > >>Hi-
> > >>    I have this piece of code below that runs, then opens another wb,
> > runs
> > >>the code in that wb(Transfer2), then closes and saves that wb, then(while
> > >>stepping through), the cursor goes to the line
> > >"Application.ScreenUpdating=True",
> > >>but the line is not highlighted in yellow (as all steps are in stepping
> > >>through). Then if I continue to hit F8, it goes back to the beginning of
> > the
> > >>Transfer mac and starts again. Why does it not continue through after it
> > >closes the
> > >>other wb?? It's like it steps out completely after closing the ITEHH.xls
> > wb.
> > >>  Jim
> > >>
> > >>Sub Transfer()
> > >>   Application.EnableEvents = False
> > >>   Application.DisplayAlerts = False
> > >>   Workbooks.Open Filename:="C:\ftpfiles\ITE11.WK3"
> > >>   Range("V8:AG152").Select
> > >>   Selection.Copy
> > >>   Windows("ITECAT3D.xls").Activate
> > >>   Range("B8").Select
> > >>   ActiveSheet.Paste
> > >>   Windows("ITE11.WK3").Activate
> > >>   ActiveWindow.Close
> > >>   Application.DisplayAlerts = True
> > >>   ChDir "C:\Documents and Settings\ITEREBATES"
> > >>   Workbooks.Open Filename:= _
> > >>     "C:\Documents and Settings\ITEREBATES\ITEHH.xls"
> > >>     Application.Run ("ITEHH.XLS!Transfer2")' Stops here, but goes back
> > to
> > >>beginning of this macro and runs again
> > >>     Application.ScreenUpdating = True
> > >>     Application.EnableEvents = True
> > >>     ActiveWorkbook.Save
> > >>     Application.Quit
> > >>End Sub
> > >>
> > >>****Below is code in ITEHH.xls workbook***************
> > >>
> > >>Sub Transfer2()
> > >>   Application.DisplayAlerts = False
> > >>   Workbooks.Open Filename:="C:\ftpfiles\ITEHH1.WK3"
> > >>   Range("V8").Select
> > >>   Selection.Copy
> > >>   Windows("ITEHH.xls").Activate
> > >>   Range("A8").Select
> > >>   ActiveSheet.Paste
> > >>   Windows("ITEHH1.WK3").Activate
> > >>   ActiveWindow.Close
> > >>   Application.DisplayAlerts = True
> > >>     ActiveWorkbook.Close savechanges:=True
> > >>End Sub
> >
>
>
> --------------------------------------------------------------------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G