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

__._,_.___

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

Messages in this topic (1)

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

.

__,_._,___