Paste Special

[Skin Paul]

Have 2.33 intell imac Running 10.4.8
What I would like help with is : I want to run a macro to paste special,
values, add. from my active cell (Monthly Profit/Loss cell F20) to three
other cells.
For instance monthly cell F20 = 2600.00. To cell G2(cash flow)paste special,
value,add INT(10% of active cell). To cell H2(reinvestment cell) paste
special,value,add INT(23% of active cell and balance paste special, value,
add of active cell to cell I2 for running totals in the three cells. Any
help welcome
Paul

 

[JE McGimpsey]

Have 2.33 intell imac Running 10.4.8
What I would like help with is : I want to run a macro to paste special,
values, add. from my active cell (Monthly Profit/Loss cell F20) to three
other cells.
For instance monthly cell F20 = 2600.00. To cell G2(cash flow)paste special,
value,add INT(10% of active cell). To cell H2(reinvestment cell) paste
special,value,add INT(23% of active cell and balance paste special, value,
add of active cell to cell I2 for running totals in the three cells.

One way:

Public Sub Distribute()
Const dCashFlowPercent As Double = 0.1
Const dReinvestmentPercent As Double = 0.23
Dim dAmount As Double
Dim dDistribute(1 To 3) As Double
Dim i As Long
dAmount = Range("F20").Value
dDistribute(1) = Int(dAmount * dCashFlowPercent)
dDistribute(2) = Int(dAmount * dReinvestmentPercent)
dDistribute(3) = dAmount - dDistribute(1) - dDistribute(2)
With Range("G2:I2")
For i = 1 To 3
.Cells(i).Value = .Cells(i).Value + dDistribute(i)
Next i
End With
End Sub

 

[Skin Paul]

Thanks J.E. Much appreciated works a treat. Had to change :-
dAmount = Range("F20").Value. As F20 is not always the active cell. I did
this,
Public Sub Distribute()
Const dCashFlowPercent As Double = 0.1
Const dReinvestmentPercent As Double = 0.23
Dim dAmount As Double
Dim dDistribute(1 To 3) As Double
Dim i As Long
Application.CutCopyMode = False
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
dAmount = Range("F1").Value ...etc
Great work J.E. Thanks, Paul

 

[JE McGimpsey]

Thanks J.E. Much appreciated works a treat. Had to change :-
dAmount = Range("F20").Value. As F20 is not always the active cell. I did
this,
Public Sub Distribute()
Const dCashFlowPercent As Double = 0.1
Const dReinvestmentPercent As Double = 0.23
Dim dAmount As Double
Dim dDistribute(1 To 3) As Double
Dim i As Long
Application.CutCopyMode = False
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
dAmount = Range("F1").Value ...etc

Would probably be somewhat easier to avoid copy and paste special
altogether by changing

dAmount = Range("F20").Value

to

dAmount = ActiveCell.Value