Run this macro on all sheets except those I'd like to exclude

[Adam1027]

Hello,

I am trying to get this macro to run in every sheet in the workbook except for the sheets I'd like to exclude. I've spent all day reading similar questions but havne't been able to get any of the code to work. Any help is appreciated.

(Sorry it when i tagged it as code it was cutting off half of the macro.)


Sub Delete_OldDate_Rows()


'Delete Old Rows

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A76").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC2=ANRSCo!R5C2,""Copy"",IF(RC2<anrsco!r5c2,""delete"",""false""))"
Range("A76").Select
Selection.AutoFill Destination:=Range("A76:A3000"), Type:=xlFillDefault
Range("A76:A3000").Select


'Copy Paste the last months row as values

Dim cell As Range
Dim myrows As String
For Each cell In Range("A1:A" & [A65536].End(xlUp).Row)
If cell.Value = "Copy" Then
myrows = myrows & cell.EntireRow.Address & ","
End If
Next cell
myrows = Left(myrows, Len(myrows) - 1)
Range(myrows).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A79").Select
Application.CutCopyMode = False


' Delete Rows With Delete in Column A

Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$3000").AutoFilter Field:=1, Criteria1:="Delete"
Rows("76:76").Select
Rows("76:3000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A76").Select

' Hide Rows with "Date" in them

Rows("6:6").Select
Selection.EntireRow.Hidden = True
Rows("8:8").Select
Selection.EntireRow.Hidden = True
Rows("10:10").Select
Selection.EntireRow.Hidden = True
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("14:14").Select
Selection.EntireRow.Hidden = True
Rows("16:16").Select
Selection.EntireRow.Hidden = True
Rows("18:18").Select
Selection.EntireRow.Hidden = True
Rows("20:20").Select
Selection.EntireRow.Hidden = True
Rows("22:22").Select
Selection.EntireRow.Hidden = True
Rows("24:24").Select
Selection.EntireRow.Hidden = True
Rows("26:26").Select
Selection.EntireRow.Hidden = True
Rows("28:28").Select
Selection.EntireRow.Hidden = True
Rows("30:30").Select
Selection.EntireRow.Hidden = True
Rows("32:32").Select
Selection.EntireRow.Hidden = True
Rows("34:34").Select
Selection.EntireRow.Hidden = True
Rows("36:36").Select
Selection.EntireRow.Hidden = True
Rows("38:38").Select
Selection.EntireRow.Hidden = True
Rows("40:40").Select
Selection.EntireRow.Hidden = True
Rows("42:42").Select
Selection.EntireRow.Hidden = True
Rows("44:44").Select
Selection.EntireRow.Hidden = True
Rows("46:46").Select
Selection.EntireRow.Hidden = True
Rows("48:48").Select
Selection.EntireRow.Hidden = True
Rows("50:50").Select
Selection.EntireRow.Hidden = True
Rows("52:52").Select
Selection.EntireRow.Hidden = True
Rows("54:54").Select
Selection.EntireRow.Hidden = True
Rows("56:56").Select
Selection.EntireRow.Hidden = True
Rows("58:58").Select
Selection.EntireRow.Hidden = True
Rows("60:60").Select
Selection.EntireRow.Hidden = True
Rows("62:62").Select
Selection.EntireRow.Hidden = True
Rows("64:64").Select
Selection.EntireRow.Hidden = True
Range("A5").Select


End Sub</anrsco!r5c2,""delete"",""false""))"

 

[Adam1027]

1

 

[daverunt]

Hi,

there are plenty of examples on this site. I've modified the one here to include another sheet.

http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html

Sub test()
' hiker95, 10/29/2013
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Sheet1" And ws.Name <> "Sheet4" Then
  ws.Activate

    
    '***** put your macro code here *****

    
  End If
Next ws
End Sub

 

[Adam1027]

Yes, thank you. I have used that exact code before. However, I keep receiving an Invalid Procedure Call or Argument on a specific part f the code below:

myrows = Left(myrows, Len(myrows) - 1)

Any Ideas?

It works without that piece.

 

[daverunt]

That code just removes the last ',' from myrows after building up the string.

if 'Copy' doesn't exist (case is important too) in the range then myrows = "".
The invalid call is because you are trying to shorten a zero length string by 1.

Try checking myrows before that line:

For Each cell In Range("A1:A" & [A65536].End(xlUp).Row)
 If cell.Value = "Copy" Then
 myrows = myrows & cell.EntireRow.Address & ","
 End If
 Next cell
 If Len(myrows) > 0 Then     'or you could use If myrows <> "" Then
 myrows = Left(myrows, Len(myrows) - 1)
 Range(myrows).Select
 End If