VBA to remove conditional formatting and past values

[mountainman88]

Im looking for 4 separate pieces of VBA please

1. cycle through every tab in a workbook and remove all conditional formatting
2. cycle through every tab in a workbook and paste values entire tab
3. cycle through tabs in a workbook but only those on a list of tabs in a tab called 'listCF' and remove conditional formatting
4. cycle through tabs in a workbook but only those on a list of tabs in a tab called 'listPV' and remove conditional formatting and paste values entire tab

many thanks

 

[Jamie McMillan]

Hello,

Part 1

Sub FormatinGone()
Dim ws As Worksheet
    For Each ws In Worksheets
    ws.Cells.FormatConditions.Delete
    Next ws
End Sub

I don't know what - and paste values entire tab - means.

The above code should get you started.

Jamie

 

[mountainman88]

Hello,

Part 1

Sub FormatinGone()
Dim ws As Worksheet
    For Each ws In Worksheets
    ws.Cells.FormatConditions.Delete
    Next ws
End Sub

I don't know what - and paste values entire tab - means.

The above code should get you started.

Jamie

literally select all, copy the tab and then paste values back onto the tab removing all formulas. Do this for each tab. Thanks

 

[Jamie McMillan]

Hello,

This will copy and paste the values from A1:L21. Change the selection area to the largest you have.

Sub CopyAll()
Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Worksheets
    ws.Select
    Cells.Range("A1:L21").Copy 
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Next ws
    Application.ScreenUpdating = True
End Sub

Jamie

 

[Fluff]

Another option

Sub mountainman()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      With Ws.UsedRange
         .FormatConditions.Delete
         .Value = .Value
      End With
   Next Ws
End Sub