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

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
>
>
>

A thousand "Thank You's"


"Martin Fishlock" <(E-Mail Removed)> wrote in message
news:36EA93F2-BCDE-450F-AC4B-(E-Mail Removed)...
> 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
>>
>>
>>