Hello all,

I am struggling mightily with writing a Macro that grabs data including customer name, id, product name, product id, amount and the date and organizes them into a master sheet.

I know i must create a nested loop, but am struggling to compile anything that works effectively. Currently, it jumps from worksheet to worksheet and only grabs the top of ordered products and ignores the others.

The end process is supposed to cycle through all three invoices individually, move the necessary fields over to the master sheet and then move to the next invoice. Currently, its skimming the top of each. The way i have approached it is one massive For loop for all invoices and more conditional statements within. What i really need is a do while, however nothing ever compiles successfully. Currently i have been using If statements within the main for loop.

I have been working at this for hours and am only ending in frustration. I have been messing with all sorts of Variables, so some will not make any sense whatsoever. I want all of these If statements out as they are noneffective.

Sub Macro1()
'
' Macro1 Macro
'
    Dim CustomerName As String
    Dim CustomerID As Integer
    Dim ProductID As String
    Dim ProductName As String
    Dim Amount As Double
    Dim CurrDate As String
    Dim i As Integer
    Dim j As Integer
    Dim x As Integer
    Dim y As Integer
    Dim isEmpty As Boolean
    Dim wksht As Worksheet
    Dim w As Long
    Dim wkshtnames()
    w = 0
    i = 12
    j = 3
    x = 6
    y = 5
    For Each wksht In ActiveWorkbook.Worksheets
    w = w + 1
    ReDim Preserve wkshtnames(1 To w)
    wkshtnames(w) = wksht.Name
    If Worksheets(w).Cells(i, j).Value <> "" Then
    ProductID = Worksheets(w).Cells(i, j).Value
    Worksheets("Transaction List").Cells(x, y - 2) = ProductID
    End If
    If Worksheets(w).Cells(i + 1, j).Value <> "" Then
    ProductID = Worksheets(w).Cells(i, j).Value
    Worksheets("Transaction List").Cells(x + 1, y - 2) = ProductID
    End If
    If Worksheets(w).Cells(i + 2, j).Value <> "" Then
    ProductID = Worksheets(w).Cells(i, j).Value
    Worksheets("Transaction List").Cells(x + 2, y - 2) = ProductID
    End If
    If Worksheets(w).Cells(i + 3, j).Value <> "" Then
    ProductID = Worksheets(w).Cells(i, j).Value
    Worksheets("Transaction List").Cells(x + 3, y - 2) = ProductID
    End If
    If Worksheets(w).Cells(i + 4, j).Value <> "" Then
    ProductID = Worksheets(w).Cells(i, j).Value
    Worksheets("Transaction List").Cells(x + 4, y - 2) = ProductID
    End If
    CurrDate = Worksheets(w).Cells(2, 5).Value
    Worksheets("Transaction List").Cells(x, y - 3).Value = CurrDate
    ProductName = Worksheets(w).Cells(i, j + 1).Value
    Worksheets("Transaction List").Cells(x, y + 1).Value = ProductName
    CustomerName = Worksheets(w).Cells(8, 4).Value
    Worksheets("Transaction List").Cells(x, 4).Value = CustomerName
    x = x + 1
    
    
    Next
End Sub