Sabtu, 04 Februari 2012

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

 

I am surprised it did not choke on the select statement. Generally, if you
refer to sheets without qualifying them the macro will look for those sheets
in the workbook it is running in.

I would change each of the Sheets("Us") to either
ActiveWorkbook.Sheets("Us")
Or Workbooks("Us.csv"). Sheets("Us")

Actually you should not need the first statement as the only sheet will be
activated/selected automatically.

The following should work

ChDir FileLocation
Workbooks.Open Filename:="Us.CSV"
ActiveSheet.Move Before:=Workbooks("Find New Wild Shoes.xlsm").Sheets(2)

Not tested (on Animals or on my PC)

Best Regards
David Grugeon
Excel VBA Group Moderator

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of mickey11030
Sent: Sunday, 5 February 2012 6:15 AM
To: ExcelVBA@yahoogroups.com
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

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