Convert Contents to Values VBA

[dannyok90]

Hi all,

Im looking to convert the formulas in some cells to values and then revert back to formulas?


Basically i have a save button that saves the file but i want the data to become static in the saved file some cells do have formulas that need to stay because they are looking at the now static data.


I have got this far which selects the cells:

Union(Cells(1, "A"), Cells(4, "A"), Cells(7, "A"), Cells(5, "E"), Cells(7, "E"), Cells(5, "K"), Cells(7, "K"), Cells(11, "Q"), Cells(13, "Q"), Cells(15, "Q"), Cells(19, "Q")).Select

Heres the full code for context:

Option Explicit
Sub SaveAsExample()
 
    Dim FName           As String
    Dim FPath           As String
    
    FPath = "T:\V-Task\V0000+\200+\V0297\Shared\Project Status Report (PSR)\PSRs"
    FName = Sheets("Sheet1").Range("A9") & ".xlsm"

With Union(Cells(1, "A"), Cells(4, "A"), Cells(7, "A"), Cells(5, "E"), Cells(7, "E"), Cells(5, "K"), Cells(7, "K"), Cells(11, "Q"), Cells(13, "Q"), Cells(15, "Q"), Cells(19, "Q")).Select
     
End With
 With ActiveWorkbook
 
     Columns("U:W").EntireColumn.Hidden = True
     ActiveSheet.Range("C10").Select
   .SaveCopyAs Filename:=FPath & "\" & FName
    Columns("U:W").EntireColumn.Hidden = False
End With
     
End Sub

Thanks in advance :D
Dan

 

[Fluff]

How about

For Each Cl In Union(Cells(1, "A"), Cells(4, "A"), Cells(7, "A"), Cells(5, "E"), Cells(7, "E"), Cells(5, "K"), Cells(7, "K"), Cells(11, "Q"), Cells(13, "Q"), Cells(15, "Q"), Cells(19, "Q"))
   Cl.Value = Cl.Value
Next Cl

 

[dannyok90]

How about

For Each Cl In Union(Cells(1, "A"), Cells(4, "A"), Cells(7, "A"), Cells(5, "E"), Cells(7, "E"), Cells(5, "K"), Cells(7, "K"), Cells(11, "Q"), Cells(13, "Q"), Cells(15, "Q"), Cells(19, "Q"))
   Cl.Value = Cl.Value
Next Cl

Thanks Fluff!

what type of variable is "cl"

Dan

 

[Fluff]

It's a Range

 

[dannyok90]

yep, I was being daft!

Thanks very much, it works a treat :D

Dan

 

[Fluff]

You're welcome & thanks for the feedback