Copy paste a range from one Workbook to another

[Kc-Mass]

Hi

I do most of my work in Access but am now working on a reporting system that
is about 2/3rds access and 1/3rd Excel. What I am trying to do is open two
Excel files in Excel 2003 and copy a range from workbook1, sheet 1 to
workbook2, sheet1.

I am getting a failure on the "Paste" operation in the second WorkBook.

Any and all ideas appreciated.

Here is my non working code so far.

Sub ExcelRangeChange()
'Set an instance of Excel and pointers for workbooks and sheets
Dim xlApp As Excel.Application
Dim xlBook1 As Excel.WorkBook
Dim xlBook2 As Excel.WorkBook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook1 = xlApp.Workbooks.Open("C:\Documents and Settings\Barbara\My
Documents\WkBk1.xls")
Set xlSheet1 = xlBook1.Worksheets("Sheet1")
Set xlBook2 = xlApp.Workbooks.Open("C:\Documents and Settings\Barbara\My
Documents\WkBk2.xls")
Set xlSheet2 = xlBook2.Worksheets("Sheet1")
xlApp.Visible = True

xlSheet2.Range("B1:B17").Select
xlSheet2.Range("b1:b17").Cut
xlSheet2.Range("E1").Select
xlSheet2.Paste
Workbooks("wkBk1.xls").Activate
Worksheets("Sheet1").Activate
xlSheet1.Range("B1:B17").Select
xlSheet1.Range("B1:B17").Cut
xlSheet1.Range("E1").Select
ActiveSheet.Paste

Workbooks("WkBk2.xls").Activate
Worksheets("Sheet1").Activate
xlSheet2.Range("b1").Select
ActiveSheet.Paste
xlSheet2.Range("e1:e17").Select
xlSheet2.Copy
Workbooks("WkBk1.xls").Activate
xlSheet1.Range("b1").Select
ActiveSheet.Paste

Kevin C

 

[Martin Fishlock]

Kevin:

Try this.

I've cut out the selects and activates that weren't needed.

Generally a cut only works once when you paste so use a copy and then cut if
needed later (here you don't as you paste over it).

Sub ExcelRangeChange()
'Set an instance of Excel and pointers for workbooks and sheets
Dim xlBook1 As Excel.Workbook
Dim xlBook2 As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet

Set xlBook1 = Workbooks.Open( _
"C:\Documents and Settings\Barbara\My Documents\WkBk1.xls")
Set xlSheet1 = xlBook1.Worksheets("Sheet1")
Set xlBook2 = Workbooks.Open( _
"C:\Documents and Settings\Barbara\My Documents\WkBk2.xls")
Set xlSheet2 = xlBook2.Worksheets("Sheet1")

' xlApp.Visible = True

xlSheet2.Range("B1:B17").Cut xlSheet2.Range("E1")
xlSheet1.Range("B1:B17").Copy ' use copy and then cut if needed
xlSheet1.Paste xlSheet1.Range("E1")
xlSheet2.Paste xlSheet2.Range("B1")
xlSheet2.Range("E1:E17").Copy xlSheet1.Range("B1")

Application.CutCopyMode = False
End Sub

 

[Kc-Mass]

A thousand "Thank You's"

Kevin:

Try this.

I've cut out the selects and activates that weren't needed.

Generally a cut only works once when you paste so use a copy and then cut
if
needed later (here you don't as you paste over it).

Sub ExcelRangeChange()
'Set an instance of Excel and pointers for workbooks and sheets
Dim xlBook1 As Excel.Workbook
Dim xlBook2 As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet

Set xlBook1 = Workbooks.Open( _
"C:\Documents and Settings\Barbara\My Documents\WkBk1.xls")
Set xlSheet1 = xlBook1.Worksheets("Sheet1")
Set xlBook2 = Workbooks.Open( _
"C:\Documents and Settings\Barbara\My Documents\WkBk2.xls")
Set xlSheet2 = xlBook2.Worksheets("Sheet1")

' xlApp.Visible = True

xlSheet2.Range("B1:B17").Cut xlSheet2.Range("E1")
xlSheet1.Range("B1:B17").Copy ' use copy and then cut if needed
xlSheet1.Paste xlSheet1.Range("E1")
xlSheet2.Paste xlSheet2.Range("B1")
xlSheet2.Range("E1:E17").Copy xlSheet1.Range("B1")

Application.CutCopyMode = False
End Sub