Excel Macro Loop. Looking for code to loop through entire workbook

Question

I am new to making macros and have basically no experience with VBA. However, I have managed to cobble together the code below.

The code is supposed to load data from a worksheet (the name of which I enter on the first active sheet, then run the macro via a linked button) into the first empty row of the active sheet.

What I need for it to do is basically the compilation code, where it is handles all the copying and pasting for me. I need it to repeat the code for every worksheet in the workbook, continuously loading the data into the next blank row until it has processed all sheets. Currently, as mentioned, I load each worksheet by entering the worksheet name into a specific cell on the active sheet and hit a linked button which runs the macro.

Can any one help me modify this code?

Sub LoadData()
    Dim RawSheet, ReportSheet As Worksheet
    Dim ReportRow, BlankCount, i As Integer
    Dim RawName As String
    Dim NegativeNumber, Average As Double
    RawName = Cells(1, 8)
    Set RawSheet = Worksheets(RawName)
    Set ReportSheet = ActiveSheet
    For i = 9 To 1000
        If ReportSheet.Cells(i, 5) = "" Then
            ReportRow = i
            Exit For
        End If
    Next i
    BlankCount = 0
    Mode = ""
    For i = 1 To 1000
        If (RawSheet.Cells(i, 1) = "") Then
            If BlankCount > 10 Then
                Exit For
            Else
                BlankCount = BlankCount + 1
            End If
        Else
            BlankCount = 0
            If RawSheet.Cells(i, 1) = "Series Name" Then
                ReportSheet.Cells(ReportRow, 5) = RawSheet.Cells(i, 2)
            ElseIf RawSheet.Cells(i, 1) = "Series Date" Then
                ReportSheet.Cells(ReportRow, 7) = RawSheet.Cells(i, 2)
            ElseIf RawSheet.Cells(i, 1) = "Weight" Then
                ReportSheet.Cells(ReportRow, 8) = RawSheet.Cells(i, 2)
            ElseIf RawSheet.Cells(i, 3) = "Temperature" Then
                ReportSheet.Cells(ReportRow, 9) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "Heart Rate" Then
                ReportSheet.Cells(ReportRow, 10) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "LVAW;d" Then
                ReportSheet.Cells(ReportRow, 11) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "LVAW;s" Then
                ReportSheet.Cells(ReportRow, 14) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "LVPW;d" Then
                ReportSheet.Cells(ReportRow, 13) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "LVPW;s" Then
                ReportSheet.Cells(ReportRow, 16) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "LV Mass Cor" Then
                ReportSheet.Cells(ReportRow, 17) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "Diameter;d" Then
                ReportSheet.Cells(ReportRow, 12) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "Diameter;s" Then
                ReportSheet.Cells(ReportRow, 15) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "LA" Then
                ReportSheet.Cells(ReportRow, 18) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "Ejection Fraction" Then
                ReportSheet.Cells(ReportRow, 19) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "Fractional Shortening" Then
                ReportSheet.Cells(ReportRow, 20) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "V;d (LV Trace)" Then
                ReportSheet.Cells(ReportRow, 21) = RawSheet.Cells(i, 4)
            ElseIf RawSheet.Cells(i, 1) = "V;s (LV Trace)" Then
                ReportSheet.Cells(ReportRow, 22) = RawSheet.Cells(i, 4)
            ElseIf RawSheet.Cells(i, 3) = "Stroke Volume" Then
                ReportSheet.Cells(ReportRow, 23) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 3) = "Cardiac Output" Then
                ReportSheet.Cells(ReportRow, 24) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "LV MPI IV" Then
                ReportSheet.Cells(ReportRow, 27) = RawSheet.Cells(i, 4)
            ElseIf RawSheet.Cells(i, 1) = "MV E" Then
                ReportSheet.Cells(ReportRow, 28) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "MV A" Then
                ReportSheet.Cells(ReportRow, 29) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "MV E/A" Then
                ReportSheet.Cells(ReportRow, 30) = RawSheet.Cells(i, 4)
            ElseIf RawSheet.Cells(i, 1) = "MV Decel" Then
                ReportSheet.Cells(ReportRow, 31) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "IVRT" Then
                ReportSheet.Cells(ReportRow, 32) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "IVCT" Then
                ReportSheet.Cells(ReportRow, 33) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "S WAVE" Then
                ReportSheet.Cells(ReportRow, 34) = RawSheet.Cells(i, 5)
            ElseIf RawSheet.Cells(i, 1) = "E'" Then
                NegativeNumber = RawSheet.Cells(i, 5)
                ReportSheet.Cells(ReportRow, 35) = -NegativeNumber
            ElseIf RawSheet.Cells(i, 1) = "A'" Then
                NegativeNumber = RawSheet.Cells(i, 5)
                ReportSheet.Cells(ReportRow, 36) = -NegativeNumber
            ElseIf RawSheet.Cells(i, 1) = "E'/A'" Then
                ReportSheet.Cells(ReportRow, 37) = RawSheet.Cells(i, 4)
            ElseIf RawSheet.Cells(i, 1) = "MV E/E'" Then
                NegativeNumber = RawSheet.Cells(i, 4)
                ReportSheet.Cells(ReportRow, 38) = -NegativeNumber
            ElseIf RawSheet.Cells(i, 1) = "AET" Then
                ReportSheet.Cells(ReportRow, 39) = RawSheet.Cells(i, 5)
            End If
        End If
    Next i

End Sub

Do all of your source sheets have the same structure, and are there values in both columns 2 and 5 for the same value of i ? If Yes then it seems like you'd be missing values? — Tim Williams
– Tim Williams, Commented Nov 10 at 16:35
Actually, I don't understand why you're using VBA at all, when you could just code these as VLookip formulas in the REPORT worksheet. Code the formulas once and the just FILL DOWN each time you want to snapshot another row. — Chris Maurer
– Chris Maurer, Commented Nov 10 at 16:44

Tim Williams · Accepted Answer · 2025-11-11 16:56:32Z

If you need to load more than one value from the same row then this might be an improvement:

Sub LoadAllSourceSheets()
    Dim ws As Worksheet, wb As Workbook, ReportSheet As Worksheet
    Dim ReportRow As Range
    
    Set wb = ActiveWorkbook 'or ThisWorkbook
    Set ReportSheet = ActiveSheet
    'this is the next empty report row to be populated
    Set ReportRow = ReportSheet.cells(Rows.Count, 5).End(xlUp).Offset(1).EntireRow
    
    For Each ws In wb.Worksheets
        If ws.name <> ReportSheet.name Then 'skip the report sheet
            LoadData ws, ReportRow
            Set ReportRow = ReportRow.Offset(1) 'next row for the report
        End If
    Next ws
End Sub

'Scan the worksheet `RawSheet` and transfer data to range `ReportRow` 
'   according to headers in Columns A and C
Sub LoadData(RawSheet As Worksheet, ReportRow As Range)
    Dim rw As Long, vC1, vC2, vC3, vC4, vC5, v
    Dim NegativeNumber, Average As Double
    
    For rw = 1 To RawSheet.cells(Rows.Count, 1).End(xlUp).Row 'to last occupied cell in col A
        
        vC1 = RawSheet.cells(rw, 1).Value 'read the values from the row *once*
        vC2 = RawSheet.cells(rw, 2).Value
        vC3 = RawSheet.cells(rw, 3).Value
        vC4 = RawSheet.cells(rw, 4).Value
        vC5 = RawSheet.cells(rw, 5).Value
        
        Select Case vC1 'check Col A headers...
            Case "Series Name": ReportRow.cells(5).Value = vC2
            Case "Series Date": ReportRow.cells(7).Value = vC2
            Case "Weight": ReportRow.cells(8).Value = vC2
            'etc etc
            Case "A'": ReportRow.cells(36).Value = -vC5 '<< negative value example
            'etc etc
        End Select
        
        Select Case vC3 '...then check Col C headers
            Case "Temperature": ReportRow.cells(9).Value = vC5
            Case "Heart Rate": ReportRow.cells(10).Value = vC5
            Case "LVAW;d": ReportRow.cells(11).Value = vC5
            Case "LVAW;s": ReportRow.cells(14).Value = vC5
            Case "LVPW;d": ReportRow.cells(13).Value = vC5
            'etc etc
        End Select
    Next rw
End Sub

If this, or a different response, answered your question, please flag as "Accepted" to help out anyone coming along later with a similar problem.

Tuhin Shaikh · Accepted Answer · 2025-11-10 16:34:15Z

0

You should use a For Each ws In ThisWorkbook.Worksheets loop, skip the report sheet, and call your existing logic inside it.

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then Call LoadDataFor(ws)
Next ws

answered Nov 10 at 16:34

Tuhin Shaikh

967 bronze badges

2 Comments

Allison Rodgers Nov 10 at 22:10

Where exactly do I add this loop? I can't seem to find a spot that works. I keep getting an error about "no for"

Tim Williams Nov 11 at 16:58

To use this you'd need to refactor your LoadData to accept an argument of type Worksheet, so you can pass in each source sheet in turn. The code above would be in a separate method like Sub LoadAllSheets() for example.

Collectives™ on Stack Overflow

Excel Macro Loop. Looking for code to loop through entire workbook

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related