Copying cells from one workbook to another
Ok, I have two files, one an excel spreadsheet, the other a text file. I open both in excel and want to copy cells from the text file to the spreadsheet using VBA. The code I have used is as follows:
VB Code:
Workbooks("conversion.xls").Sheets("Bintext3").Range("A1:DX64") = Workbooks("conversion.txt").Sheets(1).Range("A1:DX64")
But nothing happens! No error is returned but then again nothing is copied - I'm guessing it must be something to do with the Workbooks property???
Re: Copying cells from one workbook to another
You would need to do a copy and paste.
record the macro using Excel, and it will provide the code for you.
e.g.
open excel
start macro recording
open text file
copy text file
paste into excel
stop recording
Re: Copying cells from one workbook to another
Are you sure that's the only way? I'd prefer not to move anything onto the clipboard, unless there's a way of removing it afterwards? but thanks anyway :bigyello:
Re: Copying cells from one workbook to another
the other way would be to assign all of the items in the text file to variables, before writing to the excel sheet.
e.g. an array...
Re: Copying cells from one workbook to another
Hmm...I think I'll stick with the copy and paste method.
Thanks very much!
Re: Copying cells from one workbook to another
Incidentally, just in case anyone else reads this thread, I have since found out that the following code works:
VB Code:
ThisWorkbook.Sheets("Sheet1").Range("A1:D10").Value = Workbooks("New Text Document.txt").Sheets(1).Range("A1:D10").Value
The mistake I was making before was assuming that ".Value" was assumed. If ".Value" is omitted then it doesn't work! :bigyello: