VBA to Select all sheets (except 3), and copy and paste as values

[Huples_Cat]

Hi,

I have a file that generates several worksheets based on a template one (changing certain parameters) and what i need is a macro that selects all those sheets, copies and pastes over them as values so as to hard-code the data, leaving 3 tabs alone.

i have a macro to delete all except those same three sheets so have tried using record macro to get an idea of what i need to amend it with but no luck as of yet.

Here's my delete macro, which includes the three worksheets i also don't want to copy/paste-as-values in the new macro. The s.Delete bit is clearly what i need to relace!

Any ideas anyone?

Dim s

Application.DisplayAlerts = False
For Each s In Sheets
If s.Name <> "Template" And s.Name <> "Input" And _
s.Name <> "Lookup" Then
s.Delete
End If
Next
Application.DisplayAlerts = True

 

[rpaulson]

try this.

Sub Do_it()

For Each s In Sheets

If s.Name = "Template" Then GoTo 999
If s.Name = "Input" Then GoTo 999
If s.Name = "Lookup" Then GoTo 999

    s.Cells.Copy
    s.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

999 Next

End Sub