Copying data between workbooks

[Karen53]

Hi,

I thought I'd seen posts on this while looking for other information but now
I need them they are not coming up on any of my searches.

Can I copy cells from a sheet in one workbook into a sheet in another
workbook, but not as a link, the actual value?

 

[Dave Peterson]

Try recording a macro when you do it manually and you'll have useable code.

Dim RngToCopy as range
dim DestCell as range

set rngtocopy = workbooks("book1.xls").worksheets("sheet999").range("x9:z15")
set destcell = workbooks("book2.xls").worksheets("sheet888").range("a1")

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

application.cutcopymode = false

======
or just assign the value:
with rngtocopy
destcell.resize(.rows.count,.columns.count).value = .value
end with

 

[Jim Thomlinson]

Yes you can copy cells between workbooks. It will be something like this
(untested but this should be close)...

dim rngCopyFrom as range
Dim wbkCopyTo as workbook
dim rngCopyTo as Range

on error resume next
set wbkCopyto = workbooks("MyFile.xls")
if wbkcopyto is nothing then
set wbkcopyto = workbooks.open("C:\MyFile.xls")
on error goto 0
if wbkcopyto is nothing then
msgbox "Sorry... Can't find the destination file."
else
set rngcopyfrom = thisworkbook.sheets("Sheet1").Range("A1")
set rngcopyto = wbkcopyto.Sheets("Sheet1").Range("A1")

rngcopyto.value = rngcopyfrom.value
wbkcopyto.close

 

[Karen53]

My thanks to you both.

I shall boldly(?) go where this person has never gone before.

 

[Dave Peterson]

Good luck!

Here's hoping you don't end up on Boston Legal with the mad cow!