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
No comments:
Post a Comment
I'd love to hear from you!
-Nick