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

 

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

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 5

Visit Your Group

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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___