Results 1 to 2 of 2

Thread: Changing all remaining sheets to values, keeping the table format.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Changing all remaining sheets to values, keeping the table format.

    Hi, I have the following code, that should delete some sheets, change to values and protect the other ones, save and close. it works, except for one sheet(Units) that contains a table. In this one, the content gets twisted, showing formulas pasted as values and out of 500 lines, 25-30 are copied everywhere.
    I have no clue why. Can someone take a look on it?
    Code:
    Sub SaveReport()
        Dim xWs As Worksheet
        Dim path As String
        Dim name As String
        Dim pwd As String
    
    
        path = ""
        name = "Balance" & ThisWorkbook.Sheets("Admin").Range("F3").Value
        pwd = "password1"
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlManual
        For Each xWs In Application.ThisWorkbook.Worksheets
            If xWs.name <> "Units" And xWs.name <> "Overview" And xWs.name <> "OM" And xWs.name <> "OD" Then
                xWs.Delete
            End If
        Next
        For Each xWs In ThisWorkbook.Worksheets
            With xWs.UsedRange
                .Value = .Value
            End With
            xWs.Protect Password:=pwd, UserInterfaceOnly:=True
        Next xWs
        
    ThisWorkbook.SaveAs FileName:=path & name, FileFormat:=51
    ThisWorkbook.Close savechanges:=False
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
    End Sub

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing all remaining sheets to values, keeping the table format.

    Fixed by changing second For Each to:
    Code:
        For Each xWs In ThisWorkbook.Worksheets
             For Each rng In xWs.UsedRange
                If rng.HasFormula Then
                    rng.Formula = rng.Value
                End If
            Next rng
            xWs.Protect Password:=pwd, UserInterfaceOnly:=True
        Next xWs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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