title: Run a VBA macro to trigger macros in different sheets
body: <p>The situation is like this:
  I have <strong>40 worksheets embedded with same Macros called RetrieveNumbers</strong>. The end results in 40 sheets will be different based upon various parameters in each worksheet. </p>
  
  <p>To update the numbers, I manually click the macro buttons to retrieve numbers in the 40 worksheets. As a result of that, I'm sick of it. To simplify the testing, I only use two sheets(Sheet1, Sheet2) to test if, by clicking a Macro named RunAll, it would run through the two Macros.</p>
  
  <p>Surely, I have FAILED.</p>
  
  <p>I have tried :</p>
  
  <ol>
  <li><p>application.run</p></li>
  <li><p>call </p></li>
  </ol>
  
  <p>Two scenarios I tried:</p>
  
  <ol>
  <li><p>I hit F5 as I was in the RunAll window and my other screen on <strong>the Sheet1 worksheet</strong>. It runs perfectly <strong>and yet it runs twice in Sheet1 rather than going to Sheet2.</strong></p></li>
  <li><p>I hit F5 as I was in the RunAll window and my another screen on <strong>the RunAll worksheet</strong>. After clicking it, I went back to see if there were any numbers. And surely, there weren't.</p></li>
  </ol>
  
  <p>I thought the Macro would go to Sheet2 and then run Macro Retrivenumbers2. But it didn't. It stayed at the current worksheet. Please give me some guidance on how to run the next sheets I want. Let me know if I need to clarify more on this question.</p>
  
  <p>The Macro RetrieveNumbers<br>
  (Since the Macro RetrieveNumbers2 is as same as RetrieveNumbers1, I don't include it)</p>
  
  <pre><code>Sub RetrieveNumbers1()
  
  Dim NumberFiles As Integer, FilesVisited As Integer, RowNumber As Integer
  Let NumberFiles = ActiveSheet.Cells("2", "A").Value
  Let FilesVisited = 0                            'start from 0
  Let RowNumber = 4                            'start from column B
  
  
  If NumberFiles &gt; 30 Then
      MsgBox "Don't try to retrieve numbers from more than 30 files at a time!"
  Else
      For FilesVisited = 1 To NumberFiles
  
      'Open files, get path, file, tab name and cells
      Dim PathFileOpen As String, NameFileOpen As String, NameTab As String, FileDir As String
      Let PathFileOpen = ActiveSheet.Cells(RowNumber, "A").Text
      Let NameFileOpen = ActiveSheet.Cells(RowNumber, "B").Text
      Let NameTab = ActiveSheet.Cells(RowNumber, "C").Text
  
      Dim N As Integer, Cell As String, NumberYears As String, FullLink As String
      NumberYears = ActiveSheet.Cells("2", "B").Value
      For N = 4 To NumberYears + 3
          Cell = ActiveSheet.Cells(RowNumber, N).Text
          FullLink = "(=)'" &amp; PathFileOpen &amp; "\[" &amp; NameFileOpen &amp; ".xlsm]" &amp; NameTab &amp; "'!" &amp; Cell
          ActiveSheet.Cells(RowNumber, N + 13).Value = FullLink
      Next N
      RowNumber = RowNumber + 1
  Next FilesVisited
  End If
  
  ActiveSheet.Range("A1").CurrentRegion.Replace What:="(=)", Replacement:="=", _
  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, 
  SearchFormat:=False, ReplaceFormat:=False
  
  End Sub
  </code></pre>
  
  <p>The Macro RunAll</p>
  
  <pre><code>Sub runall()
  
  Call Sheet1.RetrieveNumbers1
  Call Sheet2.RetrieveNumbers2
  
  End Sub
  </code></pre>
  
  <p><strong>Clear Example of the file</strong>
  <a href="https://i.stack.imgur.com/FXPDs.png" rel="nofollow noreferrer">Working file example</a></p>