How to copy a formula down with a macro

Using VBA macros in Excel can make life easier. Here's another example of how. Let’s say I have formulas in row 2 of Sheet2 that I want to copy down a specified number of rows. The number of rows I want to fill the formula down is a value in A1 on Sheet1. Below is the code to show you how to do it along with my comments.

Sub Copy()

'Declare variables. R1 is the range to copy. R2 is the range where we want to put the formulas in the spreadsheet. M is the number of rows to fill to.

Dim r1 As Range, r2 As Range, m As Long

Dim ws1, ws2 As Worksheet

   

'get the row number to copy to from Define Variables sheet

Set ws1 = ThisWorkbook.Sheets("Sheet1")

The number of rows I want to fill the formula down is in A1, plus one for the header

m = ws1.Range("A1") + 1

       

Set ws2 = ThisWorkbook.Sheets("Sheet2")

Declare the range that you want to copy. In my case, the formulas are on Sheet2, in the second row, columns A to Z.

Set r1 = ws2.Range("A2:Z2")

Declare the range you want to fill in. In my example, I want to fill in from A2 through column Z, and down to the row number earlier defined as M.

Set r2 = ws2.Range("A2:Z" & m)

   

Finally, copied the first declared range into the fill declared range.

    r1.Copy r2

   

End Sub

Conversely, if you want to add a command button to your spreadsheet that will automatically clear a range in a sheet, you could use the following VBA code:

Sub Clear()

Dim r1 As Range

Dim ws1, ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Define Variables")

Set ws2 = ThisWorkbook.Sheets("AJ Generator")

Dim m As Integer

m = ws1.Range("L2") + 1

Set r1 = ws2.Range("A3:AJ" & m)

r1.Clear

End Sub