Macro for multiple sheets doesn't work properly

Question

Sub RunMacroOnAllSheetsToRight()
    For i = ActiveSheet.Index To Sheets.Count
        Call MyFunction(i)
    Next i
End Sub

Function MyFunction(i)
    'Code goes here
     Columns("R:R").ColumnWidth = 8.1
     [S1].Resize(, 14).EntireColumn.Insert
    MsgBox "I'm currently on sheet " & ThisWorkbook.Sheets(i).name
End Function

I found a sample of code for running macro that should run on all sheets on the right from the active one, but it is not working, it keeps running on one sheets, but the msgbox shows me that the sheets are changed(each time it displays different name). Can you help me? I am new to vba-excel.

[How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Jul 03 '14 at 20:11
@simoco thank you for interesting resource. I will read through it :) I still have a lot to learn. — cgnx, Jul 03 '14 at 20:18

Horaciux · Accepted Answer · 2014-07-03T20:13:28.980

1

You need to activate each sheet. Then activate original sheet.

  Sub RunMacroOnAllSheetsToRight()
  Dim a As Integer

    a = ActiveSheet.Index 'Save current sheet

    For i = a To Sheets.Count
        Call MyFunction(i)
    Next i
   Sheets(a).Activate 'At the end, activate original sheet
End Sub

Function MyFunction(i)
    'Code goes here
    Sheets(i).Activate 'Activate each sheet
     Columns("R:R").ColumnWidth = 8.1
     [S1].Resize(, 14).EntireColumn.Insert
    MsgBox "I'm currently on sheet " & ActiveSheet.Name 'Trustworthy information
End Function

edited Jul 03 '14 at 20:13

answered Jul 03 '14 at 20:08

Horaciux

6,322
2
22
41

thank you it works like charm, I have one miniquestion, how can I have msgbox only show on which sheet it currently is without having to click on ok button? – cgnx Jul 03 '14 at 20:13
@D.N Glad it works, please acept the answer or up vote it. There is not possible. A message box waits for you to click. – Horaciux Jul 03 '14 at 20:16
@D.N Use `Debug.Print "I'm currently on sheet " & ActiveSheet.Name` to log activity without having to click on ok button – Horaciux Jul 03 '14 at 20:18
I guess this runs too fast for me to see anything, but thank you anyway. I really appreciate it. – cgnx Jul 03 '14 at 20:22

Macro for multiple sheets doesn't work properly

1 Answers1