How to create a values only copy of WB while keeping formatting and filters in tact?

[Bill_in_AZ]

Good Day All!

I am new to the forum on a recommend from a colleague. I have not found anything that fits my particular issue. I have used the code below to create the values only copy of the workbook. The issue is the copy does not keep the formatting nor the filters in tact. I'm not sure if this is possible but any suggestions would be appreciated.

Thanks for your time.


Sub CreateValuesOnly()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Output As Workbook, Source As Workbook
Dim sh As Worksheet
Dim FileName As String
FileName = "C:\Users\blittlej\Desktop\ValuesOnly_.xlsx"
Dim firstCell
Dim curdate As String
curdate = Format(Now(), "yyyy-MM-dd")
Set Source = ActiveWorkbook
Set Output = Workbooks.Add
Output.SaveAs FileName
Dim i As Integer
For Each sh In Source.Worksheets
Dim newSheet As Worksheet
' select all used cells in the source sheet:
sh.Activate
sh.UsedRange.Select
Application.CutCopyMode = False
Selection.Copy
' create new destination sheet:
Set newSheet = Output.Worksheets.Add(after:=Output.Worksheets(Output.Worksheets.Count))
newSheet.Name = sh.Name
' make sure the destination sheet is selected with the right cell:
newSheet.Activate
firstCell = sh.UsedRange.Cells(1, 1).Address
newSheet.Range(firstCell).Select
' paste the values:
Range(firstCell).PasteSpecial Paste:=xlPasteValues
Range(firstCell).PasteSpecial Paste:=xlPasteFormats
'Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths, _
'Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Next
' delete the sheets that were originally there
While Output.Sheets.Count > Source.Worksheets.Count
Output.Sheets(1).Delete
Wend
FileName = "C:\Users\blittlej\Desktop\ValuesOnly_" & curdate & ".xlsx"
Output.SaveAs FileName
Output.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

[Bill_in_AZ]

Well, I figured out that if I change
Range(firstCell).PasteSpecial Paste:=xlPasteFormats with
Range(firstCell).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone , SkipBlanks:=False, Transpose:=False I get the formats and filters. However, formulas copy over as well and I only want values. Any thoughts on this?

[snb]

All your code can be replaced by:


Sub M_snb()
Application.ScreenUpdating = False

ThisWorkbook.Sheets(1).Copy
With ActiveWorkbook
.Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.value
.SaveAs "C:\Users\blittlej\Desktop\ValuesOnly_.xlsx", 51
.Close 0
End With
End Sub

[Aussiebear]

@snb. What does this line represent ".Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.value"?

[snb]

It turns formulae into values.

[Aussiebear]

Doesn't the very next line do that when saving?

[snb]

No, it doesn't turn formulae into values, it only saves the file by a certain name and an indicated format (fileformat; .xlsx =51; .xlsb= 50; etc.)

[Aussiebear]

Isn't it saving as values at the same time

[snb]

Not in my Excel version (2010).
And I hope not in other versions too. How would you otherwise be able to store a file with formulae ?

[Aussiebear]

We are walking in circles here. I draw your attention back to my initial question

what does this line represent .Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.value How does this turn formula into values?

[snb]

Just run the code.

[Aussiebear]

Bullshit son.. tell me how it works.

[Paul_Hossler]

My understanding (and I learned it here) ....

When you set the .Value of a destination cell (even if it contains a formula), any formula or value in that cell is replaced with a computed value (not a formula)

So if

Range("A1").Formula = "=10*B1"
Range("B1").Value = 100

then

Range("A1").Value = Range("A1").Value

is sort of like a Copy/Paste Values from and onto itself in that the .Value is replaced by the results of the formula, giving

Range("A1").Value = 1000

Range("A1").Formula = 1000 also (no computation)

[Aussiebear]

Thank you Paul. We used to see a lot of the Range('A1").formula = Range("A1").value type constructions where it was understood that .formula was being copied and pasted as .value. Where it poses an issue is when we are taking a .value and copy/paste as a .value. If its already a value why convert it to a value?

[georgiboy]

Thank you Paul. We used to see a lot of the Range('A1").formula = Range("A1").value type constructions where it was understood that .formula was being copied and pasted as .value. Where it poses an issue is when we are taking a .value and copy/paste as a .value. If its already a value why convert it to a value?

The cell may or may not be a formula but either way it has a value, it is taking that value and overwriting the formula with said value. So taknig the value of a calculated formula and placing the value there instead.

Value of [A1] = the value/ result of [A1]

[Aussiebear]

The cell may or may not be a formula but either way it has a value.

In the formula bar Excel displays it as a formula rather than a value unless you specifically force Excel to show the value. To do this Excel must be giving a priority to the formula rather than a value but in VBA it seems therefore that it is the other way around.

[Paul_Hossler]

Thank you Paul. We used to see a lot of the Range('A1").formula = Range("A1").value type constructions where it was understood that .formula was being copied and pasted as .value. Where it poses an issue is when we are taking a .value and copy/paste as a .value. If its already a value why convert it to a value?

Not all .Value are created equal

.Formula cells also have a .Value = results of the .Formula

Entering a .Value replaces any existing .Value and .Formula. The new 'formula' is just the value


30075

[Aussiebear]

Now I'm wondering why snb couldn't simply explain this.

[snb]

VBA basics


Sub M_snb()
Cells(1, 1) = 100
Cells(2, 1) = 200
Cells(3, 1) = "=A1+A2"

Debug.Print 1, Cells(3, 1).Formula
Debug.Print 2, Cells(3, 1).Value
Debug.Print 3, Cells(3, 1).Text

Cells(3, 1) = Cells(3, 1).Value

Debug.Print 4, Cells(3, 1).Formula
Debug.Print 5, Cells(3, 1).Value
Debug.Print 6, Cells(3, 1).Text

Cells(3, 1).NumberFormat = "€ 0000"

Debug.Print 7, Cells(3, 1).Formula
Debug.Print 8, Cells(3, 1).Value
Debug.Print 9, Cells(3, 1).Text
End Sub

You could have seen the result when running the code.
No helper is obliged to do anything. Laziness shouldn't be rewarded.
Why can't you apologize for your rude behaviour ?
A moderator is obliged to respect and apply the courtesy rules in a forum.

[Paul_Hossler]

In the formula bar Excel displays it as a formula rather than a value unless you specifically force Excel to show the value. To do this Excel must be giving a priority to the formula rather than a value but in VBA it seems therefore that it is the other way around.


When you select a cell that contains a formula (.Formula = "=123+123"), then =123+123 is displayed in the Formula Bar

If you then click in the Formula Bar, and press F9, you see the results (aka .Value) for that cell, 246

So they're both really always there

[Aussiebear]

Thank you Paul and Georgiboy (yet again). You are most helpful. I'm guessing snb will respond at some stage.