Create New Sheet and Copy Formula to New Sheet

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

Hi

Try this

Code:

---

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