Copy paste a range from one Workbook to another
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 |
Copy paste a range from one Workbook to another
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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Kc-Mass" wrote: 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 |
Copy paste a range from one Workbook to another
A thousand "Thank You's"
"Martin Fishlock" wrote in message ... 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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Kc-Mass" wrote: 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 |
All times are GMT +1. The time now is 03:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com