Minggu, 05 Februari 2012

[ExcelVBA] Re: Copying a Sheet from One Workbook to Another

 



I'm sorry, I must have taken my idiot pills instead of my vitamins this morning. The problem with my program is I changed the name of the main file from Find New Wild Shoes to Find New Wild and Deleted Shoes. Not to make excuses for myself, but a little better error message, like Workbook Not Found, would have been helpful.

Thanks everyone for your invaluable help.
Michael Trombetta

--- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...> wrote:
>
> Would you please post your whole code (assuming it's not too big). We can't
> work from isolated statements.
>
> > ... but it still does not work.
>
> So what does it do instead?
>
> Please provide more information than we need, not less. :-(
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "mickey11030" <mickey11030@...>
> To: <ExcelVBA@yahoogroups.com>
> Sent: Monday, February 06, 2012 5:15 AM
> Subject: [ExcelVBA] Re: Copying a Sheet from One Workbook to Another
>
>
> > Thanks everyone for your suggestions, but none of them worked.
> > I had high hopes for David Smart's question whether the workbook contained
> > 2 sheets. My workbook, in fact, contains only 1 sheet. (Where the
> > Sheets(2) that the macro recorded came from I'll leave for another day.)
> > But the change to Sheets(1) had no effect.
> >
> > I also used David's suggestion to incorporate the FileLocation variable in
> > the Open statement. That too did no good.
> >
> > David Grugeon suggested I change the Move statement to
> > ActiveSheet.Move Before:=Workbooks("Find New Wild Shoes.xlsm").Sheets(2)
> > which I did (after changing Sheets(2) to Sheets(1). That too had no
> > effect.
> >
> > My final code is
> >
> > Workbooks.Open Filename:=FileLocation & "\" & "Us.CSV"
> > ActiveSheet.Move Before:=Workbooks("Find New Wild Shoes.xlsm").Sheets(1)
> >
> > but it still does not work. I should emphasize that putting a Macro1
> > statement before these statements, where Macro1 is my original code, works
> > fine.
> >
> > As I said earlier, what the %#*@ is going on.
> >
> > --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@>
> > wrote:
> >>
> >> As I see it (i.e. not tested at all), the big difference between your two
> >> bits of code is the ChDir statement.
> >>
> >> I wonder if this could be "pulling the rug out from under" your main
> >> workbook. It really is a major change to the code.
> >>
> >> Why not instead use your file location constant simply to qualify the
> >> open
> >> for the CSV file and leave your active directory sitting where it was?
> >>
> >> Something like
> >>
> >> > Workbooks.Open Filename:=FileLocation & "\" & "Us.CSV"
> >>
> >> (or without the "\" if the constant has a trailing backslash).
> >>
> >> Another candidate for the out of bounds would be the Sheets(2). Are you
> >> sure your main workbook contains 2 sheets at this stage?
> >>
> >> Regards, Dave S
> >>
> >> ----- Original Message -----
> >> From: "mickey11030" <mickey11030@>
> >> To: <ExcelVBA@yahoogroups.com>
> >> Sent: Sunday, February 05, 2012 7:14 AM
> >> Subject: [ExcelVBA] Copying a Sheet from One Workbook to Another
> >>
> >>
> >> > In a program I'm working on, I open a csv file and want to copy its
> >> > single
> >> > sheet to the workbook that contains the program.
> >> >
> >> > I recorded a macro and got this:
> >> >
> >> > Workbooks.Open Filename:= _
> >> > "C:\Users\Michael\Documents\Excel\SexyShoes\WildDNA\Us.CSV"
> >> > With ActiveWindow
> >> > .Width = 949.5
> >> > .Height = 476.25
> >> > End With
> >> > Sheets("Us").Select
> >> > Sheets("Us").Move Before:=Workbooks("Find New and Deleted Wild
> >> > Shoes.xlsm"). _
> >> > Sheets(2)
> >> > The Width and Height statements are just me making the tabs visible so
> >> > I
> >> > can drag the Us sheet to the Find New and Deleted Wild Shoes workbook.
> >> >
> >> > I copied these statements to the main program, dropping the Width and
> >> > Height statements and using the FileLocation constant to simplify the
> >> > Open
> >> >
> >> >
> >> > ChDir FileLocation
> >> > Workbooks.Open Filename:="Us.CSV"
> >> > Sheets("Us").Select
> >> > Sheets("Us").Move Before:=Workbooks("Find New Wild
> >> > Shoes.xlsm").Sheets(2)
> >> >
> >> > When this code is executed I get a Run-time error '9'/Subscript out of
> >> > range on the Move statement. Except for trivial differences, the code
> >> > is
> >> > identical, so why should it work in a macro but not inline?
> >> >
> >> > I have circumvented the problem by calling the macro from the main
> >> > program, but it would be nice to know what the #%@*& is going on!
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > ------------------------------------
> >> >
> >> > ----------------------------------
> >> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
> >> > Go
> >> > here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> >> > the ARCHIVES for EXCEL VBA.
> >> >
> >> > ----------------------------------
> >> > Visit our ExcelVBA group home page for more info and support files:
> >> > http://groups.yahoo.com/group/ExcelVBA
> >> >
> >> > ----------------------------------
> >> > More free tutorials and resources available at:
> >> > http://www.mousetrax.com
> >> >
> >> > ----------------------------------Yahoo! Groups Links
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > -----
> >> > No virus found in this message.
> >> > Checked by AVG - www.avg.com
> >> > Version: 10.0.1424 / Virus Database: 2112/4786 - Release Date: 02/03/12
> >> >
> >>
> >
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> > here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> > the ARCHIVES for EXCEL VBA.
> >
> > ----------------------------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.yahoo.com/group/ExcelVBA
> >
> > ----------------------------------
> > More free tutorials and resources available at:
> > http://www.mousetrax.com
> >
> > ----------------------------------Yahoo! Groups Links
> >
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 10.0.1424 / Virus Database: 2112/4789 - Release Date: 02/04/12
> >
>

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___

Tidak ada komentar:

Posting Komentar