Stopping the loop to work on few worksheet

Question

Sub Macro5()
    Dim filename As String
    Dim MyRefAddr As String
    Dim MyCopyAddr As String
    Dim MyDestAddr As String
    Dim NoOfCols As Integer
    Dim lastRow As Long
    Dim Distributor As String
    Dim Z As Double
    Dim Davg As Double
    Dim LT As Long
    Dim temp As Long

    Worksheets.Add(Before:=Worksheets(1)).Name = "Norms"
    Sheets("Norms").Select
    Range("A1").Select
    ActiveCell.Value = "SKU"
    MyCopyAddr = Range("A1").Offset(1, 0).Address
    Range("A1").Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=Sheet3!RC"
    Range(MyCopyAddr).AutoFill Destination:=Range(MyCopyAddr + ":A69")

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Sheet1" And ws.Name <> "Norms" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then
            Range("B5").Select
            Selection.End(xlToRight).Select
            MyRefAddr = ActiveCell.Address
            NoOfCols = Application.WorksheetFunction.Count(Range("B5:" + MyRefAddr))
            'STDEV
            MyCopyAddr = Range(MyRefAddr).Offset(-1, 1).Address
            Range(MyRefAddr).Offset(-1, 1).Select
            ActiveCell.Value = "Sigma D"
        End If
    Next ws
End Sub

Whats the error in the code. THe code is going to "norms" sheet and calculating there inspite of excluding it

@simoco Can you plzz explain or give it a try.. I m new to this stuff. — user3564381, Apr 25 '14 at 09:33
I have used If ws.Name <> "Sheet1" And ws.Name <> "Norms" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then to prevent the code to go to mentioned sheets like "Norms". but it is not happening — user3564381, Apr 25 '14 at 09:36
code works fine for me. Try to change `ActiveWorkbook.Sheets` to `ThisWorkbook.Sheets` — Dmitry Pavliv, Apr 25 '14 at 09:38
1) your `IF` works, but 2) in your loop `Range("B5").Select` always refers to range on _active sheet_ (`Norms` in your case) rather than on sheet that is current in loop (`ws`). Also check this [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 25 '14 at 09:41
That's the point i am trying to make . Actually I don't want the code to go to Norms Sheet anytime .. Changed Range("B5").Select to ActiveSheet.Range("B5").Select Still it is computing things in Norms Sheet I know this is silly, but its troubling me from a lot of time — user3564381, Apr 25 '14 at 09:47
I've already told you what to do! `ActiveSheet.Range("B5").Select` and `Range("B5").Select` the same - they both referce to _active sheet_ which is `Norms` because you selected it few lines before: `Sheets("Norms").Select`. Have you read the post I gave you the link in comments above: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select)??? Read it attentively and rewrite your code — Dmitry Pavliv, Apr 25 '14 at 09:50
Changed Code to this as suggested in the post Worksheets.Add(Before:=Worksheets(1)).Name = "Norms" With Sheets("Norms").Range("A1") ActiveCell.Value = "SKU" MyCopyAddr = Range("A1").Offset(1, 0).Address Range("A1").Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=Sheet3!RC" Range(MyCopyAddr).AutoFill Destination:=Range(MyCopyAddr + ":A69") End With 'For Each ws In ThisWorkbook.Sheets If ws.Name <> "Sheet1" And ws.Name <> "Norms" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then Again computing the norms , no relied — user3564381, Apr 25 '14 at 10:15
You need to activate the ws Worksheet right after your 'If ws.Name <> "Sheet1" ..... ' line — Seb, Apr 25 '14 at 11:49

score 0 · Answer 1 · edited May 23 '17 at 10:09

The code calculate in the Norm sheet because it's the active worksheet. All the range in the loop refer to the active sheet. You should avoid using select and activate :

How to avoid using Select in Excel VBA macros

EDIT : Made some cleanup and removed all 'Select' and 'Activate'

Sub Macro5()
    Dim ws As Worksheet
    Dim NoOfCols As Integer
    Dim workSheetNorm As Worksheet, cell As Range

    Set workSheetNorm = Worksheets.Add(Before:=Worksheets(1))
    'workSheetNorm.Name = "Norms"


    Set cell = workSheetNorm.Range("A1")
    cell.Value = "SKU"

    Set cell = cell.Offset(1, 0)
    cell.FormulaR1C1 = "=Sheet3!RC"
    cell.Offset(1, 0).AutoFill Destination:=workSheetNorm.Range(cell.Offset(1, 0), "A69")

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Sheet1" And ws.Name <> "Norms" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then
            Set cell = ws.Range("B5", ws.Cells(5, ws.Columns.Count).End(xlToLeft))

            NoOfCols = Application.WorksheetFunction.Count(cell)
            'STDEV
            cell.Offset(-1, 1).Value = "Sigma D"
        End If
    Next ws
End Sub

I tried to do the same using With Command With Sheets("Norms").Range("A1") ActiveCell.Value = "SKU" MyCopyAddr = Range("A1").Offset(1, 0).Address Range("A1").Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=Sheet3!RC" Range(MyCopyAddr).AutoFill Destination:=Range(MyCopyAddr + ":A69") End With MsgBox (ActiveSheet.Name) For Each ws In ThisWorkbook.Sheets If ws.Name <> "Sheet1" And ws.Name <> "Norms" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then Sheets(ActiveSheet.Name).Select MsgBox (ActiveSheet.Name) no relief — user3564381, Apr 25 '14 at 12:26

score 0 · Answer 2 · answered Apr 25 '14 at 12:52

You just need to reference all Ranges to ws?
Something like this?:

For Each ws In ActiveWorkbook.Sheets 
    If ws.Name <> "Sheet1" And ws.Name <> "Norms" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then
        ws.Range("B5").Select 
        Selection.End(xlToRight).Select 
        MyRefAddr = ActiveCell.Address 
        NoOfCols = Application.WorksheetFunction.Count(ws.Range("B5:" + MyRefAddr)) 'STDEV 
        MyCopyAddr = ws.Range(MyRefAddr).Offset(-1, 1).Address 
        ws.Range(MyRefAddr).Offset(-1, 1).Select 
        ActiveCell.Value = "Sigma D" 
    End If 
Next ws

Stopping the loop to work on few worksheet

2 Answers2