Using Selection with Excel Objects

Question

    xlobj.Worksheets("Control").Range("A3:P3").Select
    Selection.AutoFill Destination:=Range("A3:P1500"), Type:=xlFillDefault

    xlobj.Worksheets("Control").Select
    Selection.ClearFormats
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Hi,

I have the above code but it errors out (Run time error 1004: Subscript out of range) at the 2nd line above. I see that it has a problem of identifying the 'selection' using the object.

How can I go around this? I want to pull down the formulas from range A3 to P3

Thanks for the help! (this would help me with other selection issues in the code as well)

Also, above this code somewhere, I have this (defined)

    Set xlobj = CreateObject("Excel.Application")
    xlobj.DisplayAlerts = 0
    xlobj.Workbooks.Open wbPathName & "\" & wbList(intListCounter), False, True
    DoEvents

You create a second instance of Excel (`xlobj`) and select a range there, but then you use `Selection` which belongs to your current instance of Excel that hosts the running code, not `xlobj`. Instead of trying to fix this situation, [remove it altogether](http://stackoverflow.com/q/10714251/11683). — GSerg, Oct 12 '16 at 19:37
`code`FolderPath = "\\edcfs2\share2\nathsh\XenApp\Desktop\Getit\" `code` FileName = Dir(FolderPath & "*-Report.xlsm") `code`Application.DisplayAlerts = False' `code` Do While FileName <> "" `code` Workbooks.Open (FolderPath & FileName), False, True `code` Application.ScreenUpdating = False `code` Workbooks(FileName).Activate `code`Sheets("Narrative Report").Select Now that I transformed my code, I am getting a subscript (runtime 9) error at the last line. Workbook indicated is open (and a different sheet is selected) — Dingo, Oct 12 '16 at 20:17
@Dingo you can update your question with the updated code, but note that any part of the code that relies on .Select, .Activate, .Copy, etc. can easily cause error by the user clicking or copying something while the macro runs. — Slai, Oct 12 '16 at 20:20

score 2 · Accepted Answer · answered Oct 12 '16 at 20:11

2

Here is one way to avoid the .Select and .Copy

With xlobj.Worksheets("Control").Cells
    .Range("A3:P1500").Formula = .Range("A3:P3").Formula 
    .ClearFormats
    .Value = .Value     ' converts the formulas to values
End With

answered Oct 12 '16 at 20:11

Slai

22,144
5
45
53

Thank you. Going forward I will use this (unfortunately since this answer came bit late, I've transformed my code to avoid using objects) – Dingo Oct 12 '16 at 20:16

Using Selection with Excel Objects

1 Answers1