CODEWITHSUNDEEP
excelvba
user3740620
user3740620

Reputation: 65

Autofill Runtime error 1004 - autofill dynamic range

I am trying to autofill (xlFillFormats) a dynamic range according to a user input.

The code is:

    If iInputRows > 1 Then
    Rows(14).Resize(iInputRows, Columns.Count).EntireRow.Insert _
      Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D13").Resize(iInputRows, 1).DataSeries Rowcol:=xlColumns, _
      Type:=xlLinear, Step:=1
    Range("X13:AR13").Resize(iInputRows, 21).FillDown
    Rows(13 + iInputRows).Delete
    Range("O13:U13").Resize(iInputRows, 7).AutoFill Destination:=Range("O13:U" & iInputRows), Type:=xlFillFormats

End If

Technically, all the rows that have been inserted should have the same formats as their corresponding sources cells. However, the particular range O:U (with number of rows decided by user input) is deciding to exclude the cell formatting.
I am having difficulty correcting for this in line 8 of the code above, to which I get a Runtime error.

Any suggestions?

Upvotes: 0

Views: 287

Answers (1)

Marek Stejskal
Marek Stejskal

Reputation: 2708

Your error lies here

Destination:=Range("O13:U" & iInputRows)

You forgot to offset the rows of the destination range, it should be like this:

Destination:=Range("O13:U" & 13 + iInputRows)

EDIT:

also, .Resize(iInputRows, 7) is unnecessary if you wish to copy the formatting of the first row only and should be removed.

Upvotes: 1

Related Questions