CODEWITHSUNDEEP
vbaexcel
cgnx
cgnx

Reputation: 113

Macro for multiple sheets doesn't work properly

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.

Upvotes: 0

Views: 1185

Answers (1)

Horaciux
Horaciux

Reputation: 6477

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

Upvotes: 1

Related Questions