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@yahoo.com>
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
>
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