Solution
Here ‘Input’ refers to main sheet and the values from ‘Input’ is copied to ‘Output’.
sorting2 – macro function to sort
Shoppee_Run() – A new sheet is created on running macros, copy values from one another, erase the new sheet on every ‘Run’ of macros. Also it creates as a csv file.
Sub sorting2()
'
' sorting2 Macro
'
' fixed the sorting on verions 17
'
Range("B6").Select
ActiveWorkbook.Worksheets("Input").Sort.SortFields.Clear
' version 19 change to sort by createtime first
ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add2 Key:=Range("I:I"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add2 Key:=Range("A:A"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Input").Sort
.SetRange Range("A6:BB999")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub Shoppee_Run()
'
' Run Macro
'
'
'clean output worksheet
Sheets("Output").Select
ActiveSheet.Range("A2:AA55555").Select
Selection.Delete Shift:=xlUp
Selection.End(xlUp).Select
Sheets("Input").Select
'change to number on AI colume
ActiveSheet.Range("AI7:AI5000").Select
With Selection
Selection.NumberFormat = 0
.Value = .Value
End With
'change to number on Z colume 16042020
ActiveSheet.Range("Z7:Z5000").Select
With Selection
Selection.NumberFormat = 0
.Value = .Value
End With
'ENd
'End clean
sorting2
X = Sheets("Input").Range("A1").Value
' added the mapping fields 19/03/2020
S1 = Sheets("Input").Range("D1").Value
S2 = Sheets("Input").Range("e1").Value
S3 = Sheets("Input").Range("F1").Value
S4 = Sheets("Input").Range("G1").Value
S5 = Sheets("Input").Range("H1").Value
' End
A = 2
C = 0
D = 0
G = 0
N = 0
If X > 0 Then
'Copy Data
For I = 1 To X
LL = 0 'check delivery charge '1/8/2019
F = 7
Sheets("Output").Range("B" & A).Value = Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("C" & A).Value = "'" & Sheets("Input").Range("A2").Value
Sheets("Output").Range("D" & A).Value = S2
Sheets("Output").Range("E" & A).Value = "Custom"
'Sheets("Output").Range("F" & A).Value = Sheets("Input").Range("AJ" & I + 6).Value EITHER column input K or input M 09042020
If Len(Sheets("Input").Range("K" & I + 6).Value) = 0 Then
Sheets("Output").Range("F" & A).Value = Sheets("Input").Range("M" & I + 6).Value
Else
Sheets("Output").Range("F" & A).Value = Sheets("Input").Range("K" & I + 6).Value
End If
Sheets("Output").Range("G" & A).Value = S3
Sheets("Output").Range("I" & A).Value = Sheets("Input").Range("Q" & I + 6).Value
Sheets("Output").Range("J" & A).Value = "Shopee " & Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("K" & A).Value = S4
Sheets("Output").Range("L" & A).Value = "'" & Sheets("Input").Range("AS" & I + 6).Value
'Sheets("Output").Range("N" & A).Value = Sheets("Input").Range("AW" & I + 6).Value hardcode "SG" 16042020
'Sheets("Output").Range("N" & A).Value = "SG"
'Sheets("Output").Range("N" & A).Value = Sheets("Input").Range("AV" & I + 6).Value <leave it blank on ver 1.2 6/10/2021
Sheets("Output").Range("O" & A).Value = Sheets("Input").Range("AY" & I + 6).Value
Sheets("Output").Range("P" & A).Value = Sheets("Input").Range("AX" & I + 6).Value
Sheets("Output").Range("Q" & A).Value = "'" & Sheets("Input").Range("A2").Value
'version 2.1 leave blank
Sheets("Output").Range("R" & A).Value = ""
Sheets("Output").Range("S" & A).Value = Sheets("Input").Range("AQ" & I + 6).Value
Sheets("Output").Range("T" & A).Value = "'" & Sheets("Input").Range("AR" & I + 6).Value
Sheets("Output").Range("U" & A).Value = "Shopee " & Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("V" & A).Value = S5
' add the discount and rate 16042020
If Sheets("Input").Range("Z" & I + 6).Value > 0 Then
Sheets("Output").Range("W" & A).Value = "Sales Discount"
'Sheets("Output").Range("X" & A).Value = Round(-1 * Sheets("Input").Range("Z" & I + 6).Value / 1.07, 3) <removed from MY format 04/10/2021
'remove 1.07 for MY
Sheets("Output").Range("X" & A).Value = Round(-1 * Sheets("Input").Range("Z" & I + 6).Value, 3)
End If
Sheets("Output").Range("Z" & A).Value = Sheets("Input").Range("AB" & I + 6).Value
' End
'if same order no and same sku no on next line
If (Sheets("Input").Range("A" & I + 6).Value <> Sheets("Input").Range("A" & I + 7).Value) And (Sheets("Input").Range("M" & I + 6).Value <> Sheets("Input").Range("M" & I + 7).Value) Then
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("P" & I + 6).Value - Sheets("Input").Range("T" & I + 6).Value + Sheets("Input").Range("S" & I + 6).Value change 16042020
Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("R" & I + 6).Value
Else
'combine the pirce ,changed in 1/8/2019 if order no is same, then combine
K = 0
L = 0
M = Sheets("Input").Range("Q" & I + 6).Value
h = 0
'combine the unit price and delivery chagree for same sku and order number
For G = 1 To 10
' add &k 09042020
If (Sheets("Input").Range("A" & I + 6).Value = Sheets("Input").Range("A" & I + 6 + G).Value) And (Sheets("Input").Range("M" & I + 6).Value & Sheets("Input").Range("K" & I + 6).Value = Sheets("Input").Range("M" & I + 6 + G).Value & Sheets("Input").Range("K" & I + 6 + G).Value) Then
'h = h + Sheets("Input").Range("P" & I + 6).Value - Sheets("Input").Range("T" & I + 6).Value + Sheets("Input").Range("S" & I + 6).Value #16042020
h = h + Sheets("Input").Range("R" & I + 6).Value
L = L + Sheets("Input").Range("AI" & I + 6 + G).Value
K = K + 1
'M = M+1
' sum up the Qty
M = M + Sheets("Input").Range("Q" & I + 6 + G).Value
N = N + 1
If (Sheets("Input").Range("A" & I + 6).Value = Sheets("Input").Range("A" & I + 6 + G).Value) Then
End If
End If
Next G
'check the order no the delivery charge if same order number #28/8/2019 can added the K on 09042020
If (Sheets("Input").Range("M" & I + 6 + K).Value & Sheets("Input").Range("K" & I + 6 + K).Value <> Sheets("Input").Range("M" & I + 6 + K + 1).Value & Sheets("Input").Range("K" & I + 6 + K + 1).Value) Then
If (Sheets("Input").Range("A" & I + 6 + K).Value = Sheets("Input").Range("A" & I + 6 + K + 1).Value) Then
L = 0
End If
End If
'end
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("P" & I + 6).Value - Sheets("Input").Range("T" & I + 6).Value + Sheets("Input").Range("S" & I + 6).Value + h #16042020
Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("R" & I + 6).Value + h
'Sheets("Output").Range("I" & A).Value = Sheets("Input").Range("Q" & I + 6).Value
Sheets("Output").Range("I" & A).Value = M
' new line for combine delivery price
Sheets("input").Range("B2").Value = "=SUMIF(A7:A1000," & """" & Sheets("Input").Range("A" & I + 6).Value & """" & ",AI7:AI1000)"
totaldcharge = Sheets("input").Range("B2").Value
'If L > 0 Then 09042020
If L > 0 Then
A = A + 1
C = C + 1
'check upper order
Sheets("Output").Range("B" & A).Value = Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("C" & A).Value = "'" & Sheets("Input").Range("A2").Value
Sheets("Output").Range("D" & A).Value = S2
Sheets("Output").Range("E" & A).Value = "Custom"
'Sheets("Output").Range("F" & A).Value = Sheets("Input").Range("AJ" & I + 6).Value
Sheets("Output").Range("F" & A).Value = "Delivery"
Sheets("Output").Range("G" & A).Value = S3
'If Sheets("Output").Range("B" & A).Value = Sheets("Input").Range("J" & I + 5).Value Then
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AM" & I + 5).Value
'Else
'group the delivery free 09042020
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AN" & I + 6).Value + L
'Sheets("Output").Range("H" & A).Value = totaldcharge hardcode 1.49 on 19052020
'Sheets("Output").Range("H" & A).Value = 1.49
'change to MY format 4/10/21
'change to AI in verion 2.1
Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AI" & I + 6).Value
'End If
Sheets("Output").Range("I" & A).Value = "1"
Sheets("Output").Range("J" & A).Value = "Shopee " & Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("K" & A).Value = S4
Sheets("Output").Range("L" & A).Value = "'" & Sheets("Input").Range("AS" & I + 6).Value
'Sheets("Output").Range("N" & A).Value = Sheets("Input").Range("AW" & I + 6).Value harcode "SG" 16042020
'Sheets("Output").Range("N" & A).Value = "SG"
'Sheets("Output").Range("N" & A).Value = Sheets("Input").Range("AV" & I + 6).Value <leave it blank on ver 1.2 6/10/2021
Sheets("Output").Range("O" & A).Value = Sheets("Input").Range("AY" & I + 6).Value
Sheets("Output").Range("P" & A).Value = Sheets("Input").Range("X" & I + 6).Value
Sheets("Output").Range("Q" & A).Value = "'" & Sheets("Input").Range("A2").Value
Sheets("Output").Range("R" & A).Value = ""
Sheets("Output").Range("S" & A).Value = Sheets("Input").Range("AQ" & I + 6).Value
Sheets("Output").Range("T" & A).Value = "'" & Sheets("Input").Range("AR" & I + 6).Value
Sheets("Output").Range("U" & A).Value = "Shopee " & Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("V" & A).Value = S5
Sheets("Output").Range("Z" & A).Value = Sheets("Input").Range("AB" & I + 6).Value
' add the discount and rate 16042020
If Sheets("Input").Range("Z" & I + 6).Value > 0 Then
Sheets("Output").Range("W" & A).Value = "Sales Discount"
'Sheets("Output").Range("X" & A).Value = Round(-1 * Sheets("Input").Range("Z" & I + 6).Value / 1.07, 3) <This is for SG, remove from MY 4/10/21
'remove 1.07 for MY
Sheets("Output").Range("X" & A).Value = Round(-1 * Sheets("Input").Range("Z" & I + 6).Value, 3)
End If
' End
'add department for delviery
Sheets("Output").Range("Y" & A).Value = S2
'I = I + 1
End If
'combine the delivery charge if same order number ,1/8/2019
If (Sheets("Input").Range("A" & I + 6).Value = Sheets("Input").Range("A" & I + 7).Value) Then
L = Sheets("Input").Range("AN" & I + 6).Value
For G = 1 To 10
If (Sheets("Input").Range("I" & I + 6).Value = Sheets("Input").Range("I" & I + 6 + G).Value) Then
L = L & Sheets("Input").Range("AO" & I + 6 + G).Value
' hardcode 1.49 28/8/2019
L = 1.49
End If
Next G
Else
L = Sheets("Input").Range("AO" & I + 6).Value
LL = 1
End If
'End
End If
'add more colume if they buy 2 sku in the order but if same order no and same sku no on next line, not run this \ bug fix the same sku no for next line 03042020
'If (Sheets("Input").Range("M" & I + 6).Value <> Sheets("Input").Range("M" & I + 7).Value) And (Sheets("Input").Range("A" & I + 6).Value <> Sheets("Input").Range("A" & I + 7).Value) Then
If (Sheets("Input").Range("A" & I + 6).Value <> Sheets("Input").Range("A" & I + 7).Value) Then
'09042020
Sheets("input").Range("B2").Value = "=SUMIF(A7:A1000," & """" & Sheets("Input").Range("A" & I + 6).Value & """" & ",AI7:AI1000)"
totaldcharge = Sheets("input").Range("B2").Value
'If Sheets("Input").Range("AN" & I + 6).Value > 0 Then 09042020
If totaldcharge > 0 Then
A = A + 1
C = C + 1
'check upper order
Sheets("Output").Range("B" & A).Value = Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("C" & A).Value = "'" & Sheets("Input").Range("A2").Value
Sheets("Output").Range("D" & A).Value = S2
Sheets("Output").Range("E" & A).Value = "Custom"
'Sheets("Output").Range("F" & A).Value = Sheets("Input").Range("AJ" & I + 6).Value
Sheets("Output").Range("F" & A).Value = "Delivery"
Sheets("Output").Range("G" & A).Value = S3
'If Sheets("Output").Range("B" & A).Value = Sheets("Input").Range("J" & I + 5).Value Then
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AM" & I + 5).Value
' delivery charge if same order no 1/8/2019
' If totaldcharge > 0 Then
'Sheets("Output").Range("H" & A).Value = totaldcharge hardcode 1.49 on 19052020
'Sheets("Output").Range("H" & A).Value = 1.49
'change to AI in verion 2.1
Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AI" & I + 6).Value
'Else
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AN" & I + 6).Value
' End If
'Else
' delivery charge if same order no LL= 1 then run
'If LL = 1 Then
'Sheets("Output").Range("H" & A).Value = Sheets("Input").Range("AN" & I + 6).Value
'Else
'Sheets("Output").Range("H" & A).Value = L
'End If
'End If
Sheets("Output").Range("I" & A).Value = "1"
Sheets("Output").Range("J" & A).Value = "Shopee " & Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("K" & A).Value = S4
Sheets("Output").Range("L" & A).Value = "'" & Sheets("Input").Range("AS" & I + 6).Value
'Sheets("Output").Range("N" & A).Value = Sheets("Input").Range("AW" & I + 6).Value hardcode "SG" 16042020
'Sheets("Output").Range("N" & A).Value = Sheets("Input").Range("AV" & I + 6).Value <leave it blank on ver 1.2 6/10/2021
Sheets("Output").Range("O" & A).Value = Sheets("Input").Range("AY" & I + 6).Value
Sheets("Output").Range("P" & A).Value = Sheets("Input").Range("AX" & I + 6).Value
Sheets("Output").Range("Q" & A).Value = "'" & Sheets("Input").Range("A2").Value
Sheets("Output").Range("R" & A).Value = ""
Sheets("Output").Range("S" & A).Value = Sheets("Input").Range("AQ" & I + 6).Value
Sheets("Output").Range("T" & A).Value = "'" & Sheets("Input").Range("AR" & I + 6).Value
Sheets("Output").Range("U" & A).Value = "Shopee " & Sheets("Input").Range("A" & I + 6).Value
Sheets("Output").Range("V" & A).Value = S5
Sheets("Output").Range("Z" & A).Value = Sheets("Input").Range("AB" & I + 6).Value
'add department for delviery
Sheets("Output").Range("Y" & A).Value = S2
L = 0
End If
End If
If (Sheets("Input").Range("M" & I + 6).Value & Sheets("Input").Range("K" & I + 6).Value = Sheets("Input").Range("M" & I + 7).Value & Sheets("Input").Range("K" & I + 7).Value) And (Sheets("Input").Range("A" & I + 6).Value = Sheets("Input").Range("A" & I + 7).Value) Then
I = I + K
D = D + 1
End If
A = A + 1
F = F + 1
X = X + 1
Next I
' competed copy data
' order number
X = Sheets("Input").Range("A1").Value + C
B = 2
For I = 1 To X - N
If I = 1 Then
Sheets("Output").Range("A" & B).Value = 1
Else
If Sheets("Output").Range("J" & B).Value <> Sheets("Output").Range("J" & B - 1).Value Then
Sheets("Output").Range("A" & B).Value = Sheets("Output").Range("A" & B - 1).Value + 1
Else
Sheets("Output").Range("A" & B).Value = Sheets("Output").Range("A" & B - 1).Value
End If
End If
B = B + 1
Next I
' Competed order number
' replace the customer name
X = Sheets("Input").Range("A1").Value + C
C = 2
For I = 1 To X - N
Sheets("Output").Range("B" & C).Value = S1
C = C + 1
Next I
' Completed replace
'Copy to new file
Sheets("Input").Select
Sheets("input").Range("B2").Value = ""
Range("A6").Select
Sheets("Output").Select
Sheets("Output").Copy
MsgBox "Completed, Please Double check and save"
Windows("convert.xlsm").Activate
Sheets("Input").Select
Range("A6").Select
Else: MsgBox "NULL Record"
Sheets("Input").Select
Range("A6").Select
Exit Sub
End If
End Sub