Macro Formula to Copy Specific Data Only In A Sheet, Paste the Copied Data Below Original Data with New Description And Multiply The Value Of A Cell

[Shaylena]

Hey All,

I am having major difficulties working out a macro formula for what I am trying to do, unfortunately, this seems way, way more advanced than my current skillset, and I can't seem to find any good video tutorials on what I am trying to do.

I am trying to create a macro with a button that calculates a 2% carbon tax as a separate line item with a unique description, below all my original data (Unfortunately, I cant just add a "Carbon" column in the worksheet, it needs to be formatted as its own separate line item as per our clients request)

This macro would hopefully copy the "Description" value if the Province Column is ON or SK (and a few others, just used two to simplify it for this example) and add on a " - Carbon Tax" at the end of it. The Value column would take the original value of the line and multiply it by .02 . The Type Column would autofill as "Carbon tax." I would only want this to happen on line items that had a positive value in the Value Column, not negative. See below for an example.

On my worksheet right now, the GST, PST, HST, and Total columns are all just formulas based on the Value and Province Columns, so I would love if the macro would just copy the entire line and just change the Description and Value columns so that the GST, PST, HST, and Total still calculate automatically.

Thanks in advance for all your help, or links to possible tutorials that might help!

Province	Site	Description	Type	Value	GST	PST	HST	Total
ON	1	1 - LollyPop	Sweet	$100.00	0	0	$13	$113.00
BC	2	2 - Doughnut	Sweet	$100.00	$5.00	$7.00	$0	$112.00
SK	3	3 - Popcorn	Savory	$100.00	$5.00	$6.00	$0	$111.00
ON	4	4 - Coffee	Savory	-$100.00	0	0	-$13	-$113.00
ON	1	1 - LollyPop - Carbon Tax	Carbon Tax	$2.00	$0.00	$0.00	$.26	$2.26
SK	3	3 - Popcorn - Carbon Tax	Carbon Tax	$2.00	$.10	$.12	$	$2.22

 

[Jamie McMillan]

Hello,

The following code will do 'pretty much' what you want. If a negative value it leaves a blank row. It copies the formulae and the formatting.

Sub ShayLena
    Dim i As Integer
    i = 5     ' i is the number of the first row
    For i = 5 To 10     '10  is the number of the last row
    If Cells(i, 6).Value < 0 Then GoTo Skipi
    Cells(i + 7, 2).Value = Cells(i, 2).Value & " - Carbon Tax"
    Range(Cells(i, 3), Cells(i, 10)).Copy
    Cells(i + 7, 3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells(i + 7, 6).Value = Cells(i, 6).Value * 0.02
Skipi:
    Next i
End Sub

Jamie