copy,switch,paste,erase

[Guest]

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.

 

[Guest]

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

 

[Gary Keramidas]

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

 

[Guest]

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

.Range("A1:C5").Copy

 

[Gary Keramidas]

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

 

[Guest]

Thank!

However the paste special copied the formulas rather than their values

 

[Guest]

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

 

[Guest]

SORRY!!

It does work. Thanks a Million.