Create New Sheet and Copy Formula to New Sheet

[mag]

Hi All
I need To Create New sheet Using Macro and Copy Formula New Sheet i Created Copy of May 17 i need to Create May 18 Sheet and Delete Sales Receives and Spoil all and Copy 17 Closing Balance to 18 Opening Balance By Using Macro is There Any way Please Help Me. I Need Keep all Formula only Want Delete No Formula Figures In May 18 Sheet
Please Find The attached File i Need to Change.

Regards
mag



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

[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