Run Macro in All Worksheets Except One

[Ramiro]

I need help with a Macro that runs in all sheets except one called "Data", that in all the other sheets changes all the formulae to values.


Thanks in Advance.

 

[hiker95]

Ramiro,

Try:

Option Explicit
Sub test()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Data" Then

    
    '***** the rest of your macro code goes here *****

    
  End If
Next ws
End Sub

 

[hiker95]

Ramiro,

Try:

Option Explicit
Sub testV2()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Data" Then

    
    With ws.UsedRange
      .Value = .Value
    End With

    
  End If
Next ws
End Sub

 

[hiker95]

Ramiro,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub testV2()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Data" Then

    
    With ws.UsedRange
      .Value = .Value
    End With

    
  End If
Next ws
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the testV2 macro.

 

[Ramiro]

Thank you very much hiker95 it worked perfectly.

 

[hiker95]

Ramiro,

You are very welcome. Glad I could help.

Thanks for the feedback.

And, come back anytime.

 

[shredder11]

I apologize for dredging out this topic but it fits my question perfectly and I was unable to follow what you suggested before. my code:

Sub Set_Scores()

    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "all_parameters" Then
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AG$324"), , xlYes).Name = _
                "Tabella1"
            Range("Tabella1[#All]").Select
            ActiveSheet.ListObjects("Tabella1").TableStyle = "TableStyleMedium2"
            Range("Tabella1[[#Headers],[Rank Rating]]").Select
            ws.ListObjects("Tabella1").Sort. _
                SortFields.Clear
            ws.ListObjects("Tabella1").Sort. _
                SortFields.Add Key:=Range("Tabella1[[#All],[Rank Rating]]"), SortOn:= _
                xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ws.ListObjects("Tabella1").Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
          End If
          
    End With
        
    Next ws
    
End Sub

do you happen to know what's wrong with it? the result is that it only applies the Macro on the "all_parameters" sheet, the only one I hoped to spare (as highlighted in the code). the macro was started from this sheet though (in the case it has some importance)

 

[hiker95]

shredder11,

I do not have your workbook/worksheets for testing.

But, I just tried the code you posted in your reply #7, in a test workbook/worksheets, and, found the problems, I think.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub Set_Scores_V2()
' hiker95, 04/14/2016, ME735788
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "all_parameters" Then
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AG$324"), , xlYes).Name = "Tabella1"
    Range("Tabella1[#All]").Select
    ActiveSheet.ListObjects("Tabella1").TableStyle = "TableStyleMedium2"
    Range("Tabella1[[#Headers],[Rank Rating]]").Select
    ws.ListObjects("Tabella1").Sort.SortFields.Clear
    ws.ListObjects("Tabella1").Sort.SortFields.Add Key:=Range("Tabella1[[#All],[Rank Rating]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.ListObjects("Tabella1").Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  End If
Next ws
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Set_Scores_V2 macro.

 

[polkadotbikini5]

I have been trying to run a simple goal seek formula on multiple worksheets within a workbook, but am failing. It only ever works on the active sheet. I tried using the code referenced below with the following macro:

Range("H53").GoalSeek Goal:=0.1, ChangingCell:=Range("I15")

The worksheets I want the macro to run on are named "Project 1" through "Project 20" (so there are 20 worksheets). The worksheets I do NOT want the macro to run on are named "Data" and "Input". Any suggestions on how to make this work?

Thanks!

Ramiro,

Try:

Option Explicit
Sub test()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Data" Then

    
    '***** the rest of your macro code goes here *****

    
  End If
Next ws
End Sub

 

[hiker95]

polkadotbikini5,

Welcome to the MrExcel forum.

My versions of Excel do not support GoalSeek.

You could examine the below macro, and, it may get you started in the right direction.

Sub test()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

  If ws.Name <> "Data" And ws.Name <> "Input" Then

    
    '***** the rest of your macro code goes here *****

    
  End If
Next ws
End Sub

Maybe someone else on MrExcel will be able to help you.