copy,switch,paste,erase
 

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

copy,switch,paste,erase
 

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

copy,switch,paste,erase
 

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

copy,switch,paste,erase
 

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

copy,switch,paste,erase
 

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

copy,switch,paste,erase
 

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

copy,switch,paste,erase
 

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.

copy,switch,paste,erase
 

SORRY!!

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