Copy and paste special from Active Cell to end

[Cluelessonmacros]

I am clueless so any help is very much appreciated
I want to copy and paste multiple formulas from the last active cell
Column A will always have data dropped in
I want to copy formulas from H2 to Y2 from the last active cell in Column H to Y (this is because we remove the formulas on a lot of the data to preserve amendments and the data needs to go to to the end of the data in column A

I was trying autofill but it overwrites all the data already in and we manually amend some of this data later

Found a few things but not sure how to quite make it work (I have basic record and some basic amend knowledge on macros only I am a newbie!)

Any help is appreciated. Thank you
This is what I was thinking not sure how on track I am

Dim LastRow As Long

Sheets("Sheet 1").Select

LastRow = Range("A" & Rows.Count).End(xlUp).Row

ActiveCell.AutoFill Destination:=Range("H2:Y2" & LastRow)

 

[Anthony47]

Would using Range("H2:Y2").AutoFill Destination:=Range("H2:Y" & LastRow), instead of ActiveCell.AutoFill Destination:=Range("H2:Y2" & LastRow) do the job?
If not:
-supposing that your column A extends from A2 to A100
-H2:Y2 are populated by formulas
-now do you want to copy H2:Y2 to H3:Y100?

If "No!" then what is the request?

 

[kvsrinivasamurthy]

Try

Sub Macro1()
Lr = Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Range("H2:Y2").Copy
    Range("H" & Lr & "Y" & Lr).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

 

[James006]

Hi,
A simple instruction:

Range("H2:Y2").Copy Range("H3:Y" & Range("A" & Rows.Count).End(xlUp).Row)

 

[Cluelessonmacros]

Hi . Thank you both for your advise and comments and I am sorry I didn't explain myself very well.

I want to maintain the previous data entered into columns H to Y, (formulas have all been removed)

I then want to add in the formulas only from H to Y on the next available blank row

for example data is written in and formulas overwritten in H2 to Y100

I then want to add in the formulas from H2 to Y2 and add them in from H101 up to where my data ends in column A

Sorry does this explain it better?

 

[James006]

May be

Sub CopyFormulas()
Dim rstart As Long
    rstart = Range("H" & Rows.Count).End(xlUp).Row + 1
    Range("H2:Y2").Copy Range("H" & rstsrt & ":Y" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

 

[James006]

Once you have tested the macro, feel free to share your comments

 

[Cluelessonmacros]

Once you have tested the macro, feel free to share your comments

Thank you ever so much James006, so when i try this code i do get an error. Run time error 1004 and method 'range of object _Global failed.
Sorry

 

[Cluelessonmacros]

Thank you ever so much James006, so when i try this code i do get an error. Run time error 1004 and method 'range of object _Global failed.
Sorry

Forgot to add the debug highlights this section Range("H2:Y2").Copy Range("H" & rstsrt & ":Y" & Range("A" & Rows.Count).End(xlUp).Row)

 

[James006]

OOPS ... very sorry rstsrt is the variable and should read rstart ...