Below the macro I used.
I don't get any error.
See the attached file.
Sub CONVERTROWSTOCOL_oeldere()
Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long
With Sheets("output")
.UsedRange.ClearContents
.Range("A1:H1").Value = Array("Cust SKU", "Desc", "Family", "Sub Family", "Cust Category", "date", "value", "year")
End With
rsht1 = Sheets("data").Range("A" & Rows.Count).End(xlUp).Row
rsht2 = Sheets("output").Range("A" & Rows.Count).End(xlUp).Row
col = 7
For i = 3 To rsht1
If InStr(1, Sheets("data").Range("A" & i).Value, "TOTAL") > 0 Then
Exit For
End If
Do While Sheets("data").Cells(2, col).Value <> "" 'And Sheets("data").Cells(i, col).Value <> ""
rsht2 = rsht2 + 1
Sheets("output").Range("A" & rsht2).Value = Sheets("data").Range("A" & i).Value
Sheets("output").Range("B" & rsht2).Value = Sheets("data").Range("B" & i).Value
Sheets("output").Range("C" & rsht2).Value = Sheets("data").Range("C" & i).Value
Sheets("output").Range("D" & rsht2).Value = Sheets("data").Range("D" & i).Value
Sheets("output").Range("E" & rsht2).Value = Sheets("data").Cells(i, col).Text
Sheets("output").Range("F" & rsht2).Value = Sheets("data").Cells(1, col).Value
Sheets("output").Range("G" & rsht2).Value = Sheets("data").Cells(i, col).Value
Sheets("output").Range("H" & rsht2).Value = "=YEAR(RC[-2])"
If Sheets("output").Range("E" & rsht2).Value = 0 Then
Sheets("output").Range("E" & rsht2).Value = " "
End If
col = col + 1
Loop
col = 7
Next
Columns("A:Z").EntireColumn.AutoFit
Range("A1").Select
End Sub
Bookmarks