Link to home
Start Free TrialLog in
Avatar of culpees
culpeesFlag for United States of America

asked on

"Run-Timer Error 1004 : Autofill method of Range class failed"

Help!
Trying to copy down cells (formula and formatting) starting at an inconsistent source row ending with the last row of data.

columns J-T have last row of formulas/formats to be autofilled down to last row of data matched in column H


Sub Macro1()

    Dim sheet As Worksheet
    Dim lastform As Long   'finds last formula row
    Dim nextrow As Long    'finds first row in destination range
    Dim lastrow As Long     'finds last row of raw data (aka last row in destination range)
    Dim source As String
    Dim dest As String
   
    Set sheet = ActiveWorkbook.Sheets("Pressure Data")
    sheet.Select
    With sheet
        lastform = .Range("J" & .Rows.Count).End(xlUp).Row   'evaluates to 1762
        nextrow = lastform + 1                                                'evaluations to 1763
        lastrow = .Range("H" & .Rows.Count).End(xlUp).Row   ' evaluates to 2488
        source = "$J" & lastform & ":$T" & lastform               ' evaluates to "$J1762:$T1762"
        dest = "$J" & nextrow & ":$T" & lastrow                     'evaluates to "$J1763:$T2488"
        Range(source).Select 'selects last row of formulas
        Selection.End(xlDown).Select
        Selection.AutoFill destination:=.Range(dest), Type:=xlFillDefault              
   
    End With
   
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of culpees

ASKER

Okay. I changed my dest to include same row and it still didn't work...

BUT I then removed the redundant rows which you have done here and it fixed it.
I'm sooooooo happy!