Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Save current worksheet as values

    Hello to everyone.

    I am using a lot of excel and i need always to have a backup of figures. So all the time i am capying and pasting the data as values in order to have a frozen status for the figures.
    There is anyway to run a macro which will save the current sheet as a new one including all the format and data as values, and also the current name of the sheet by adding a suffix of _V?

    Thanks in advance for your valuable time.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Donk,

    This code works in my limited test.
    Code:
    Option Explicit
    
    Sub CreateBkupWithValues()
    
        Dim shtBase As Worksheet
        Dim shtNew  As Worksheet
        
        Application.ScreenUpdating = False 'Keep screen from blinking
        Set shtBase = ActiveSheet
        shtBase.Select
        shtBase.Copy After:=shtBase
        Set shtNew = ActiveSheet
        shtNew.Select
        shtNew.Name = shtBase.Name & "_V"  'Rename Sheet
        Cells.Select
        
        With Selection
            .Copy
            .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _
               SkipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
               SkipBlanks:=False, Transpose:=False
        End With        'Selection
        
        Application.CutCopyMode = False
        [a1].Select     'Clear Selection
        shtBase.Select  'Return to Original Worksheet
        
    End Sub    'CreateBkupWithValues()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Mar 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It works perfect.
    Thank you very much for your valuable time

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Another
    '=========
    Option Explicit
    Sub values()
    Dim asn As String
    Application.ScreenUpdating = 0
    asn = ActiveSheet.Name
    Worksheets.Add(After:=Sheets(asn)).Name = asn & "_V"
    ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). Value = _
    Sheets(asn).Cells.SpecialCells(xlCellTypeVisible). Value
    Application.ScreenUpdating = 1
    End Sub
    Don Guillett
    Excel Developer
    dguillett @gmail.com

Posting Permissions

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