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.

 

[Lehcar]

Afternoon,
Self taught user here (I still search web for most answers).
Hiker95 - I have tried your code and I understand the theory behind the loop code:
'HIKER95 original code
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

But my code is using a reference to a ListObject (who's name I will NOT know because another macro creates all my sheets and the ListObject tables are auto named and the number of sheets will vary month on month). Apologies if this is not the correct place to put the question.

When I write my code WITHOUT the loop and run it on a sheet then manually selected the next page and run it, it works fine, but when I run your code with mine I get a Run-time Error '1004' AutoFilter method of Range class failed. This is probably down to the

Can you assist? Thanks in advance

My code is:
Private Sub p3DataExtract()
‘Adapted from: hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html

' Object is to search data on each persons sheet in the Recipient column and only show their data, by deleting all the other rows off. Master and Names sheets are not to be checked.

Dim ws As Worksheet

'Loop through all sheets and run this code
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Master" And ws.Name <> "Names" Then
With ws
' Hard paste result of formula in cell A1
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Filter for rows not showing whatever is in Cell A1 and then delete these. Then remove auto filter to leave me relevant data
' For the benefit of this post - the A1 cell is looking for the TAB name, The tab name is based on a list of unique names created from column 7 in the Master sheet, so it will always find 1 row of data minimum

ActiveSheet.ListObjects(1).Range.AutoFilter Field:=7, Criteria1:="<>" & Range("A1").Value, Operator:=xlAnd 'THIS IS WHERE I GET THE ERROR
Rows("3:" & Rows.Count).EntireRow.Delete ' first row of data (row A is NOT part of the ListObject Table) and then delete these
ActiveSheet.ListObjects(1).Range.AutoFilter Field:=7 ' remove the autofilter from column 7 of the data range
End With
End If
Next ws

End Sub

 

[hiker95]

Lehcar,

I can not follow what you are trying to do.

In order to assist you it would help us if you posted all of your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]


And, it would help if we could see your actual raw data workbook/worksheets.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Lehcar]

I am not allowed to plaster the raw data. Thanks for taking the time to read my post.

 

[ZVI]

... When I write my code WITHOUT the loop and run it on a sheet then manually selected the next page and run it, it works fine, but when I run your code with mine I get a Run-time Error '1004' AutoFilter method of Range class failed. ...

Hi,
Try this:

Private Sub p3DataExtract()
  'Adapted from: hiker95 , 10 / 29 / 2013
  ' http://www.mrexcel.com/forum/excel-q...xcept-one.html
 
  ' Object is to search data on each persons sheet in the Recipient column
  ' and only show their data, by deleting all the other rows off.
  ' Master and Names sheets are not to be checked.
 
  Dim ws As Worksheet
 
  'Loop through all sheets and run this code
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Master" And ws.Name <> "Names" Then
      If ws.ListObjects.Count > 0 Then
        With ws
          ' Replace formula in A1 of ws for its value,
          ' this can be ommited, uncomment the line below if it;s required
          '.Range("A1").Value = .Range("A1").Value
         
          ' Filter for rows not showing whatever is in Cell A1 and then delete these.
          ' Then remove auto filter to leave me relevant data
          .ListObjects(1).Range.AutoFilter Field:=7, Criteria1:="<>" & .Range("A1").Value, Operator:=xlAnd
          .Rows("3:" & .Rows.Count).EntireRow.Delete ' first row of data (out of the ListObject Table) and then delete these
          .ListObjects(1).Range.AutoFilter Field:=7  ' remove the autofilter from column 7 of the data range
        End With
      End If
    End If
  Next
 
End Sub

 

[Domroy]

My turn to have problems here! I'm trying to use this code to clear all worksheets in my workbook, except "Active Listings" - and it's ONLY clearing "Active Listings" - LOL

What am I doing wrong?!

Sub ClearTable()

' ClearTable Macro
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    
    If ws.Name <> "Active Listings" Then
'
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A3").Select
    End If
    Next ws
End Sub

 

[ZVI]

Hi,
Try this:

Sub ClearTable()
 
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Active Listings" Then
      ws.Range("A1", ws.UsedRange).Offset(2).EntireRow.Delete
      ws.Activate
      ws.Range("A3").Select
    End If
  Next
 
End Sub

 

[Domroy]

YES!!!! Thank you! It worked (which, I'm sure isn't a surprise to you)! Thanks so much!

 

[ZVI]

Glad you've got it working!

 

[hopstr]

Brand new to VBA and have done a lot of online searching to get codes for a spreadsheet I'm working on for a client. I've tried the code originally posted by hiker95 but I'm getting the Next without For error. I haven't been able to catch onto what this means. I'm trying to get the tabs on a spreadsheet to update to the last day on a pay period except the TEMPLATE tab. Could anyone instruct me on how to fix this?

The code I'm trying to get to work is below:

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 <> "TEMPLATE" Then


On Error Resume Next
If Len(ws.Range("A2")) > 0 Then
ws.Name = Replace(ws.Range("A2").Value, "/", "-")


End If
Next ws
End Sub

 

[hopstr]

My apologies, the code below.

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 <> "TEMPLATE" Then


    On Error Resume Next
    If Len(ws.Range("A2")) > 0 Then
        ws.Name = Replace(ws.Range("A2").Value, "/", "-")


  End If
Next ws
End Sub