Copy the entire visible worksheet and paste it as value and format it as text?

user_19531923 0
Viewed 41

Require some assistance from some professional here. I am trying to run a Marco which able to copy the entire worksheet and paste it as value and format is as text(copied from the web). Below is my code. Apparently, it is showing an error message 400. After troubleshooting , the error started from Cells.Select to Selection.NumberFormat = "@".

Please assists.

Dim xWs As Worksheet
Dim xWb As Workbook

For Each xWs In xWb.Worksheets
    If xWs.Visible = xlSheetVisible Then
    xWs.Select
    xWs.Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
    SkipBlanks _:=False,Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "@"
    End If

End Sub
vba excel
1 Answers

You can just use .Value = .Value to turn any formulas into values.

Dim xWb As Workbook
Set xWb = ThisWorkbook ' setting your desired workbook is needed!

Dim xWs As Worksheet
For Each xWs In xWb.Worksheets
    If xWs.Visible = xlSheetVisible Then
        xWs.UsedRange.Value = xWs.UsedRange.Value  ' turn formulas into values
        xWs.UsedRange.NumberFormat = "@"
    End If
Next xWs

End Sub
user_32196130
Related