Minggu, 05 Februari 2012

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

__._,_.___
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