ausführen von mehreren Makros, um über mehrere excel-Arbeitsmappen - vba

Ich habe nicht deine Makros, so habe ich erstellt dummy-Makros, die Ausgabe einiger Werte in das Direktfenster für jedes Blatt von jeder Arbeitsmappe (mit Ausnahme der Arbeitsmappe mit den Makros).

Du code angezeigt wird, hängt von der Ausgabe makro-Aktivierung jedes Arbeitsblatt. Dies ist eine schlechte Praxis. Ich gehe in die Arbeitsmappe und den Namen des Arbeitsblattes, zu dem Sie die Makros. I output den Wert von Zelle A1 ( .Cells(1, 1).Value ), um zu zeigen, wie es gemacht wird.

Ich hoffe das ist genug, um Ihnen den Einstieg. Fragen, wenn etwas unklar ist.

Option Explicit
Sub ControlCall()

  Dim FileNameCrnt As String
  Dim InxWSheet As Long
  Dim MsgErr As String
  Dim PathCrnt As String
  Dim RowReportCrnt As Long
  Dim WBookCtrl As Workbook
  Dim WBookOther As Workbook
  Dim WSheetNameOtherCrnt As String

  If Workbooks.Count > 1 Then
    ' It is easy to get into a muddle if there are multiple workbooks
    ' open at the start of a macro like this.  Avoid the problem.
    Call MsgBox("Please close all other workbooks " & _
                "before running this macro", vbOKOnly)
    Exit Sub
  End If

  Application.ScreenUpdating = False

  Set WBookCtrl = ActiveWorkbook

  ' Assume all the workbooks to be processed are in the
  ' same folder as the workbook containing this macro.
  PathCrnt = WBookCtrl.Path

  ' Add a slash at the end of the path if needed.
  If Right(PathCrnt, 1) <> "\" Then
    PathCrnt = PathCrnt & "\"
  End If

  FileNameCrnt = Dir$(PathCrnt & "*.xl*")

  Do While FileNameCrnt <> ""

    If FileNameCrnt <> WBookCtrl.Name Then
      ' Consider all workbooks except the one containing this macro
      Set WBookOther = Workbooks.Open(PathCrnt & FileNameCrnt)

      For InxWSheet = 1 To WBookOther.Worksheets.Count
        WSheetNameOtherCrnt = WBookOther.Worksheets(InxWSheet).Name

        Call macro_1(WBookOther, WSheetNameOtherCrnt)
        Call macro_2(WBookOther, WSheetNameOtherCrnt)
        Call macro_3(WBookOther, WSheetNameOtherCrnt)
        Call macro_4(WBookOther, WSheetNameOtherCrnt)
        Call macro_5(WBookOther, WSheetNameOtherCrnt)
        Call macro_6(WBookOther, WSheetNameOtherCrnt)
      Next
      WBookOther.Close SaveChanges:=False
    End If
 FileNameCrnt = Dir$()
Loop

Application.ScreenUpdating = True

End Sub
Sub macro_1(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "1 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_2(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "2 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_3(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "3 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_4(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "4 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_5(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "5 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_6(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "6 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub