+ Reply to Thread
Results 1 to 6 of 6

Save Worksheet Without Formulas (i.e. Values Only)

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Save Worksheet Without Formulas (i.e. Values Only)

    Hello Again,

    I am trying to save a selected worksheet as individual workbook on my desktop. Till here the script is working fine but the problem here is is is saving with formulas although i need to save only values not formulas. i have highlighted the part below in my script.

    Can any one help me please.

    Private Sub SaveExcel_Click()
    n = LB1.ListCount
    Dim MyFullName, DTAddress As String
    Application.ScreenUpdating = False
    Select_Sheets
    
    DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
    DTAddress = DTAddress & Worksheets("ART NRP").Range("F2").Value & " " & Worksheets("ART NRP").Range("C2").Value
    On Error Resume Next
    MkDir DTAddress
    DTAddress = DTAddress & "\"
        For i = 1 To n
            If LB1.Selected(i - 1) Then
                With Sheets(LB1.List(i - 1))
                    If IsNull(.UsedRange.Text) Then
                    If ActiveSheet.Name = "Quotation (Arabic)" Then fname = Worksheets("ART NRP").Range("F2").Value & " " & "ARA Quot" & " " & Worksheets("ART NRP").Range("C2").Value & " " & ".xls"
                    If ActiveSheet.Name = "Template" Then fname = Worksheets("ART NRP").Range("F2").Value & " " & "Member Calculation" & " " & Worksheets("ART NRP").Range("C2").Value & " " & ".xls"
                    If ActiveSheet.Name = "ART NRP" Then fname = Worksheets("ART NRP").Range("F2").Value & " " & "NRP" & " " & Worksheets("ART NRP").Range("C2").Value & " " & ".xls"
                    If ActiveSheet.Name = "Calculation Result" Then fname = Worksheets("ART NRP").Range("F2").Value & " " & "Census With Premiums" & " " & Worksheets("ART NRP").Range("C2").Value & " " & ".xls"
                        .Copy
                        With ActiveWorkbook
                            Application.DisplayAlerts = 0
                            .Worksheets("ART NRP").Unprotect "******@aleem103"
                            .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            .SaveAs DTAddress & fname, 56
                            Application.DisplayAlerts = 1
                            .Close 0
                        End With
                        Response = MsgBox("The " & " " & ActiveSheet.Name & " " & " Successfully Save As EXL. Format" & vbCrLf + " " + vbCrLf + "Abdul Aleem - Lets Make Life Easier...", vbOKOnly + vbInformation, "Underwriting Department")
                    Else
                        Response = MsgBox("YOU CANNOT SAVE " & " " & ActiveSheet.Name & " " & "AS EXCEL BECAUSE" & " " & ActiveSheet.Name & " " & " " & "IS EMPTY OR RESTRICTED TO THE USER" & vbCrLf + " " + vbCrLf + "Abdul Aleem - Lets Make Life Easier...", vbOKOnly + vbInformation, "Underwriting Department")
                    End If
                End With
            End If
        Next i
        Application.ScreenUpdating = 1
    End Sub

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Save Worksheet Without Formulas (i.e. Values Only)

    Please check the MS documentation on the copy functionality, as it seems to me that you are copying a sheet but pasting like a range: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

    apart from that, the fastest was to convert a formula result into a value:
        With Range(Cells(1, 1), Cells(5, 5))
            .Value = .Value
        End With
    example for Activesheet Range A1:E5
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,794

    Re: Save Worksheet Without Formulas (i.e. Values Only)

    To "extend" the solution proposed by tehneXus a bit one can also use

    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    Alf

  4. #4
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Re: Save Worksheet Without Formulas (i.e. Values Only)

    Thanks ALF it works. Just one question the activesheet the sheet which i am copying the values or the sheet which i am pasting the values.

  5. #5
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Re: Save Worksheet Without Formulas (i.e. Values Only)

    Reputation added and topic marked as closed

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,794

    Re: Save Worksheet Without Formulas (i.e. Values Only)

    Thanks for feed back and rep.

    activesheet the sheet which i am copying the values or the sheet which i am pasting the values
    As you have marked the thread I assume you have solved the problem but just to be sure yes it's the same sheet.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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