Hopefully this is simple and short.

Two workbooks are open in excel, "a" and "b" each with one sheet. While
looking a workbook "a" perform the following:

1. Switch to workbook "b"
2. Copy the range a1:c:5 to the clipboard
3. Switch to workbook "a"
4. Paste special values from the clipboard to range a1
5. Close workbook "b", leaving workbook "a" open.

KISS me.
--
l-hawk

Hi,

Paste this in as a module in Book a

Sub marine()
Workbooks("b.xls").Sheets("Sheet1").Range("a1:c5") .Copy
Worksheets("Sheet1").Select
Range("A1").Select
ActiveSheet.PasteSpecial
Workbooks("B").Close savechanges:=True
End Sub

Mike

"hawki" wrote:

Hopefully this is simple and short.

Two workbooks are open in excel, "a" and "b" each with one sheet. While
looking a workbook "a" perform the following:

1. Switch to workbook "b"
2. Copy the range a1:c:5 to the clipboard
3. Switch to workbook "a"
4. Paste special values from the clipboard to range a1
5. Close workbook "b", leaving workbook "a" open.

KISS me.
--
l-hawk

Thank!

However the paste special copied the formulas rather than their values
--
l-hawk


"Mike H" wrote:

Hi,

Paste this in as a module in Book a

Sub marine()
Workbooks("b.xls").Sheets("Sheet1").Range("a1:c5") .Copy
Worksheets("Sheet1").Select
Range("A1").Select
ActiveSheet.PasteSpecial
Workbooks("B").Close savechanges:=True
End Sub

Mike

"hawki" wrote:

Hopefully this is simple and short.

Two workbooks are open in excel, "a" and "b" each with one sheet. While
looking a workbook "a" perform the following:

1. Switch to workbook "b"
2. Copy the range a1:c:5 to the clipboard
3. Switch to workbook "a"
4. Paste special values from the clipboard to range a1
5. Close workbook "b", leaving workbook "a" open.

KISS me.
--
l-hawk

you can give this a try, just change the name of the workbooks and worksheets

Option Explicit
Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("bookA.xls")
Set wb2 = Workbooks("bookB.xls")

With wb2.Worksheets("Sheet1")
.Range("A1:C5").Copy
wb1.Worksheets("sheet1").Range("A1").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
wb2.Close savechanges:=False
End Sub

--


Gary


"hawki" wrote in message
...
Hopefully this is simple and short.

Two workbooks are open in excel, "a" and "b" each with one sheet. While
looking a workbook "a" perform the following:

1. Switch to workbook "b"
2. Copy the range a1:c:5 to the clipboard
3. Switch to workbook "a"
4. Paste special values from the clipboard to range a1
5. Close workbook "b", leaving workbook "a" open.

KISS me.
--
l-hawk

Thanks! However the macro stoped at the line below. There was a compile/
syntax error.

.Range("A1:C5").Copy
--
l-hawk


"Gary Keramidas" wrote:

you can give this a try, just change the name of the workbooks and worksheets

Option Explicit
Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("bookA.xls")
Set wb2 = Workbooks("bookB.xls")

With wb2.Worksheets("Sheet1")
.Range("A1:C5").Copy
wb1.Worksheets("sheet1").Range("A1").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
wb2.Close savechanges:=False
End Sub

--


Gary


"hawki" wrote in message
...
Hopefully this is simple and short.

Two workbooks are open in excel, "a" and "b" each with one sheet. While
looking a workbook "a" perform the following:

1. Switch to workbook "b"
2. Copy the range a1:c:5 to the clipboard
3. Switch to workbook "a"
4. Paste special values from the clipboard to range a1
5. Close workbook "b", leaving workbook "a" open.

KISS me.
--
l-hawk

compiles fine here, post the code with the changes you made.

--


Gary


"hawki" wrote in message
...
Thanks! However the macro stoped at the line below. There was a compile/
syntax error.

.Range("A1:C5").Copy
--
l-hawk


"Gary Keramidas" wrote:

you can give this a try, just change the name of the workbooks and worksheets

Option Explicit
Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("bookA.xls")
Set wb2 = Workbooks("bookB.xls")

With wb2.Worksheets("Sheet1")
.Range("A1:C5").Copy
wb1.Worksheets("sheet1").Range("A1").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
wb2.Close savechanges:=False
End Sub

--


Gary


"hawki" wrote in message
...
Hopefully this is simple and short.

Two workbooks are open in excel, "a" and "b" each with one sheet. While
looking a workbook "a" perform the following:

1. Switch to workbook "b"
2. Copy the range a1:c:5 to the clipboard
3. Switch to workbook "a"
4. Paste special values from the clipboard to range a1
5. Close workbook "b", leaving workbook "a" open.

KISS me.
--
l-hawk

Below is the macro. It now works fine until the following line,
"wb1.Worksheets("info").Range("A1").PasteSpeci al xlPasteValues".

Option Explicit
Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("A.xls")
Set wb2 = Workbooks("1.xls")

With wb2.Worksheets("info")
.Range("A1:A5").Copy
wb1.Worksheets("info").Range("A1").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
wb2.Close savechanges:=False
End Sub
--
l-hawk


"Gary Keramidas" wrote:

compiles fine here, post the code with the changes you made.

SORRY!!

It does work. Thanks a Million.
--
l-hawk