Percent increase calculations...

Good morning to everyone...(or whatever time of day it may be)

I have been working on this code for a little while (with lots of help form NBVC and Ska67can)

I was wondering if anyone could help me with the small amount of code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.

Thanks a bunch to everyone in advance.

'Code created and edited by MBVC and Ska67Can

Sub CopyPaste()

Dim sht As Worksheet
Dim LastRow As Long, LastCol As Long, LastRow2 As Long, LastCol2 As Long

For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> "Instructions" And sht.Name <> "SATcosts" Then
       LastRow = sht.Cells(65536, 4).End(xlUp).Row
       LastCol = sht.Cells(5, 256).End(xlToLeft).Column
       ' Check if Year has changed.  If yes, proceed, else warning given.
       
       
           If sht.Cells(1, 2).Value <> Year(Now()) Then
           sht.Cells(4, LastCol + 1).Value = sht.Cells(1, 2).Value - 1
           Range(sht.Cells(5, 4), sht.Cells(LastRow, 5)).Copy
           sht.Cells(5, LastCol + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
           
           
           sht.Cells(5, LastCol + 1).Value = 0
    
           LastCol2 = sht.Cells(5, 256).End(xlToLeft).Column
           LastRow2 = sht.Cells(65536, LastCol2).End(xlUp).Row
           
    
           ' Add left/right borders
           With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(LastRow2, LastCol2)).Borders(xlEdgeLeft)
               .LineStyle = xlContinuous
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(LastRow2, LastCol2)).Borders(xlEdgeRight)
               .LineStyle = xlContinuous
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           Application.CutCopyMode = False ' Clear clipboard so big clipboard dialog will not appear when closing main workbook
        Else
            MsgBox "Year has not changed!"
            Exit For
        End If
    End If
Next sht

End Sub

Not exactly sure what which columns you mean by "current year" and "past year"

Try this code and see if it does what you want. You will see that after you hit the copy button, the 3rd column will be 0's, as you add figures in the Column E, the calculations will adjust.....is this right?

Option Explicit

'Code created and edited by NBVC and Ska67Can

Sub CopyPaste()

Dim sht As Worksheet
Dim LastRow As Long, LastCol As Long, LastRow2 As Long, LastCol2 As Long

For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> "Instructions" And sht.Name <> "SATcosts" Then
       LastRow = sht.Cells(65536, 4).End(xlUp).Row
       LastCol = sht.Cells(5, 256).End(xlToLeft).Column
       ' Check if Year has changed.  If yes, proceed, else warning given.
       
       
           If sht.Cells(1, 2).Value <> Year(Now()) Then
           sht.Cells(4, LastCol + 1).Value = sht.Cells(1, 2).Value - 1
           Range(sht.Cells(5, 4), sht.Cells(LastRow, 5)).Copy
           sht.Cells(5, LastCol + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
           
           
           'sht.Cells(5, LastCol + 1).Value = 0
    
           LastCol2 = sht.Cells(5, 256).End(xlToLeft).Column
           LastRow2 = sht.Cells(65536, LastCol2).End(xlUp).Row
           
           ' calculate percentage change
           With sht.Range(Cells(5, LastCol2 + 1).Address, Cells(LastRow2, LastCol2 + 1).Address)
                .Formula = "=(" & Range("E6").Address(rowabsolute:=False) & "-" & Range("P6").Address(rowabsolute:=False) & ")/" & Range("E6").Address(rowabsolute:=False)
           End With
    
           ' Add left/right borders
           With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(LastRow2, LastCol2)).Borders(xlEdgeLeft)
               .LineStyle = xlContinuous
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(LastRow2, LastCol2)).Borders(xlEdgeRight)
               .LineStyle = xlContinuous
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           Application.CutCopyMode = False ' Clear clipboard so big clipboard dialog will not appear when closing main workbook
        Else
            MsgBox "Year has not changed!"
            Exit For
        End If
    End If
Next sht

End Sub

BTW: My handle is NBVC.....not MBVC...

Try this code:

Option Explicit

'Code created and edited by NBVC and Ska67Can

Sub CopyPaste()

Dim sht As Worksheet
Dim LastRow As Long, LastCol As Long, LastRow2 As Long, LastCol2 As Long

For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> "Instructions" And sht.Name <> "SATcosts" Then
       LastRow = sht.Cells(65536, 4).End(xlUp).Row
       LastCol = sht.Cells(5, 256).End(xlToLeft).Column
       ' Check if Year has changed.  If yes, proceed, else warning given.
       
       
           If sht.Cells(1, 2).Value <> Year(Now()) Then
           sht.Cells(4, LastCol + 1).Value = sht.Cells(1, 2).Value - 1
           Range(sht.Cells(5, 4), sht.Cells(LastRow, 5)).Copy
           sht.Cells(5, LastCol + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
           
           
           'sht.Cells(5, LastCol + 1).Value = 0
    
           LastCol2 = sht.Cells(5, 256).End(xlToLeft).Column
           LastRow2 = sht.Cells(65536, LastCol2).End(xlUp).Row
           
           ' calculate percentage change
           With sht.Range(Cells(5, LastCol2 + 1).Address, Cells(LastRow2, LastCol2 + 1).Address)
                .Formula = "=(" & Cells(6, LastCol2).Address(rowabsolute:=False) & "-" & Cells(6, LastCol2 - 2).Address(rowabsolute:=False) & ")/" & Cells(6, LastCol2).Address(rowabsolute:=False)
           End With
           
           With Range(Cells(5, LastCol2 + 1), Cells(LastRow2, LastCol2 + 1))
                .Font.ColorIndex = 41
            End With
    
           ' Add left/right borders
           With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(LastRow2, LastCol2)).Borders(xlEdgeLeft)
               .LineStyle = xlContinuous
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           With Range(sht.Cells(4, LastCol2 + 1), sht.Cells(LastRow2, LastCol2)).Borders(xlEdgeRight)
               .LineStyle = xlContinuous
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(4, LastCol2 + 1)).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
           End With
            With Range(sht.Cells(4, LastCol2 - 1), sht.Cells(4, LastCol2 + 1)).Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
            End With
            Application.CutCopyMode = False ' Clear clipboard so big clipboard dialog will not appear when closing main workbook
          
        Else
            MsgBox "Year has not changed!"
            Exit For
        End If
    End If
Next sht

End Sub