Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Paste method fails in vba Results

Hi,

I am formatting an excel spreadsheet by opening the spreadsheet in Access (2010 versions of both(Access and excel)). I have the macro code that adds sub-totals to the spreadsheet and it works perferctly but only the first time.
The second time that I run the code, it fails with a 1004 error code.

Googling I found this microsoft solution:

http://support.microsoft.com/kb/178510

1. (pasted extract example from article)

Note The error message occurs because the code refers to the method of the cell without preceding the call with the xlSheet object variable.

2. Stop the project and change the following line of code:
3. Change the line of code to resemble the following line of code. 
4. Run the program again. Notice that you can run the code multiple times without receiving an error message.

Basically it article says that you must explicitly reference the obejects as in above example:

Below I will dispay 1. the orginal recorded vba and 2. my reworking of the code to satifiy microsoft's request for explicit references. Both sets of code work the first time and both fail the second time.

Can anyone see, in my explicit reference code, what is missing?

    'create Subtotals ORIGINAL
    
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-87
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8, 9, 10 _
        , 11, 12, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
 

     'code edited for explicit reference

    .Worksheets("qdfExportReport").Range("A3").Select
------>    .Worksheets("qdfExportReport").Range(Selection, Selection.End(xlToRight)).Select    <-------- On
the second run error results here:
    .Worksheets("qdfExportReport").Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-87
    .Worksheets("qdfExportReport").Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8, 9, 10 _
        , 11, 12, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
my Question is:
Can anyone spot what is missing from the second line of the "code edited for explicit reference"
to complete the explicit reference requirements from the Microsoft Documention.

PS I have fixed a couple of other difference macros with 1004 error but the reference requirements were more straightforward.

Many thanks.


No luck finding an answer? You could always try Google.