Using macro to copy formulas to a variable range

jmgoldjr

New Member
Joined
Nov 4, 2014
Messages
5
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.

IDFormulaFormulaFormulaFormulaFormulaFormulaFormulaFormula
111111111
211111111
311111111
411111111
511111111
611111111
711111111
811111111
911111111
1011111111
11
12
13
14
15
16
17
18
19
20

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

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
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

New Member
Joined
Oct 7, 2014
Messages
25
Is this what you need?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,639
Messages
5,626,040
Members
416,155
Latest member
bokboxes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top