Using macro to copy formulas to a variable range

[jmgoldjr]

I am attempting to use a macro that will identify the last row in my data source and then copy a range of formulas to the end of the data. The file is updated weekly and there will be close to 1000 new rows each week.

I am new to using macros and when I record one through Excel it saves the range as a static number and the next week the formulas are not copied to the new rows.

I have attached a sample that I am working with. Any help would be greatly appreciated.

ID	Formula	Formula	Formula	Formula	Formula	Formula	Formula	Formula
1	1	1	1	1	1	1	1	1
2	1	1	1	1	1	1	1	1
3	1	1	1	1	1	1	1	1
4	1	1	1	1	1	1	1	1
5	1	1	1	1	1	1	1	1
6	1	1	1	1	1	1	1	1
7	1	1	1	1	1	1	1	1
8	1	1	1	1	1	1	1	1
9	1	1	1	1	1	1	1	1
10	1	1	1	1	1	1	1	1
11
12
13
14
15
16
17
18
19
20

<colgroup><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>

 

[Redwolfx]

You just need something like

Dim LC as Long

LC = Sheets("Sheet1").Range("A1").End(xlDown).Row

Then something like

Range("A1:A" & LC).Filldown

 

[delos001]

Is this what you need?

Sub testa()


    Range("H3").Select
    Selection.Copy


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select


Dim LRa As Long
LRa = ActiveSheet.UsedRange.Rows.Count
Range("A" & LRa + 1).Select
ActiveSheet.Paste


End Sub