How to Copy from One File to Another File

[GreenInIowa]

Hi,

I have some data & formulas in one Excel file and would like to COPY it to
another Excel file. I used the following command lines, but it is not
working. Could you please tell me why?

Workbooks("Book1.xls").Worksheets("Sheet1").Range("B:B") =
Workbooks("Book2.xls").Worksheets("Sheet1").Range("B:B")

Then, I tried this, but it is not working either!

Workbooks("Book2.xls").Worksheets("Sheet1").Range("B:B").Copy
Destination:=Workbooks("Book1.xls").Worksheets("Sheet1").Range("B:B")

Thanks.

 

[Tom Ogilvy]

there isn't anything wrong with the code per se.

The first code will reproduce the cell values as constants with no
formatting (formulas will be copied as the values they produce).

Merged cells could be problematic

What isn't working?

to be more explicit with the first

Workbooks("Book1.xls").Worksheets("Sheet1").Range("B:B").Value = _
Workbooks("Book2.xls").Worksheets("Sheet1").Range("B:B").Value


the above copies to book1 from book2

assuming the problem isn't just wrapped text:
Workbooks("Book2.xls").Worksheets("Sheet1").Range("B:B").Copy _
Destination:=Workbooks("Book1.xls").Worksheets("Sheet1").Range("B:B")

 

[GreenInIowa]

I am confused too! Here is the circumstances

I have a Excel file named "Book1" and have another Excel file named "Book2".
They are both open, but not SAVED.

If I were to type your code exactly you have below

Sub CopyingExactFormula_DifferentFiles()

Destination:=Workbooks("Book1.xls").Worksheets("Sheet1").Range("B:B")
Workbooks("Book1.xls").Worksheets("Sheet1").Range("B:B").Value = _
Workbooks("Book2.xls").Worksheets("Sheet1").Range("B:B").Value

End Sub

I gives me erro with yellow backgroud. However, if I want to copy some
formulas in the SAME file I have following code and it works

Sub Copy_SameFile()
Sheets("Inputs_Pomeroy").Range("a101:ad106").Formula =
Sheets("Analysis_Pomeroy (2)").Range("a101:ad106").Formula
End Sub

I am doing something stupid here?

Thanks!

 

[Tom Ogilvy]

If they haven't been save, then they don't have a .xls extension:

Sub CopyingExactFormula_DifferentFiles()
Workbooks("Book1").Worksheets("Sheet1").Range("B:B").Value = _
Workbooks("Book2").Worksheets("Sheet1").Range("B:B").Value
End Sub

or

Sub CopyingExactFormula_DifferentFiles()
Workbooks("Book2").Worksheets("Sheet1" _
).Range("B:B").Copy _
Destination:=Workbooks("Book1") _
.Worksheets("Sheet1").Range("B:B")
End Sub

 

[GreenInIowa]

Wow, this is great and it works! I did not realized that I needed to save
first.

Thanks, Tom!

GreenInIowa