Results 1 to 2 of 2

Thread: Create New Sheet and Copy Formula to New Sheet

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0

    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
    Attached Files Attached Files
    Last edited by DocAElstein; 06-12-2023 at 05:14 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Last edited by DocAElstein; 06-12-2023 at 05:11 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. How to create automated planning sheet.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 03-21-2013, 01:01 PM
  2. Dynamic Worksheet Generator Sheet Copy
    By mfaisalrazzak in forum Excel Help
    Replies: 2
    Last Post: 03-01-2013, 05:38 PM
  3. Replies: 1
    Last Post: 02-10-2013, 06:21 PM
  4. Replies: 2
    Last Post: 12-26-2012, 08:31 AM
  5. Copy selected data to other excel sheet
    By dhiraj.ch185 in forum Excel Help
    Replies: 2
    Last Post: 02-02-2012, 06:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •