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

 

With Regards to:
> My final code is
>
> Workbooks.Open Filename:=FileLocation & "\" & "Us.CSV"
> ActiveSheet.Move Before:=Workbooks("Find New Wild Shoes.xlsm").Sheets(1)

In your first statement you open a new workbook and load the .CSV file into a sheet in it.

This changes the activeworkbook to the new one and the activesheet to the newly created sheet.

In your second state, your goal seems to be to move this sheet into a different workbook.

The sheets.move documentation does not indicate that moving between workbooks is supported, and if not, I suspect the error indicates that it is not supported.

There are a couple ways around this. One way that does not involve a new workbook is to use the QueryTable (from Text) on the Data menu.

You can specify many parameters this way; but you end up with a "table"; which if you don't want, you can delete from the QueryTables collection.

DB

Sub Macro5()
'
' Macro5 Macro
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;G:\_MyProjects\CreditCards\2012_CreditCard_Amazon.CSV", Destination:= _
Range("$A$1"))
.Name = "2012_CreditCard_Amazon_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.MaintainConnection = False
.Refresh BackgroundQuery:=False
End With

'add following line to change from table to normal
ActiveSheet.QueryTables(ActiveSheet.QueryTables.Count).Delete
End Sub

__._,_.___

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

Messages in this topic (9)

Recent Activity:

• New Members 6

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

.

__,_._,___