Running code in multiple worksheets

Question

Trying to run code on an Excel document that has compiled data. I am running two macros individually on each sheet. I am unable to run this macro on all the worksheets in this workbook. First macro is for sorting the columns:

Sub Macro1()
Dim ws As WorkSheet
Range("Sort").Select
Selection.Sort Key1:=ws.Range("q66"), Order1:=xlAscending, Key2:=ws.Range("u66") _
    , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
Next WS
End Sub

Second macro is for hiding the rows based on value "Null":

Sub HideList()
Dim ws As Worksheet
Dim cell As Range

For Each cell In ws.Range("HideList")
    With cell
        .EntireRow.Hidden = _
            (Value = "Empty")
    End With
 Next ws

 End Sub

It works if I remove the ws. from the range but again works only for single sheet.

You need to set the ws either through a `For Each ws in activeworkbook.sheets` or set each individual sheet `Set ws = Sheets("Sheet1")` — Scott Craner, Nov 11 '15 at 23:37
I did, but I get errors Sub Macro1() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets Range("Sort").Select Selection.Sort Key1:=Range("q66"), Order1:=xlAscending, Key2:=Range("u66") _ , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Next ws End Sub — Sean Aslam, Nov 11 '15 at 23:41
The `Range("Sort")` likely has a workbook scope. You cannot use it on individual worksheets. Better to define each worksheet's sorting range 'manually'. See [this recent question](http://stackoverflow.com/questions/33661309/streamline-a-recorded-macro-that-spans-over-multiple-tabs-and-sorts-on-different/33661909#33661909). — , Nov 11 '15 at 23:45

nutsch · Answer 1 · 2015-11-11T23:50:36.197

0

You still have a couple ranges that do not reference the relevant worksheet. Update as follows:

Sub Macro1()
Dim ws As WorkSheet
For Each ws in activeworkbook.sheets 
 ws.Range("Sort").Sort Key1:=ws.Range("q66"), Order1:=xlAscending, Key2:=ws.Range("u66") _
    , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
Next WS
End Sub

Sub Delete_Name_Date()
   Dim cell As Range
For Each ws in activeworkbook.sheets 
    For Each cell In ws.Range("R66:R306")
      With cell
        .EntireRow.Hidden = _
            (.Value = "Empty")
       End With
    Next
next ws
End Sub

edited Nov 11 '15 at 23:50

answered Nov 11 '15 at 23:44

nutsch

5,922
2
20
35

Thanks for the macro. The second macro is working fine across the spreadsheet. The first one fails with run time error 1004 "Method Range of object _Worksheet failed". ws.Range is whats causing the issue. Any suggestions? – Sean Aslam Nov 12 '15 at 14:24
do you have a range named "Sort" on each worksheet of the workbook? – nutsch Nov 12 '15 at 15:19

Running code in multiple worksheets

1 Answers1