VBA Remove Formulas from all sheets..working but how do I ignore one sheet?

[Melimob]

Hi there

I need to save the file as a new workbook (asking user to choose path) and within that file, remove all formulas.

The code I am using to remove all formulas is:

Sub All_Cells_In_All_WorkSheets_1()
    Dim sh As Worksheet
    
    Sheets("ImportNonCCB").Visible = True
    Sheets("LKUPs").Visible = True
    
    For Each sh In ActiveWorkbook.Worksheets
        sh.Select
        With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next sh
    
    Sheets("CC Reconfiguration Data").Range("I3" & LastRow) _
    .Formula = "=VLOOKUP(tblCCReconfig[Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0)"
    
End Sub

This works however on one of the sheets I want to leave the formulas in there.

Also, you can see that I added a formula back in which works except it's not copying down to last row, only appears in cell I3?

Grateful for advice on any of this as I'm not sure how I'm going to incorporate this with saving a new workbook?

Many thanks

 

[mole999]

something like
If sh.name = xyz then Next sh

 

[JLGWhiz]

For Each sh In ActiveWorkbook.Worksheets        
   [COLOR=#b22222]If sh.Name <> "CC Reconfiguration Data" Then
[/COLOR]      With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
       End With
       Application.CutCopyMode = False
    [COLOR=#b22222]End If
[/COLOR]Next sh

 

[Melimob]

For Each sh In ActiveWorkbook.Worksheets        
   [COLOR=#b22222]If sh.Name <> "CC Reconfiguration Data" Then
[/COLOR]      With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
       End With
       Application.CutCopyMode = False
    [COLOR=#b22222]End If
[/COLOR]Next sh

Thanks again JLGWhiz

however I'm not sure which way to tackle this.

There are two sheets with certain ranges where I want formulas to remain but the rest of the sheets are fine to keep paste special values.

I thought if I make the whole workbook to remove values and then re-add the formulas back in would be easier?

However, my ranges are awkward:

Sheet("COD MAPPING")
formula is every other cell since they're in merged cells
i.e. range D3 =IFERROR(VLOOKUP(D$1,Return_GDS,6,0),"")
range D4 = =IFERROR(VLOOKUP(D$1,Return_GDS,3,0),"")

then repeated across in F3 & F4, H3, & H4 etc.. right across to AZ3 & 4.
The same formulas are in rows 20& 21 same columns.

and then on
Sheet("CC RECONFIGURATION")
I have this which works well except doesn't copy down, only pastes in first cell?

Sheets("CC Reconfiguration Data").Range("I3" & LastRow) _
.Formula = "=VLOOKUP(tblCCReconfig[Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0)"

Otherwise, I could leave the formulas and reference the ranges to copy and paste over however I would have the same issue as the ranges are placed oddly. Unless I can name these ranges and reference a named range? (this can't be in a table though).

cheers

 

[JLGWhiz]

Well, I can tell you that VBA does not cater to merged cells. There are ways of working with them, but it is a pain to write code that has to deal with them. If you can design your database without merged cells, it can make it a lot easier to develop code to handle data and formulas. When I design a database, it is always with the thought that I will need to write code to manage the data. I can always produce a report with fancy fonts, colors and merged cells from the database. But the database itself will be very plain looking.