Create New Sheet and Copy Formula to New Sheet

[Admin]

Hi

Try this

Option Explicit
Sub kTest()
    
    Dim wksSource       As Worksheet
    Dim wksDest         As Worksheet
    Dim rngData         As Range
    Dim rngAreas        As Range
    Dim rngArea         As Range
    
    Application.ScreenUpdating = 0
    
    Set wksSource = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 'last worksheet
    wksSource.Copy , wksSource
    Set wksDest = ActiveSheet
    wksDest.Name = Format(DateValue(wksSource.Name) + 1, "dd mmm yyyy")
    Set rngData = wksDest.Range("b3:i" & wksDest.Range("a" & wksDest.Rows.Count).End(3).Row + 1)
    rngData.Columns(3) = rngData.Columns(8).Value
    
    rngData.Columns(4).Resize(, 4).ClearContents 'clears col e,f,g and h
    'rngData.Columns(5).Resize(, 3).ClearContents 'clears col f,g and h
    wksDest.Cells(1) = "Sales Analysis Report  " & wksDest.Name
    
    On Error Resume Next
    With rngData
        .AutoFilter 2, "="
        Set rngAreas = .Columns(3).SpecialCells(12)
        If Not rngAreas Is Nothing Then
            For Each rngArea In rngAreas.Areas
                If rngArea.Cells(1).Offset(, 5).HasFormula Then
                    rngArea.Cells(1).FormulaR1C1 = rngArea.Cells(1).Offset(, 5).FormulaR1C1
                End If
            Next
        End If
        .AutoFilter
    End With
        
    Application.ScreenUpdating = 1
    
End Sub

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA