Kategorien
Allgemeines

Excel macro loop by sheet

Collect data from multiple excel tabs and put them together at a new collector sheet:

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MA_NAME = ActiveWorkbook.Worksheets(I).Name

vJanuar = ActiveWorkbook.Worksheets(I).Cells(8, 3)
vFebruar = ActiveWorkbook.Worksheets(I).Cells(8, 4)
vMarch = ActiveWorkbook.Worksheets(I).Cells(8, 5)

vzJanuar = ActiveWorkbook.Worksheets(I).Cells(9, 3)
vzFebruar = ActiveWorkbook.Worksheets(I).Cells(9, 4)
vzMarch = ActiveWorkbook.Worksheets(I).Cells(9, 5)

ActiveWorkbook.Worksheets(239).Cells(I, 1) = MA_NAME

ActiveWorkbook.Worksheets(239).Cells(I, 3) = vJanuar
ActiveWorkbook.Worksheets(239).Cells(I, 4) = vFebruar
ActiveWorkbook.Worksheets(239).Cells(I, 5) = vMarch

ActiveWorkbook.Worksheets(239).Cells(I, 7) = vzJanuar
ActiveWorkbook.Worksheets(239).Cells(I, 8) = vzFebruar
ActiveWorkbook.Worksheets(239).Cells(I, 9) = vzMarch

Next I

End Sub

Schreibe einen Kommentar