Hello everyone
I have a code that has nested loops for columns and rows in sheet1 and for each cell check if it is greater than zero .. if the value is greater than a zero so the related data to be transferred
In sheet1 I have fixed row (1) and fixed column (1) and in between the cells to be checked
For example the cell A2 has value greater than zero so the related data would be:
-----------------
B1 in Sheet1 >> Column C in Sheet2
A2 in Sheet1 >> Column I in Sheet2
The cell itself in B2 >> Column L in Sheet2
As for Sales ID in Sheet2 Would be sequence for Customers
You can run the working code to see the expected results
Sub Test()
Dim r1, r2, c, t, e
r1 = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
r2 = Sheets(2).Range("a" & Rows.Count).End(xlUp).Row + 1
Sheets(2).Range("A2:l" & r2) = ""
Application.ScreenUpdating = False
c = Sheets(1).Range("www1").End(xlToLeft).Column
For t = 2 To c
For e = 2 To r1
If Sheets(1).Cells(e, t) > 0 Then
r2 = Sheets(2).Range("a" & Rows.Count).End(xlUp).Row + 1
Sheets(2).Range("a" & r2) = t - 1
Sheets(2).Range("c" & r2) = Sheets(1).Cells(1, t).Value
Sheets(2).Range("i" & r2) = Sheets(1).Cells(e, 1).Value
Sheets(2).Range("l" & r2) = Sheets(1).Cells(e, t).Value
End If
Next
Next
Application.ScreenUpdating = True
End Sub
Hope it is clear
Bookmarks