Copy down command button

[sowetoddid]

I have a command button placed at the end of the first 40 rows o
Sheet1. When pushed, the button inserts an equation into about
different cells on that particular row, copies the cells, and paste
the values only.

In the next row down, the same equations are pasted, copied and value
pasted.


Is it possible to copy-down the VB Code so that I do not have to kee
duplicating and changing it all the way down to row 500. The onl
updates in the macro are subtracting 1 from [R] in each of th
equations. Each row that I move down and put a command button,
subtract 1 from the [R] value.


Sub CalculateRow12()

Range("K12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[-5]C[-9]"
Range("N12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[15]C[-6]"
Range("O12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[3]C[-7]"
Range("P12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[5]C[-8]"
Range("Q12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[2]C[-9]"
Range("W12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[29]C[-15]"
Range("X12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[31]C[-16]"
Range("Z12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[30]C[-18]"
Range("AA12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[32]C[-19]"
Range("K12:R12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("V12:Z12").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("K12").Select
End Su

 

[Bernie Deitrick]

If the formulas are the same, simply incrementing the row by one, you could
use a sub like the one below, to do all 489 rows at once.
You can also make the row or column reference absolute, by changing from
R[?] to RX, where X is the row number, for example.

HTH,
Bernie
MS Excel MVP

Sub CalculateRow12To500()

Range("K12").Resize(489, 1).FormulaR1C1 = "=R[-5]C[-9]"
Range("N12").Resize(489, 1).FormulaR1C1 = "=R[15]C[-6]"
Range("O12").Resize(489, 1).FormulaR1C1 = "=R[3]C[-7]"
Range("P12").Resize(489, 1).FormulaR1C1 = "=R[5]C[-8]"
Range("Q12").Resize(489, 1).FormulaR1C1 = "=R[2]C[-9]"
Range("W12").Resize(489, 1).FormulaR1C1 = "=R[29]C[-15]"
Range("X12").Resize(489, 1).FormulaR1C1 = "=R[31]C[-16]"
Range("Z12").Resize(489, 1).FormulaR1C1 = "=R[30]C[-18]"
Range("AA12").Resize(489, 1).FormulaR1C1 = "=R[32]C[-19]"
Range("K12:R12").Resize(489, 8).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Range("V12:Z12").Resize(489, 5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("K12").Select
End Sub

I have a command button placed at the end of the first 40 rows of
Sheet1. When pushed, the button inserts an equation into about 8
different cells on that particular row, copies the cells, and pastes
the values only.

In the next row down, the same equations are pasted, copied and values
pasted.


Is it possible to copy-down the VB Code so that I do not have to keep
duplicating and changing it all the way down to row 500. The only
updates in the macro are subtracting 1 from [R] in each of the
equations. Each row that I move down and put a command button, I
subtract 1 from the [R] value.


Sub CalculateRow12()

Range("K12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[-5]C[-9]"
Range("N12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[15]C[-6]"
Range("O12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[3]C[-7]"
Range("P12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[5]C[-8]"
Range("Q12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[2]C[-9]"
Range("W12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[29]C[-15]"
Range("X12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[31]C[-16]"
Range("Z12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[30]C[-18]"
Range("AA12").Select
ActiveCell.FormulaR1C1 = "=PollutantSummaries!R[32]C[-19]"
Range("K12:R12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("V12:Z12").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("K12").Select
End Sub