API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 24th, 2017
87
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 26.57 KB | None | 0 0
raw download clone embed print report
  1. Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"
  2. Sheets("Main Tab").Range("I2:O" & LastRow).Value = Sheets("Main Tab").Range("I2:O" & LastRow).Value
  3.  
  4. Sub Clear_Click()
  5.  
  6. DisableOptimize
  7. UnfilterAll
  8. Application.Calculation = xlCalculationManual
  9. Application.EnableEvents = False
  10.  
  11. MainTabLastColum = "AU"
  12.  
  13.  
  14. Sheets("Order Upload").AutoFilterMode = False
  15. Sheets("Main Tab").AutoFilterMode = False
  16. Sheets("Microstrategy Data").AutoFilterMode = False
  17. Sheets("Velocity").AutoFilterMode = False
  18.  
  19. LastRow = Sheets("Order Upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  20. If LastRow > 1 Then
  21. Sheets("Order Upload").Range("A2:K" & LastRow).ClearContents
  22. End If
  23.  
  24. LastRow = Sheets("SKU-DC Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  25. If LastRow > 1 Then
  26. Sheets("SKU-DC Summary").Range("A2:S" & LastRow).ClearContents
  27. End If
  28.  
  29.  
  30.  
  31. LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  32. If LastRow > 1 Then
  33. Sheets("Main Tab").Range("A2:" & MainTabLastColum & LastRow).ClearContents
  34. End If
  35.  
  36. LastRow = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  37. If LastRow > 1 Then
  38. Sheets("Microstrategy Data").Range("A2:H" & LastRow).ClearContents
  39. End If
  40.  
  41. LastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  42. If LastRow > 1 Then
  43. Sheets("Velocity").Range("J2:N" & LastRow).ClearContents
  44. Sheets("Velocity").Range("V2:V" & LastRow).ClearContents
  45. End If
  46.  
  47.  
  48. MainTabLastColum = Null
  49. QuantityLastColumn = Null
  50. LastRow = Null
  51.  
  52. End Sub
  53. Sub LoadMicroData2()
  54.  
  55. Dim StartTime As Double
  56. Dim SecondsElapsed As Double
  57. StartTime = Timer
  58.  
  59.  
  60.  
  61.  
  62. DisableOptimize
  63.  
  64. LastRowMsCopyTo = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  65. LastRowMsCopyFrom = Sheets("Data Input Microstrategy").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  66.  
  67.  
  68. Call CopyValues(Sheets("Data Input Microstrategy").Range("A7:A" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("A2:A" & LastRowMsCopyFrom))
  69. Call CopyValues(Sheets("Data Input Microstrategy").Range("B7:B" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("B2:B" & LastRowMsCopyFrom))
  70. Call CopyValues(Sheets("Data Input Microstrategy").Range("C7:C" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("C2:C" & LastRowMsCopyFrom))
  71. Call CopyValues(Sheets("Data Input Microstrategy").Range("D7:D" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("E2:E" & LastRowMsCopyFrom))
  72. Call CopyValues(Sheets("Data Input Microstrategy").Range("E7:E" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("F2:F" & LastRowMsCopyFrom))
  73. Call CopyValues(Sheets("Data Input Microstrategy").Range("F7:F" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("G2:G" & LastRowMsCopyFrom))
  74. Call CopyValues(Sheets("Data Input Microstrategy").Range("G7:G" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("H2:H" & LastRowMsCopyFrom))
  75.  
  76. LastRowMsCopyTo = Sheets("Microstrategy Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  77.  
  78. Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyTo).Formula = "=Index('Data Input by Account'!$D:$D,match($A2,'Data Input by Account'!$A:$A,false))"
  79.  
  80. Worksheets("Microstrategy Data").UsedRange.Columns("D").Calculate
  81.  
  82. Call CopyValues(Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom), Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom))
  83. Call CopyValues(Sheets("Microstrategy Data").Range("A2:A" & LastRowMsCopyFrom), Sheets("Main Tab").Range("A2:A" & LastRowMsCopyFrom))
  84. Call CopyValues(Sheets("Microstrategy Data").Range("B2:B" & LastRowMsCopyFrom), Sheets("Main Tab").Range("B2:B" & LastRowMsCopyFrom))
  85. Call CopyValues(Sheets("Microstrategy Data").Range("C2:C" & LastRowMsCopyFrom), Sheets("Main Tab").Range("C2:C" & LastRowMsCopyFrom))
  86. Call CopyValues(Sheets("Microstrategy Data").Range("D2:D" & LastRowMsCopyFrom), Sheets("Main Tab").Range("D2:D" & LastRowMsCopyFrom))
  87. Call CopyValues(Sheets("Microstrategy Data").Range("E2:E" & LastRowMsCopyFrom), Sheets("Main Tab").Range("E2:E" & LastRowMsCopyFrom))
  88. Call CopyValues(Sheets("Microstrategy Data").Range("F2:F" & LastRowMsCopyFrom), Sheets("Main Tab").Range("F2:F" & LastRowMsCopyFrom))
  89. Call CopyValues(Sheets("Microstrategy Data").Range("G2:G" & LastRowMsCopyFrom), Sheets("Main Tab").Range("G2:G" & LastRowMsCopyFrom))
  90. Call CopyValues(Sheets("Microstrategy Data").Range("H2:H" & LastRowMsCopyFrom), Sheets("Main Tab").Range("H2:H" & LastRowMsCopyFrom))
  91. LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  92.  
  93.  
  94. Application.CutCopyMode = False
  95.  
  96. LastRowMsCopyTo = Null
  97. LastRowMsCopyFrom = Null
  98. LastRow = Null
  99.  
  100. End Sub
  101. Sub CopyValues(rngSource As Range, rngTarget As Range)
  102.  
  103. rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
  104.  
  105. End Sub
  106.  
  107. Sub Calculate_Click()
  108.  
  109.  
  110. MainTabLastColum = "AU"
  111. QuantityLastColumn = "O"
  112.  
  113. Worksheets("Main Tab").Select
  114.  
  115. DisableOptimize
  116.  
  117.  
  118. LastRowAvail = Sheets("Quantity Available").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  119.  
  120. Sheets("Quantity Available").Range("E2:E" & LastRowAvail).Formula = "=CONCATENATE(A2,B2)"
  121. Sheets("Quantity Available").Range("F2:F" & LastRowAvail).Formula = "=CONCATENATE(A2,Upper(B2),C2)"
  122.  
  123. Sheets("Quantity Available").Range("E2:F" & LastRowAvail).Value = Sheets("Quantity Available").Range("E2:F" & LastRowAvail).Value
  124.  
  125. ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Clear
  126. ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Add Key:=Range( _
  127. "C2:C" & LastRowAvail), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  128. xlSortNormal
  129.  
  130. ActiveWorkbook.Worksheets("Quantity Available").Sort.SortFields.Add Key:=Range( _
  131. "E2:E" & LastRowAvail), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  132. xlSortNormal
  133. With ActiveWorkbook.Worksheets("Quantity Available").Sort
  134. .SetRange Range("A1:F" & LastRowAvail)
  135. .Header = xlYes
  136. .MatchCase = False
  137. .Orientation = xlTopToBottom
  138. .Apply
  139. End With
  140.  
  141. LastRowData = Sheets("Data Input by Account").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  142.  
  143. Sheets("Data Input by Account").Range("C2:C" & LastRowData).Formula = "=VLOOKUP(B7,Table5,2,FALSE)"
  144. Sheets("Data Input by Account").Range("C2:C" & LastRowData).Value = Sheets("Data Input by Account").Range("C2:C" & LastRowData).Value
  145.  
  146.  
  147. ActiveWorkbook.Worksheets("Data Input by Account").Sort.SortFields.Clear
  148. ActiveWorkbook.Worksheets("Data Input by Account").Sort.SortFields.Add Key:=Range( _
  149. "A2:A" & LastRowData), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  150. xlSortNormal
  151. With ActiveWorkbook.Worksheets("Data Input by Account").Sort
  152. .SetRange Range("A1:D" & LastRowData)
  153. .Header = xlYes
  154. .MatchCase = False
  155. .Orientation = xlTopToBottom
  156. .Apply
  157. End With
  158.  
  159.  
  160.  
  161. VelocityLastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  162.  
  163. Sheets("Velocity").Range("J2:J" & VelocityLastRow).Formula = "=CONCATENATE(A2,D2,E2,I2)"
  164. Sheets("Velocity").Range("K2:K" & VelocityLastRow).Formula = "=F2"
  165. Sheets("Velocity").Range("L2:L" & VelocityLastRow).Formula = "=G2"
  166. Sheets("Velocity").Range("M2:M" & VelocityLastRow).Formula = "=H2"
  167. Sheets("Velocity").Range("N2:N" & VelocityLastRow).Formula = "=C2"
  168.  
  169.  
  170.  
  171. Sheets("Velocity").Range("J2:N" & VelocityLastRow).Value = Sheets("Velocity").Range("J2:N" & VelocityLastRow).Value
  172.  
  173.  
  174. ActiveWorkbook.Worksheets("Velocity").Sort.SortFields.Clear
  175. ActiveWorkbook.Worksheets("Velocity").Sort.SortFields.Add Key:=Range( _
  176. "J2:J" & VelocityLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  177. xlSortNormal
  178. With ActiveWorkbook.Worksheets("Velocity").Sort
  179. .SetRange Range("A1:N" & VelocityLastRow)
  180. .Header = xlYes
  181. .MatchCase = False
  182. .Orientation = xlTopToBottom
  183. .Apply
  184. End With
  185.  
  186. Sheets("Velocity").Range("V2:V" & VelocityLastRow).Formula = "=CONCATENATE(A2,I2)"
  187. Sheets("Velocity").Range("V2:V" & VelocityLastRow).Value = Sheets("Velocity").Range("V2:V" & VelocityLastRow).Value
  188.  
  189. LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  190.  
  191.  
  192. Sheets("Main Tab").Range("U2:U" & LastRow).Formula = "=CONCATENATE(E2,C2)"
  193.  
  194. Sheets("Main Tab").Range("U2:U" & LastRow).Value = Sheets("Main Tab").Range("U2:U" & LastRow).Value
  195.  
  196. Sheets("Main Tab").Range("T2:W" & LastRow).Value = Sheets("Main Tab").Range("T2:W" & LastRow).Value
  197.  
  198. ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Clear
  199. ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Add Key:=Range( _
  200. "U2:U" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  201. xlSortNormal
  202. With ActiveWorkbook.Worksheets("Main Tab").Sort
  203. .SetRange Range("A1:AU" & LastRow)
  204. .Header = xlYes
  205. .MatchCase = False
  206. .Orientation = xlTopToBottom
  207. .Apply
  208. End With
  209.  
  210.  
  211. Sheets("Main Tab").Range("I2:I" & LastRow).Formula = "=IF(H2<>0,F2/H2)"
  212. Sheets("Main Tab").Range("J2:J" & LastRow).Formula = "=IF(VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:K$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(U2,D2,CalculateWeek), VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:K$" & VelocityLastRow & ",2,TRUE), ""Missing"")"
  213. Sheets("Main Tab").Range("K2:K" & LastRow).Formula = "=IF(VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(U2,D2,CalculateWeek), VLOOKUP(CONCATENATE(U2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",5,TRUE), ""Missing"")"
  214. Sheets("Main Tab").Range("L2:L" & LastRow).Formula = "=IF(AND(K2<>0,I2>K2),ROUND((F2/K2)/J2,0.1),"""")"
  215.  
  216.  
  217.  
  218. Sheets("Main Tab").Range("M2:M" & LastRow).Formula = "=IF(AND(F2>0,L2<>""""),L2-H2,"""")"
  219.  
  220. Sheets("Main Tab").Range("N2:N" & LastRow).Formula = "=IF(AND(L2>0,L2<>"""",M2<>""""),FLOOR(M2/IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",3,TRUE), ""Missing""),1),"""")"
  221.  
  222.  
  223.  
  224.  
  225.  
  226. Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J$2:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"
  227.  
  228.  
  229. Sheets("Main Tab").Range("I2:O" & LastRow).Value = Sheets("Main Tab").Range("I2:O" & LastRow).Value
  230.  
  231. Sheets("Main Tab").Range("Z2:Z" & LastRow).Formula = "=ROUND((N2*K2),0)"
  232. Sheets("Main Tab").Range("Z2:Z" & LastRow).Value = Sheets("Main Tab").Range("Z2:Z" & LastRow).Value
  233.  
  234. ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Clear
  235. ActiveWorkbook.Worksheets("Main Tab").Sort.SortFields.Add Key:=Range( _
  236. "Z2:Z" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
  237. xlSortNormal
  238. With ActiveWorkbook.Worksheets("Main Tab").Sort
  239. .SetRange Range("A1:AU" & LastRow)
  240. .Header = xlYes
  241. .MatchCase = False
  242. .Orientation = xlTopToBottom
  243. .Apply
  244. End With
  245.  
  246.  
  247.  
  248. Sheets("Main Tab").Range("X2:X" & LastRow).Formula = "=INDEX('Quantity Available'!$D$2:$D$" & LastRowAvail & ",MATCH(U2&""LIBERTY"",'Quantity Available'!$F$2:$F$" & LastRowAvail & ",FALSE))"
  249. Sheets("Main Tab").Range("X2:X" & LastRow).Value = Sheets("Main Tab").Range("X2:X" & LastRow).Value
  250.  
  251. Dim i As Long
  252. Dim j As Long
  253.  
  254. Dim myval1 As Long
  255. Dim myval2 As Long
  256. Dim myval3 As Long
  257. Dim MyRange1 As Range
  258. Dim MyRange2 As Range
  259. Dim MyRange3 As Range
  260.  
  261.  
  262. For i = 2 To LastRow
  263. Set MyRange1 = Worksheets("Main Tab").Range("U1:U" & i)
  264. Set MyRange2 = Worksheets("Main Tab").Range("Z1:Z" & i)
  265. Set MyRange3 = Worksheets("Main Tab").Range("X" & i)
  266.  
  267. myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("U" & i).Value, MyRange2)
  268. myval2 = MyRange3
  269. myval3 = myval2 - myval1
  270.  
  271. Worksheets("Main Tab").Cells(i, 18).Value = myval3
  272. Next
  273.  
  274.  
  275. Sheets("Main Tab").Range("S2:S" & LastRow) = "LIBERTY"
  276.  
  277. Sheets("Main Tab").Range("AC2:AC" & LastRow).Formula = "=IF(R2<0,""C"","""")"
  278. Sheets("Main Tab").Range("AC2:AC" & LastRow).Value = Sheets("Main Tab").Range("AC2:AC" & LastRow).Value
  279.  
  280. Sheets("Main Tab").Range("AB2:AB" & LastRow).Formula = "=IF(AND(Z2>VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!$J$2:$N$" & VelocityLastRow & ",4), Z2<>""""),VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!$J$2:$N$" & VelocityLastRow & ",4),Z2)"
  281. Sheets("Main Tab").Range("AB2:AB" & LastRow).Value = Sheets("Main Tab").Range("AB2:AB" & LastRow).Value
  282.  
  283. Sheets("Main Tab").Range("AA2:AA" & LastRow).Formula = "=IF(AC2=""C"",0,AB2)"
  284. Sheets("Main Tab").Range("AA2:AA" & LastRow).Value = Sheets("Main Tab").Range("AA2:AA" & LastRow).Value
  285.  
  286. Dim myval4 As Long
  287. Dim MyRange4 As Range
  288. Dim MyRange5 As Range
  289.  
  290.  
  291. For j = 2 To 500 ' LastRow
  292. Set MyRange4 = Worksheets("Main Tab").Range("A2:A" & LastRow)
  293. Set MyRange5 = Worksheets("Main Tab").Range("AA2:AA" & LastRow)
  294. myval4 = Application.WorksheetFunction.SumIf(MyRange4, Range("A" & j).Value, MyRange5)
  295. Worksheets("Main Tab").Cells(j, 31).Value = myval4
  296.  
  297. Next
  298.  
  299.  
  300.  
  301. LastRowCorrection = Null
  302. LastRowAvail = Null
  303. VelocityLastRow = Null
  304. LastRowData = Null
  305. LastRow = Null
  306.  
  307. Application.ScreenUpdating = False
  308. Application.DisplayStatusBar = False
  309. Application.Calculation = xlCalculationManual
  310. Application.EnableEvents = False
  311. Application.CutCopyMode = False
  312.  
  313.  
  314. LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  315.  
  316.  
  317. Sheets("Main Tab").Range("AI2:AI" & LastRow).Formula = "=IF($E2="""","""",INDEX(Velocity!G:G,MATCH('Main Tab'!E2,Velocity!A:A,FALSE)))"
  318. Sheets("Main Tab").Range("AR2:AR" & LastRow).Formula = "=IF($F2=0,0,ROUND((((($F2/$K2)/$J2)-$H2)/AI2),0.1))"
  319. Sheets("Main Tab").Range("AH2:AH" & LastRow).Formula = "=IF($K2=0,0,IF($F2=0,0,ROUND((((($F2/$K2)/$J2)-$H2)/AI2)*$K2,0.1)))"
  320. Sheets("Main Tab").Range("AG2:AG" & LastRow).Formula = "=IF(AH2<0,0,AH2)"
  321. Sheets("Main Tab").Range("AK2:AK" & LastRow).Formula = "=IF($A2="""","""",1+CalculateWeek)"
  322. Sheets("Main Tab").Range("AL2:AL" & LastRow).Formula = "=IF(AK2="""","""",CONCATENATE($E2,AK2))"
  323. Sheets("Main Tab").Range("AM2:AM" & LastRow).Formula = "=INDEX(Velocity!C:C,MATCH('Main Tab'!AL2,Velocity!V:V,FALSE))"
  324. Sheets("Main Tab").Range("AN2:AN" & LastRow).Formula = "=IF(K2=0,0,IF(AM2=0,0,ROUND((((($F2/$K2)*AM2)-$F2)-($H2-$F2))/AI2,0.1)))"
  325. Sheets("Main Tab").Range("AO2:AO" & LastRow).Formula = "=IF(AN2="""","""",IF(AN2<0,0,AN2))"
  326. Sheets("Main Tab").Range("AP2:AP" & LastRow).Formula = "=IF(AO2="""","""",AO2-AG2)"
  327. Sheets("Main Tab").Range("AS2:AS" & LastRow).Formula = "=IF(K2<$AR$1,0,IF($AR2<0,"""",$AR2))"
  328. Sheets("Main Tab").Range("AF2:AF" & LastRow).Formula = "=IF(K2>$AR$1,AS2,MAX(AG2,AO2))"
  329. Sheets("Main Tab").Range("AU2:AU" & LastRow).Formula = "=IF(AE2<Parameters!$B$7,0,'Main Tab'!AA2)"
  330. Sheets("Main Tab").Range("AT2:AT" & LastRow).Formula = "=CONCATENATE(A2,V2,AU2)"
  331.  
  332.  
  333. Sheets("Main Tab").Range("AA2:AU" & LastRow).Value = Sheets("Main Tab").Range("AA2:AU" & LastRow).Value
  334.  
  335. Application.CutCopyMode = False
  336.  
  337. LastRowCorrection = Null
  338. LastRow = Null
  339. MainTabLastColum = Null
  340. QuantityLastColumn = Null
  341.  
  342.  
  343. End Sub
  344.  
  345. Sub GenDoc_Click()
  346.  
  347. DisableOptimize
  348.  
  349.  
  350. LastRow = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  351.  
  352.  
  353. With ActiveWorkbook.Worksheets("Main Tab")
  354.  
  355. .AutoFilterMode = False
  356.  
  357. With .Range("A1:AU1")
  358.  
  359. .AutoFilter
  360.  
  361. .AutoFilter Field:=29, Criteria1:="<>C"
  362.  
  363. .AutoFilter Field:=47, Criteria1:="<>0"
  364.  
  365.  
  366.  
  367. End With
  368.  
  369. End With
  370.  
  371.  
  372. Set rData = Sheets("Main Tab").Range("A2:A" & LastRow) 'change this to suit your needs
  373. Set rVis = rData.SpecialCells(xlCellTypeVisible)
  374. rVis.Copy
  375. Sheets("Order Upload").Select
  376.  
  377. Sheets("Order Upload").Range("A2").Select
  378. ActiveSheet.Paste
  379.  
  380. LastRowUpload = Sheets("Order Upload").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  381.  
  382. Set rData = Sheets("Main Tab").Range("B2:B" & LastRow) 'change this to suit your needs
  383. Set rVis = rData.SpecialCells(xlCellTypeVisible)
  384. rVis.Copy
  385. Sheets("Order Upload").Select
  386.  
  387. Sheets("Order Upload").Range("J2").Select
  388. ActiveSheet.Paste
  389.  
  390. Set rData = Sheets("Main Tab").Range("S2:S" & LastRow) 'change this to suit your needs
  391. Set rVis = rData.SpecialCells(xlCellTypeVisible)
  392. rVis.Copy
  393. Sheets("Order Upload").Select
  394.  
  395. Sheets("Order Upload").Range("B2").Select
  396. ActiveSheet.Paste
  397.  
  398. Set rData = Sheets("Main Tab").Range("E2:E" & LastRow) 'change this to suit your needs
  399. Set rVis = rData.SpecialCells(xlCellTypeVisible)
  400. rVis.Copy
  401. Sheets("Order Upload").Select
  402.  
  403. Sheets("Order Upload").Range("E2").Select
  404. ActiveSheet.Paste
  405.  
  406. Set rData = Sheets("Main Tab").Range("AU2:AU" & LastRow) 'change this to suit your needs
  407. Set rVis = rData.SpecialCells(xlCellTypeVisible)
  408. rVis.Copy
  409. Sheets("Order Upload").Select
  410.  
  411. Sheets("Order Upload").Range("F2").Select
  412. ActiveSheet.Paste
  413.  
  414. Sheets("AOS Info").Select
  415. LastRowAOS = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  416.  
  417. Sheets("Order Upload").Select
  418. LastRowOrder = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  419.  
  420.  
  421. VelocityLastRow = Sheets("Velocity").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  422.  
  423. Range("C2:C" & LastRowOrder).Formula = "=ShpDt"
  424. Range("D2:D" & LastRowOrder).Formula = "=PONumber"
  425. Range("G2:G" & LastRowOrder).Formula = "=VLOOKUP(E2,'AOS Info'!$D$2:$E$" & LastRowAOS & ",2,FALSE)"
  426. Range("H2:H" & LastRowOrder).Formula = "=VLOOKUP(E2,'Velocity'!$A$2:$G$" & VelocityLastRow & ",7,FALSE)"
  427. Range("I2:I" & LastRowOrder).Formula = "=H2*F2"
  428.  
  429. Sheets("Order Upload").Range("C2:D" & LastRowOrder).Value = Sheets("Order Upload").Range("C2:D" & LastRowOrder).Value
  430. Sheets("Order Upload").Range("G2:I" & LastRowOrder).Value = Sheets("Order Upload").Range("G2:I" & LastRowOrder).Value
  431.  
  432. Sheets("Order Upload").Range("K2:K" & LastRowOrder).Formula = "=IF(B2="""",LEFT(J2,1),B2)"
  433. Sheets("Order Upload").Range("K2:K" & LastRowOrder).Value = Sheets("Order Upload").Range("K2:K" & LastRowOrder).Value
  434.  
  435. ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Clear
  436. ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Add Key:=Range( _
  437. "A2:A" & LastRowOrder), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  438. xlSortNormal
  439. ActiveWorkbook.Worksheets("Order Upload").Sort.SortFields.Add Key:=Range( _
  440. "B2:B" & LastRowOrder), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  441. xlSortNormal
  442. With ActiveWorkbook.Worksheets("Order Upload").Sort
  443. .SetRange Range("A1:K" & LastRowOrder)
  444. .Header = xlYes
  445. .MatchCase = False
  446. .Orientation = xlTopToBottom
  447. .SortMethod = xlPinYin
  448. .Apply
  449. End With
  450.  
  451. LastRowCorrection = Null
  452. LastRowTransfer = Null
  453. LastRowMain = Null
  454. 'SecondsElapsed = Round(Timer - StartTime, 2)
  455. 'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  456. 'StartTime = 0
  457.  
  458.  
  459. 'Sheets("Main Tab").Select
  460. 'ActiveSheet.ShowAllData
  461.  
  462. End Sub
  463.  
  464. Sub DisableOptimize()
  465. 'Disable As Copying Reenable at end
  466. Application.ScreenUpdating = False
  467. Application.DisplayStatusBar = False
  468. Application.Calculation = xlCalculationManual
  469. Application.EnableEvents = False
  470. End Sub
  471. Sub EnableOptimize()
  472. 'Disable As Copying Reenable at end
  473. Application.ScreenUpdating = True
  474. Application.DisplayStatusBar = True
  475. Application.Calculation = xlCalculationAutomatic
  476. Application.EnableEvents = True
  477. Application.CutCopyMode = False
  478. End Sub
  479. Sub UnfilterAll()
  480. Dim ws As Worksheet
  481.  
  482. On Error Resume Next
  483. For Each ws In ThisWorkbook.Worksheets
  484. If ws.Visible Then ws.ShowAllData
  485. Next ws
  486. On Error GoTo 0
  487. End Sub
  488.  
  489.  
  490. Sub SkuDCSummary()
  491.  
  492. Worksheets("SKU-DC SUmmary").Select
  493. DisableOptimize
  494.  
  495. 'Dim StartTime As Double
  496. 'Dim SecondsElapsed As Double
  497. 'StartTime = Timer
  498.  
  499. Worksheets("Main Tab").AutoFilterMode = False
  500.  
  501.  
  502.  
  503. LastRowMain = Sheets("Main Tab").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  504.  
  505. Sheets("Main Tab").Range("E1:E" & LastRowMain).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("SKU-DC Summary").Range("B1"), Unique:=True
  506. Sheets("Main Tab").Range("U1:U" & LastRowMain).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("SKU-DC Summary").Range("O1"), Unique:=True
  507.  
  508. LastRowSKU = Sheets("SKU-DC Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  509. LastRowAOS = Sheets("AOS Info").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  510.  
  511.  
  512.  
  513. Sheets("SKU-DC Summary").Range("A2:A" & LastRowSKU).Formula = "=INDEX('Main Tab'!$C$2:$C$" & LastRowMain & ",MATCH(B2,'Main Tab'!$E$2:$E$" & LastRowMain & ",FALSE))"
  514.  
  515. Sheets("SKU-DC Summary").Range("C2:C" & LastRowSKU).Formula = "=INDEX('AOS Info'!$E$2:$E$" & LastRowAOS & ",MATCH(B2,'AOS Info'!$A$2:$A$" & LastRowAOS & ",FALSE))"
  516.  
  517. Sheets("SKU-DC Summary").Range("D2:D" & LastRowSKU).Formula = "=INDEX('AOS Info'!$B$2:$B$" & LastRowAOS & ",MATCH(B2,'AOS Info'!$A$2:$A$" & LastRowAOS & ",FALSE))"
  518.  
  519. Dim myval1 As Long
  520. Dim MyRange1 As Range
  521. Dim MyRange2 As Range
  522. 'Dim MyRange3 As Range
  523. 'Dim MyRange4 As Range
  524.  
  525. For j = 2 To LastRowSKU
  526. Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain)
  527. Set MyRange2 = Worksheets("Main Tab").Range("F2:F" & LastRowMain)
  528. 'Set MyRange3 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)
  529. 'Set MyRange4 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)
  530.  
  531. myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2)
  532. Worksheets("SKU-DC Summary").Cells(j, 5).Value = myval1
  533. Next
  534.  
  535.  
  536.  
  537. 'Sheets("SKU-DC Summary").Range("E2:E" & LastRowSKU).Formula = "=SUMIFS('Main Tab'!$F2:$F$" & LastRowMain & ",'Main Tab'!$C2:$C$" & LastRowMain & ",A2,'Main Tab'!$E2:$E$" & LastRowMain & ",B2)"
  538.  
  539. Sheets("SKU-DC Summary").Range("F2:F" & LastRowSKU).Formula = "=E2/COUNTIFS('Main Tab'!$E2:$E$" & LastRowMain & ",B2,'Main Tab'!$H2:$H$" & LastRowMain & ","">0"")"
  540.  
  541. Sheets("SKU-DC Summary").Range("A2:F" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("A2:F" & LastRowSKU).Value
  542.  
  543. Sheets("SKU-DC Summary").Range("G2:G" & LastRowSKU).Formula = "=COUNTIFS('Main Tab'!$C2:$C$" & LastRowMain & ",A2,'Main Tab'!$E2:$E$" & LastRowMain & ",B2,'Main Tab'!$H2:$H$" & LastRowMain & ","">0"")"
  544.  
  545. For j = 2 To LastRowSKU
  546. Set MyRange1 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)
  547. Set MyRange2 = Worksheets("Main Tab").Range("H2:H" & LastRowMain)
  548. 'Set MyRange3 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)
  549. 'Set MyRange4 = Worksheets("Main Tab").Range("E2:E" & LastRowMain)
  550.  
  551. myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("B" & j).Value, MyRange2)
  552. Worksheets("SKU-DC Summary").Cells(j, 8).Value = myval1 - Range("E" & j).Value
  553. Next
  554.  
  555.  
  556. 'Sheets("SKU-DC Summary").Range("H2:H" & LastRowSKU).Formula = "=SUMIF('Main Tab'!$E$2:$E$" & LastRowMain & ",B2,'Main Tab'!$H$2:$H$" & LastRowMain & ")-E2"
  557.  
  558. 'Sheets("SKU-DC Summary").Range("G2:H" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("G2:H" & LastRowSKU).Value
  559.  
  560. Sheets("SKU-DC Summary").Range("I2:I" & LastRowSKU).Formula = "=H2/G2"
  561.  
  562. Sheets("SKU-DC Summary").Range("J2:J" & LastRowSKU).Formula = "=E2/(H2+E2)"
  563.  
  564. Sheets("SKU-DC Summary").Range("I2:J" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("I2:J" & LastRowSKU).Value
  565.  
  566. 'Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Formula = "=B2 & A2"
  567. Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("O2:O" & LastRowSKU).Value
  568.  
  569. For j = 2 To LastRowSKU
  570. Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain)
  571. Set MyRange2 = Worksheets("Main Tab").Range("AU2:AU" & LastRowMain)
  572.  
  573. myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2)
  574. Worksheets("SKU-DC Summary").Cells(j, 11).Value = myval1
  575. Next
  576.  
  577.  
  578. For j = 2 To LastRowSKU
  579. Set MyRange1 = Worksheets("Main Tab").Range("U2:U" & LastRowMain)
  580. Set MyRange2 = Worksheets("Main Tab").Range("AU2:AU" & LastRowMain)
  581.  
  582. myval1 = Application.WorksheetFunction.SumIf(MyRange1, Range("O" & j).Value, MyRange2)
  583. Worksheets("SKU-DC Summary").Cells(j, 13).Value = myval1
  584. Next
  585.  
  586.  
  587. Sheets("SKU-DC Summary").Range("K2:O" & LastRowSKU).Value = Sheets("SKU-DC Summary").Range("K2:O" & LastRowSKU).Value
  588.  
  589.  
  590.  
  591. EnableOptimize
  592. LastRowCorrection = Null
  593. LastRowTransfer = Null
  594. LastRowMain = Null
  595. 'SecondsElapsed = Round(Timer - StartTime, 2)
  596. 'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  597. 'StartTime = 0
  598.  
  599. End Sub
  600. Sub RunAll()
  601.  
  602. Dim StartTime As Double
  603. Dim SecondsElapsed As Double
  604. StartTime = Timer
  605.  
  606. Call Clear_Click
  607. Call LoadMicroData2
  608. Call Calculate_Click
  609. Call GenDoc_Click
  610. Call SkuDCSummary
  611.  
  612. SecondsElapsed = Round(Timer - StartTime, 2)
  613. MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  614. StartTime = 0
  615.  
  616. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!