CODEWITHSUNDEEP
excelvba
Dingo
Dingo

Reputation: 123

Using Selection with Excel Objects

    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

Upvotes: 0

Views: 118

Answers (1)

Slai
Slai

Reputation: 22876

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

Upvotes: 2

Related Questions