I got the output I was looking for -- not elegant and not programmer quality but functional for my needs.
Now to work on how I want to aggregate the data.
Hope this helps someone doing something similar.Example2.xls
Sub CycleThrough()
Dim c As Variant
Dim i, j As Integer
i = 1
j = 1
CheckIfSheetExists
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Output"
With Sheets("Output")
.Cells(i, 1).Value = "Name"
.Cells(i, 2).Value = "Date"
.Cells(i, 3).Value = "Contract"
.Cells(i, 4).Value = "Code"
.Cells(i, 5).Value = "Hours"
End With
For Each c In Worksheets("Entry").Range("C5:I12").Cells
If c.Value > 0 Then
i = i + 1
With Sheets("Output")
.Cells(i, j).Value = Sheets("Entry").Cells(1, 2).Value 'Name
.Cells(i, j + 1).Value = Sheets("Entry").Cells(4, c.Column).Value 'Date
.Cells(i, j + 2).Value = Sheets("Entry").Cells(c.Row, 1).Value 'Contract
.Cells(i, j + 3).Value = Sheets("Entry").Cells(c.Row, 2).Value 'Code
.Cells(i, j + 4).Value = Sheets("Entry").Cells(c.Row, c.Column).Value 'Hours
End With
'Debug.Print c.Value, c.Address, c.Column, c.Row
End If
Next
With ActiveSheet.Sort
.SortFields.Add Key:=Range("B1"), Order:=xlAscending
.SortFields.Add Key:=Range("C1"), Order:=xlAscending
.SetRange ActiveCell.CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Bookmarks