Sub Copy_data_v3()
'copies raw data to new sheets horizontally
Dim FirstR As Long
Dim LastR As Long
Dim LRow As Long
Dim LCol As Long
Dim j3 As Long
Dim n As Long
Dim Temp_n As Long
'select first sheet
Worksheets(2).Select
LRow = Cells(Rows.Count, 1).End(xlUp).Row
LCol = 34
'Debug.Print LRow
'Debug.Print LCol
Temp_n = 1
'Loop to find rows of each strategy
For j3 = 1 To LRow
Range("C" & j3).Select
Selection.End(xlDown).Select
FirstR = ActiveCell.Row
Selection.End(xlDown).Select
LastR = ActiveCell.Row
'if to deal with one-line strategies
If Cells(LastR, 3).Value <> Cells(FirstR, 3).Value Then
LastR = FirstR
End If
'if to stop the code from scanning the whole sheet
If LastR > LRow Then
LastR = LRow
End If
'Debug.Print FirstR
'Debug.Print LastR
'for to check each line of a specific strategy
For n = FirstR To LastR
Cells(n, 1).Select
If Cells(n, 1).Value = "Purchase" Then
Cells(n, 3).Copy
Worksheets(3).Cells(n + 2, 2).PasteSpecial
Cells(n, 5).Copy
Worksheets(3).Cells(n + 2, 5).PasteSpecial
Cells(n, 7).Copy
Worksheets(3).Cells(n + 2, 6).PasteSpecial
Cells(n, 18).Copy
Worksheets(3).Cells(n + 2, 7).PasteSpecial
Cells(n, 13).Copy
Worksheets(3).Cells(n + 2, 8).PasteSpecial
Cells(n, 14).Copy
Worksheets(3).Cells(n + 2, 9).PasteSpecial
Cells(n, 8).Copy
Worksheets(3).Cells(n + 2, 10).PasteSpecial
Cells(n, 9).Copy
Worksheets(3).Cells(n + 2, 11).PasteSpecial
Cells(n, 10).Copy
Worksheets(3).Cells(n + 2, 12).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(n + 2, 13).PasteSpecial
Cells(n, 15).Copy
Worksheets(3).Cells(n + 2, 33).PasteSpecial
Cells(n, 16).Copy
Worksheets(3).Cells(n + 2, 34).PasteSpecial
Cells(n, 23).Copy
Worksheets(3).Cells(n + 2, 35).PasteSpecial
Temp_n = n
Else
If Cells(n, 1).Value = "Sale" Then
Cells(n, 3).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
Cells(n, 14).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 14).PasteSpecial
Cells(n, 5).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 15).PasteSpecial
Cells(n, 7).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 16).PasteSpecial
Cells(n, 13).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 17).PasteSpecial
Cells(n, 8).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 18).PasteSpecial
Cells(n, 9).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 19).PasteSpecial
Cells(n, 10).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 20).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 21).PasteSpecial
Cells(n, 23).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 36).PasteSpecial
If Cells(n + 1, 1).Value <> "Sale" Then
Temp_n = n
End If
Else
If Cells(n, 1).Value = "Adjustment" Then
Cells(n, 3).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 24).PasteSpecial
If Cells(n + 1, 1).Value <> "Adjustment" Then
Temp_n = n
End If
Else
If Cells(n, 1).Value = "Hed" Then
Cells(n, 3).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
Cells(n, 7).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 28).PasteSpecial
Cells(n, 8).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 29).PasteSpecial
Cells(n, 9).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 30).PasteSpecial
Cells(n, 10).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 31).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 32).PasteSpecial
If Cells(n + 1, 1).Value <> "Hed" Then
Temp_n = n
End If
Else
If Cells(n, 1).Value = "Cost" Then
If Cells(n, 5).Value = "Dem" Then
Cells(n, 3).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 23).PasteSpecial
Else
If Cells(n, 14).Value = "Sol" Then
Cells(n, 3).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 22).PasteSpecial
Else
Cells(n, 3).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 2).PasteSpecial
Cells(n, 5).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 25).PasteSpecial
Cells(n, 11).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 26).PasteSpecial
Cells(n, 14).Copy
Worksheets(3).Cells(FirstR + 1 + n - Temp_n, 27).PasteSpecial
End If
End If
If Cells(n + 1, 1).Value <> "Cost" Then
Temp_n = n
End If
End If
End If
End If
End If
End If
Next n
j3 = LastR
Next j3
End Sub