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

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" <(E-Mail Removed)> wrote in message
news:1A975AAF-89E9-46A0-B9C0-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:1A975AAF-89E9-46A0-B9C0-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:C0795D19-4B39-429F-929A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:1A975AAF-89E9-46A0-B9C0-(E-Mail Removed)...
>> > 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

Below is the macro. It now works fine until the following line,
"wb1.Worksheets("info").Range("A1").PasteSpecial 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