I have the following macro below when unprotects all the sheets and unhides hidden columns before running the macro to copy the formulas and paste these as values but retaining the formats (Sub ValueCopyCommSheets)
None of the formulas and being copied as values when running the macro "Sub valueCopyCommSheets"
It would be appreciated if someone could assist me
None of the formulas and being copied as values when running the macro "Sub valueCopyCommSheets"
It would be appreciated if someone could assist me
Code:
Sub UnProtectAll()
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).Unprotect
Next
Application.ScreenUpdating = True
End Sub
Sub UnProtectactive()
With ActiveSheet
.Unprotect
End With
End Sub
Sub Unhide_All_Columns_in_Workbook()
Application.ScreenUpdating = False
UnProtectAll
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).Columns.Hidden = False
Next
Application.ScreenUpdating = True
UnProtectAll
End Sub
Sub ValueCopyCommSheets()
Dim i As Long
'Unhide_All_Columns_in_Workbook
For i = Sheets("southern").Index To Sheets("BR1 EL").Index
UnProtectAll
Unhide_All_Columns_in_Workbook
With Sheets(i)
' If .Visible Then
.UsedRange.Copy
.UsedRange.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
'End If
End With
Next i
SaveFileasVaues
End Sub
Sub SaveFileasVaues()
ActiveWorkbook.Save
Dim wb As Workbook, OldName$, NewName$
Set wb = ActiveWorkbook
OldName = wb.Name
NewName = Left(OldName, InStrRev(OldName, ".") - 1)
NewName = NewName & " Values.xlsm"
wb.SaveAs wb.Path & "\" & NewName, wb.FileFormat
End Sub