"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
culpeesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
You need to include the source range in the destination range.

Try this.
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")

    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" & lastform & ":$T" & lastrow                     'evaluates to "$J1763:$T2488"
        .Range(source).AutoFill Destination:=.Range(dest), Type:=xlFillDefault
    End With
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
culpeesAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.