Results 1 to 4 of 4

Modify macro to remove formulas

Threaded View

  1. #1
    Registered User
    Join Date
    03-14-2010
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Modify macro to remove formulas

    Hello everyone,

    I need your help with modifying the below macro (I believe, I got this from excel forum only).

    This macro actually creates new sheet based on an existing sheet called as "Template" and it also renames the newly created sheet with the record name which are found in "Unique IP" list.
    For example, if Unique IP" sheet has 10 record names (a, b, & c), macro creates 10 new sheets identical to "Template" sheet and re-names those 10 sheets with respective record names (a, b, c etc ) found in Unique IP list.

    This code works perfectly, however, the macro is taking a very long time to run when there are 100 sheets in question. (P.S - "Temaplate" sheets has lot of formulas, and the same formulas will be copied to the new sheets when they are created for each record). This is making excel very slow

    What I want is, to have macro create sheets in interval
    - Should only create 10 records at a time and once formulas have populated results convert everything/formulas on these new sheets into values (copy paste special as values).
    - Once the above is done, proceed with the next 10 records.
    - The macro should stop once the sheets are created for all the records in "Unique IP" list


    Here is the macro that I'm using so far
    Sub CreateSheets()
    Dim EndRow As Integer
    Dim StartRow As Integer
    Dim xCount As Integer
    
    'What row do the list of names start on?
    StartRow = 2
    
    Application.ScreenUpdating = False
    With Worksheets("Unique IP")
        'Determines the last row in list
        EndRow = .Range("A100").End(xlUp).Row
    
        For Each c In .Range(.Cells(StartRow, "A"), .Cells(EndRow, "A"))
                'Place each new sheet at end of workbook
                xCount = ThisWorkbook.Worksheets.Count
                Sheets("TEMPLATE").Copy After:=Sheets(xCount)
                ActiveSheet.Name = c.Value
        Next c
    End With
    Application.ScreenUpdating = True
    End Sub
    Thanks in advance!
    Prithvi
    Last edited by JBeaucaire; 05-25-2013 at 09:02 AM. Reason: Added CODE tags and corrected title, as per Forum Rules. Take a moment to read the Forum Rules in the menu bar above. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1