API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 27th, 2019
177
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 22.82 KB | None | 0 0
raw download clone embed print report
  1. Sub LeasePriceModel20_Button1_Click()
  2.  
  3. ActiveSheet.Unprotect Password:="sherpadoc1"
  4. 'Align Colums
  5. Columns("A").ColumnWidth = 0.94
  6. Columns("B").ColumnWidth = 0.94
  7. Columns("C").ColumnWidth = 0.94
  8. Columns("D").ColumnWidth = 0.94
  9. Columns("E").ColumnWidth = 0.94
  10. Columns("F").ColumnWidth = 0.94
  11. Columns("G").ColumnWidth = 0.94
  12. Columns("H").ColumnWidth = 0.94
  13. Columns("I").ColumnWidth = 0.94
  14. Columns("J").ColumnWidth = 0.94
  15. Columns("K").ColumnWidth = 0.94
  16. Columns("L").ColumnWidth = 0.94
  17. Columns("M").ColumnWidth = 0.94
  18. Columns("N").ColumnWidth = 0.94
  19. Columns("O").ColumnWidth = 0.94
  20. Columns("P").ColumnWidth = 0.94
  21. Columns("Q").ColumnWidth = 0.94
  22. Columns("R").ColumnWidth = 0.94
  23. Columns("S").ColumnWidth = 0.94
  24. Columns("T").ColumnWidth = 0.94
  25. Columns("U").ColumnWidth = 0.94
  26. Columns("V").ColumnWidth = 0.94
  27. Columns("W").ColumnWidth = 0.94
  28. Columns("X").ColumnWidth = 0.94
  29. Columns("Y").ColumnWidth = 0.94
  30. Columns("Z").ColumnWidth = 0.94
  31. Columns("AA").ColumnWidth = 0.94
  32. Columns("AB").ColumnWidth = 0.94
  33. Columns("AC").ColumnWidth = 0.94
  34. Columns("AD").ColumnWidth = 4.33
  35. Columns("AE").ColumnWidth = 0.94
  36. Columns("AF").ColumnWidth = 0.94
  37. Columns("AG").ColumnWidth = 0.94
  38. Columns("AH").ColumnWidth = 0.94
  39. Columns("AI").ColumnWidth = 0.94
  40. Columns("AJ").ColumnWidth = 0.94
  41. Columns("AK").ColumnWidth = 0.94
  42. Columns("AL").ColumnWidth = 0.94
  43. Columns("AM").ColumnWidth = 0.94
  44. Columns("AN").ColumnWidth = 0.94
  45. Columns("AO").ColumnWidth = 0.94
  46. Columns("AP").ColumnWidth = 0.94
  47. Columns("AQ").ColumnWidth = 0.94
  48. Columns("AR").ColumnWidth = 0.94
  49. Columns("AS").ColumnWidth = 0.94
  50. Columns("AT").ColumnWidth = 0.94
  51. Columns("AU").ColumnWidth = 0.94
  52. Columns("AV").ColumnWidth = 0.94
  53. Columns("AW").ColumnWidth = 0.94
  54. Columns("AX").ColumnWidth = 0.94
  55. Columns("AY").ColumnWidth = 0.94
  56. Columns("AZ").ColumnWidth = 0.94
  57. Columns("BA").ColumnWidth = 0.94
  58. Columns("BB").ColumnWidth = 0.94
  59. Columns("BC").ColumnWidth = 0.94
  60. Columns("BD").ColumnWidth = 0.94
  61. Columns("BE").ColumnWidth = 0.94
  62. Columns("BF").ColumnWidth = 0.94
  63. Columns("BG").ColumnWidth = 0.94
  64.  
  65. 'Font Settings
  66. Range(Cells(14, 1), Cells(moduleStart + 40, 58)).Font.Size = 11
  67. Range(Cells(14, 1), Cells(moduleStart + 40, 58)).Font.Name = "Times New Roman"
  68. 'Air % font
  69. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 34)).Font.Size = 8
  70.  
  71. Cells(moduleStart + 4, 18).Value = "Delivery Charge"
  72. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 14, 18)).Font.Size = 10
  73. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 14, 18)).Font.Name = "Arial"
  74.  
  75. accountingFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""??_-;_-@_-"
  76. moduleStart = 16
  77. standardSheets = 15
  78. mySheets = Worksheets.Count
  79. leaseSheet = "Lease Price Model 2.0"
  80. leaseRate = Sheets(3).Cells(13, 4).Value
  81.  
  82. For i = standardSheets To mySheets
  83.  
  84.     'Align Rows
  85.    Rows(moduleStart).RowHeight = 12
  86.     Rows(moduleStart + 1).RowHeight = 6.6
  87.    
  88.     'Thick Side Border
  89.    With Worksheets(leaseSheet).Range(Cells(moduleStart, 58), Cells(moduleStart, 58)).Borders(xlEdgeRight)
  90.        .LineStyle = xlContinuous
  91.        .Weight = xlMedium
  92.     End With
  93.      
  94.     With Worksheets(leaseSheet).Range(Cells(moduleStart - 1, 58), Cells(moduleStart - 1, 58)).Borders(xlEdgeRight)
  95.        .LineStyle = xlContinuous
  96.        .Weight = xlMedium
  97.     End With
  98.    
  99.     Range(Cells(moduleStart, 2), Cells(moduleStart, 13)).Merge
  100.     Range(Cells(moduleStart, 2), Cells(moduleStart, 13)).NumberFormat = accountingFormat
  101.     Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).Merge
  102.     Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).NumberFormat = accountingFormat
  103.     Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).Merge
  104.     Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).NumberFormat = accountingFormat
  105.     Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).Merge
  106.    
  107.     'Mapp Price
  108.    Cells(moduleStart, 30).Value = mappPrice
  109.    
  110.    
  111.     'Get mainframe name
  112.    Cells(moduleStart, 2).Value = Sheets(i).Cells(16, 2).Value
  113.    
  114.    
  115.     moduleStart = moduleStart + 2
  116.    
  117. Next i
  118.    
  119.    
  120. 'Underline
  121. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 15), Cells(moduleStart - 2, 24)).Borders(xlEdgeBottom)
  122.     .LineStyle = xlContinuous
  123.     .Weight = xlThin
  124. End With
  125.  
  126. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 30), Cells(moduleStart - 2, 39)).Borders(xlEdgeBottom)
  127.     .LineStyle = xlContinuous
  128.     .Weight = xlThin
  129. End With
  130.  
  131. With Worksheets(leaseSheet).Range(Cells(moduleStart - 2, 45), Cells(moduleStart - 2, 54)).Borders(xlEdgeBottom)
  132.     .LineStyle = xlContinuous
  133.     .Weight = xlThin
  134. End With
  135.  
  136.  
  137. 'Align Footer Row above totals
  138. Rows(moduleStart - 1).RowHeight = 6.6
  139.  
  140. 'Merging totals
  141. Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).Merge
  142. Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).Merge
  143. Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).Merge
  144.  
  145. 'Borders for totals
  146. Range(Cells(moduleStart, 15), Cells(moduleStart, 24)).BorderAround ColorIndex:=1, Weight:=xlThin
  147. Range(Cells(moduleStart, 30), Cells(moduleStart, 39)).BorderAround ColorIndex:=1, Weight:=xlThin
  148. Range(Cells(moduleStart, 45), Cells(moduleStart, 54)).BorderAround ColorIndex:=1, Weight:=xlThin
  149.  
  150. 'Add Footer
  151. Cells(moduleStart, 13).Value = "Totals"
  152. Cells(moduleStart, 13).Font.Bold = True
  153. Range(Cells(moduleStart, 13), Cells(moduleStart, 13)).HorizontalAlignment = xlRight
  154.  
  155. 'Equipment Sub Total
  156. Cells(moduleStart + 2, 13).Value = "Equipment Sub-Total"
  157. Cells(moduleStart + 2, 13).Font.Bold = True
  158. Range(Cells(moduleStart + 2, 13), Cells(moduleStart + 2, 13)).HorizontalAlignment = xlRight
  159. Range(Cells(moduleStart + 2, 26), Cells(moduleStart + 2, 35)).Merge
  160. Range(Cells(moduleStart + 2, 26), Cells(moduleStart + 2, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  161. Range(Cells(moduleStart + 2, 46), Cells(moduleStart + 2, 55)).Merge
  162. Range(Cells(moduleStart + 2, 46), Cells(moduleStart + 2, 55)).BorderAround ColorIndex:=1, Weight:=xlThin
  163. eqpSubFormula = "=O" + CStr(moduleStart)
  164. Cells(moduleStart + 2, 26).Formula = eqpSubFormula
  165.  
  166. 'Delivery Charge
  167. Cells(moduleStart + 4, 18).Value = "Delivery Charge"
  168. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 4, 35)).Merge
  169. Cells(moduleStart + 4, 27).Value = 0
  170. Range(Cells(moduleStart + 4, 18), Cells(moduleStart + 4, 18)).HorizontalAlignment = xlRight
  171.  
  172. 'Removal Charge
  173. Cells(moduleStart + 5, 18).Value = "Removal Charge"
  174. Range(Cells(moduleStart + 5, 18), Cells(moduleStart + 5, 18)).HorizontalAlignment = xlRight
  175. Range(Cells(moduleStart + 5, 27), Cells(moduleStart + 5, 35)).Merge
  176. Cells(moduleStart + 5, 27).Value = 0
  177. Range(Cells(moduleStart + 5, 27), Cells(moduleStart + 5, 35)).Interior.ColorIndex = 6
  178.  
  179. 'Service Allocation
  180. Cells(moduleStart + 6, 18).Value = "Service Allocation"
  181. Range(Cells(moduleStart + 6, 18), Cells(moduleStart + 6, 18)).HorizontalAlignment = xlRight
  182. Range(Cells(moduleStart + 6, 27), Cells(moduleStart + 6, 35)).Merge
  183. Cells(moduleStart + 6, 27).Value = 0
  184. Range(Cells(moduleStart + 6, 27), Cells(moduleStart + 6, 35)).Interior.ColorIndex = 6
  185.  
  186. 'Marketing Promotion
  187. Cells(moduleStart + 7, 18).Value = "Marketing Promotion"
  188. Range(Cells(moduleStart + 7, 18), Cells(moduleStart + 7, 18)).HorizontalAlignment = xlRight
  189. Range(Cells(moduleStart + 7, 27), Cells(moduleStart + 7, 35)).Merge
  190. Cells(moduleStart + 7, 27).Value = 0
  191. Range(Cells(moduleStart + 7, 27), Cells(moduleStart + 7, 35)).Interior.ColorIndex = 6
  192.  
  193. 'Trade-In Amount (Discount)
  194. Cells(moduleStart + 8, 18).Value = "Trade-In Amount (Discount)"
  195. Range(Cells(moduleStart + 8, 18), Cells(moduleStart + 8, 18)).HorizontalAlignment = xlRight
  196. Range(Cells(moduleStart + 8, 27), Cells(moduleStart + 8, 35)).Merge
  197. Cells(moduleStart + 8, 27).Value = 0
  198. Range(Cells(moduleStart + 8, 27), Cells(moduleStart + 8, 35)).Interior.ColorIndex = 6
  199.  
  200. 'Provincial Environmental Levy
  201. Cells(moduleStart + 9, 18).Value = "Provincial Environmental Levy"
  202. Range(Cells(moduleStart + 9, 18), Cells(moduleStart + 9, 18)).HorizontalAlignment = xlRight
  203. Range(Cells(moduleStart + 9, 27), Cells(moduleStart + 9, 35)).Merge
  204. Cells(moduleStart + 9, 27).Value = 0
  205.  
  206. 'Net Equipment Value
  207. Range(Cells(moduleStart + 10, 27), Cells(moduleStart + 10, 35)).Merge
  208. Cells(moduleStart + 10, 18).Value = "Net Equipment Value"
  209. Cells(moduleStart + 10, 18).Font.Bold = True
  210. Range(Cells(moduleStart + 10, 27), Cells(moduleStart + 10, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  211. Range(Cells(moduleStart + 10, 18), Cells(moduleStart + 10, 18)).HorizontalAlignment = xlRight
  212.  
  213. 'Settlement Amount
  214. Cells(moduleStart + 11, 18).Value = "Settlement Amount"
  215. Range(Cells(moduleStart + 11, 18), Cells(moduleStart + 11, 18)).HorizontalAlignment = xlRight
  216. Range(Cells(moduleStart + 11, 27), Cells(moduleStart + 11, 35)).Merge
  217.  
  218. 'Discretionary Items
  219. Cells(moduleStart + 12, 18).Value = "Discretionary Items"
  220. Range(Cells(moduleStart + 12, 18), Cells(moduleStart + 12, 18)).HorizontalAlignment = xlRight
  221. Range(Cells(moduleStart + 12, 27), Cells(moduleStart + 12, 35)).Merge
  222. Cells(moduleStart + 12, 27).Value = 0
  223. Range(Cells(moduleStart + 12, 27), Cells(moduleStart + 12, 35)).Interior.ColorIndex = 6
  224.  
  225. 'Invoice Price
  226. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 35)).Merge
  227. Cells(moduleStart + 14, 18).Value = "Invoice Price"
  228. Cells(moduleStart + 14, 18).Font.Bold = True
  229. Range(Cells(moduleStart + 14, 18), Cells(moduleStart + 14, 18)).HorizontalAlignment = xlRight
  230. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 35)).BorderAround ColorIndex:=1, Weight:=xlThin
  231.  
  232. 'Lease Pricing Model (Ricoh Non-Note)
  233. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).Merge
  234. Cells(moduleStart + 16, 1).Value = "B) Lease Pricing Model (Ricoh Non-Note)"
  235. Cells(moduleStart + 16, 1).Font.Bold = True
  236. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).Interior.ColorIndex = 6
  237. Range(Cells(moduleStart + 16, 1), Cells(moduleStart + 16, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  238.  
  239. 'Please enter amounts in green boxes
  240. Cells(moduleStart + 17, 2).Value = "Please enter amounts in green boxes"
  241.  
  242. 'Equipment Value
  243. Cells(moduleStart + 18, 22).Value = "Equipment Value"
  244.  
  245. 'New Equipment
  246. Cells(moduleStart + 19, 2).Value = "New Equipment"
  247. Range(Cells(moduleStart + 19, 20), Cells(moduleStart + 19, 32)).Merge
  248. Range(Cells(moduleStart + 19, 20), Cells(moduleStart + 19, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  249.  
  250. 'Used/Refinance Equipment
  251. Cells(moduleStart + 21, 2).Value = "Used/Refinance Equipment"
  252. Range(Cells(moduleStart + 21, 20), Cells(moduleStart + 21, 32)).Merge
  253. Range(Cells(moduleStart + 21, 20), Cells(moduleStart + 21, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  254.  
  255. 'Settlement
  256. Cells(moduleStart + 23, 2).Value = "Settlement"
  257. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 32)).Merge
  258. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  259.  
  260. 'Soft Costs
  261. Cells(moduleStart + 25, 2).Value = "Soft Costs"
  262. Range(Cells(moduleStart + 25, 20), Cells(moduleStart + 25, 32)).Merge
  263. Range(Cells(moduleStart + 25, 20), Cells(moduleStart + 25, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  264.  
  265. 'Air
  266. Cells(moduleStart + 18, 34).Value = "% Air"
  267.  
  268. 'New Equipment Rate
  269. Cells(moduleStart + 18, 40).Value = "Rate"
  270. Range(Cells(moduleStart + 19, 40), Cells(moduleStart + 19, 44)).Merge
  271. Range(Cells(moduleStart + 19, 40), Cells(moduleStart + 19, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  272. Cells(moduleStart + 19, 40).Value = leaseRate
  273.  
  274. 'Used/Refinance Rate
  275. Range(Cells(moduleStart + 21, 40), Cells(moduleStart + 21, 44)).Merge
  276. Range(Cells(moduleStart + 21, 40), Cells(moduleStart + 21, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  277. Cells(moduleStart + 21, 40).Value = leaseRate
  278.  
  279. 'Settlement Rate
  280. Range(Cells(moduleStart + 23, 40), Cells(moduleStart + 23, 44)).Merge
  281. Range(Cells(moduleStart + 23, 40), Cells(moduleStart + 23, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  282. Cells(moduleStart + 23, 40).Value = leaseRate
  283.  
  284. ' % of Air
  285. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 36)).Merge
  286. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 36)).BorderAround ColorIndex:=1, Weight:=xlThin
  287.  
  288. 'Soft Costs Rate
  289. Range(Cells(moduleStart + 25, 40), Cells(moduleStart + 25, 44)).Merge
  290. Range(Cells(moduleStart + 25, 40), Cells(moduleStart + 25, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  291. Cells(moduleStart + 25, 40).Value = leaseRate
  292.  
  293. 'Lease Payment
  294. Cells(moduleStart + 18, 49).Value = "Lease Payment"
  295. lpFormula = "=T"
  296. lpFormula = lpFormula + CStr(moduleStart + 19)
  297. lpFormula = lpFormula + "*"
  298. lpFormula = lpFormula + "AN"
  299. lpFormula = lpFormula + CStr(moduleStart + 19)
  300. Cells(moduleStart + 19, 49).Formula = lpFormula
  301. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 49)).NumberFormat = "$0.00"
  302.  
  303. 'New Eqp Lease Payment
  304. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 56)).Merge
  305. Range(Cells(moduleStart + 19, 49), Cells(moduleStart + 19, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  306.  
  307. 'Used / Refinanced EQP Lease payment
  308. usedFormula = "=T"
  309. usedFormula = usedFormula + CStr(moduleStart + 21)
  310. usedFormula = usedFormula + "*"
  311. usedFormula = usedFormula + "AN"
  312. usedFormula = usedFormula + CStr(moduleStart + 21)
  313. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 56)).Merge
  314. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  315. Cells(moduleStart + 21, 49).Formula = usedFormula
  316. Range(Cells(moduleStart + 21, 49), Cells(moduleStart + 21, 49)).NumberFormat = "$0.00"
  317.  
  318.  
  319. 'Settlement Lease Payment
  320. slpFormula = "=T"
  321. slpFormula = slpFormula + CStr(moduleStart + 23)
  322. slpFormula = slpFormula + "*"
  323. slpFormula = slpFormula + "AN"
  324. slpFormula = slpFormula + CStr(moduleStart + 23)
  325. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 56)).Merge
  326. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  327. Cells(moduleStart + 23, 49).Formula = slpFormula
  328. Range(Cells(moduleStart + 23, 49), Cells(moduleStart + 23, 49)).NumberFormat = "$0.00"
  329.  
  330. 'Soft Costs Lease Payment
  331. sclpFormula = "=T"
  332. sclpFormula = sclpFormula + CStr(moduleStart + 25)
  333. sclpFormula = sclpFormula + "*"
  334. sclpFormula = sclpFormula + "AN"
  335. sclpFormula = sclpFormula + CStr(moduleStart + 25)
  336. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 56)).Merge
  337. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  338. Cells(moduleStart + 25, 49).Formula = sclpFormula
  339. Range(Cells(moduleStart + 25, 49), Cells(moduleStart + 25, 49)).NumberFormat = "$0.00"
  340.  
  341. 'Rate Total
  342. Range(Cells(moduleStart + 31, 40), Cells(moduleStart + 31, 44)).Merge
  343. Range(Cells(moduleStart + 31, 40), Cells(moduleStart + 31, 44)).BorderAround ColorIndex:=1, Weight:=xlThin
  344.  
  345. 'Lease Payment
  346. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 56)).Merge
  347. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 56)).BorderAround ColorIndex:=1, Weight:=xlThin
  348.  
  349. 'Total
  350. Cells(moduleStart + 27, 2).Value = "Total"
  351. Range(Cells(moduleStart + 27, 20), Cells(moduleStart + 27, 32)).Merge
  352. Range(Cells(moduleStart + 27, 20), Cells(moduleStart + 27, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  353.  
  354. 'Total Amount Financed
  355. TAFormula = "=T"
  356. TAFormula = TAFormula + CStr(moduleStart + 31)
  357. TAFormula = TAFormula + "*"
  358. TAFormula = TAFormula + "AN"
  359. TAFormula = TAFormula + CStr(moduleStart + 31)
  360. Cells(moduleStart + 31, 2).Value = "Total Amount Financed"
  361. Cells(moduleStart + 31, 40).Value = leaseRate
  362. Range(Cells(moduleStart + 31, 20), Cells(moduleStart + 31, 32)).Merge
  363. Range(Cells(moduleStart + 31, 20), Cells(moduleStart + 31, 32)).BorderAround ColorIndex:=1, Weight:=xlThin
  364. Cells(moduleStart + 31, 49).Formula = TAFormula
  365. Range(Cells(moduleStart + 31, 49), Cells(moduleStart + 31, 49)).NumberFormat = "$0.00"
  366.  
  367. 'Misc
  368. Cells(moduleStart + 35, 58).Value = "*Please indicate any other pertinent information necessary"
  369. Cells(moduleStart + 35, 58).HorizontalAlignment = xlRight
  370. Range(Cells(moduleStart + 35, 1), Cells(moduleStart + 35, 58)).Interior.ColorIndex = 15
  371. Cells(moduleStart + 35, 1).Value = "E) Miscellaneous"
  372. Cells(moduleStart + 35, 1).Font.Bold = True
  373. Cells(moduleStart + 35, 2).HorizontalAlignment = xlLeft
  374. Range(Cells(moduleStart + 35, 1), Cells(moduleStart + 35, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  375. Rows(moduleStart + 36).RowHeight = 6.6
  376.  
  377. 'Misc Section
  378. Range(Cells(moduleStart + 37, 1), Cells(moduleStart + 39, 58)).Merge
  379. Range(Cells(moduleStart + 37, 1), Cells(moduleStart + 39, 58)).BorderAround ColorIndex:=1, Weight:=xlThin
  380. Rows(moduleStart + 40).RowHeight = 6.6
  381.  
  382.  
  383. 'Footer thick side border
  384.    With Worksheets(leaseSheet).Range(Cells(moduleStart - 1, 58), Cells(moduleStart + 40, 58)).Borders(xlEdgeRight)
  385.        .LineStyle = xlContinuous
  386.        .Weight = xlMedium
  387.     End With
  388.  
  389.     With Worksheets(leaseSheet).Range(Cells(moduleStart + 40, 1), Cells(moduleStart + 40, 58)).Borders(xlEdgeBottom)
  390.        .LineStyle = xlContinuous
  391.        .Weight = xlMedium
  392.     End With
  393.  
  394.  
  395. ' //////////////////////////////
  396. ' //Fill Movement Forms Script//
  397. ' //////////////////////////////
  398.  
  399.  
  400. currentWs = 15
  401. currentItem = 16
  402. lpmItem = 16
  403. standardSheetNumber = 15
  404. mySheets = Worksheets.Count
  405. firstProduct = 0
  406. StandardRowsPCList = 15
  407. StandardRowsInSheet = 15
  408. equipmentSheet = 2
  409. productListEnd = Sheets(2).Range("E16").End(xlDown).Row - StandardRowsPCList
  410. itemsInSheet = 0
  411. filledItems = 0
  412. lpmSheet = 6
  413.  
  414. For i = standardSheetNumber To mySheets
  415.    
  416.     Sheets(currentWs).Activate
  417.     itemsInSheet = Sheets(currentWs).Range("A16").End(xlDown).Row - StandardRowsInSheet
  418.    
  419.     For j = 0 To productListEnd - 1
  420.    
  421.         ProductCode = Sheets(equipmentSheet).Cells(currentItem + j, 5).Value
  422.         productQty = Sheets(equipmentSheet).Cells(currentItem + j, 1).Value
  423.         productCost = Sheets(equipmentSheet).Cells(currentItem + j, 10).Value
  424.         mappCost = Sheets(equipmentSheet).Cells(currentItem + j, 12).Value
  425.        
  426.         For k = firstProduct To itemsInSheet
  427.             If Sheets(currentWs).Cells(currentItem + k, 4).Value <> 1 Then
  428.                 pcToCheck = Sheets(currentWs).Cells(currentItem + k, 1).Value
  429.  
  430.                 If pcToCheck = ProductCode Then
  431.                     Sheets(currentWs).Cells(currentItem + k, 6).Value = productCost
  432.                     Sheets(currentWs).Cells(currentItem + k, 7).Value = mappCost
  433.                     Sheets(currentWs).Cells(currentItem + k, 4).Value = Sheets(currentWs).Cells(currentItem + k, 4).Value + 1
  434.                     Exit For
  435.                 End If
  436.            
  437.             End If
  438.         Next k
  439.        
  440.         filledItems = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 4), Cells(16 + itemsInSheet, 4)))
  441.        
  442.         If filledItems = itemsInSheet Then
  443.             totalPrice = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 6), Cells(16 + itemsInSheet, 6)))
  444.             totalMapp = WorksheetFunction.Sum(Sheets(currentWs).Range(Cells(16, 7), Cells(16 + itemsInSheet, 7)))
  445.            
  446.             'Totals
  447.            Sheets(lpmSheet).Activate
  448.             sellTotal = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(16, 15), Cells(16 + (mySheets - standardSheetNumber), 15)))
  449.             mappTotal = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(16, 30), Cells(16 + (mySheets - standardSheetNumber), 30)))
  450.             diffTotal = WorksheetFunction.Sum(Sheets(lpmSheet).Range(Cells(16, 45), Cells(16 + (mySheets - standardSheetNumber), 45)))
  451.            
  452.             Sheets(lpmSheet).Cells(lpmItem, 15).Value = totalPrice
  453.             Sheets(lpmSheet).Cells(lpmItem, 30).Value = totalMapp
  454.  
  455.             Sheets(lpmSheet).Cells(moduleStart, 15).Value = sellTotal
  456.             Sheets(lpmSheet).Cells(moduleStart, 30).Value = mappTotal
  457.             Sheets(lpmSheet).Cells(moduleStart, 45).Value = diffTotal
  458.            
  459.             Sheets(lpmSheet).Range(Cells(moduleStart, 30), Cells(moduleStart, 30)).NumberFormat = accountingFormat
  460.             Sheets(lpmSheet).Range(Cells(moduleStart, 15), Cells(moduleStart, 15)).NumberFormat = accountingFormat
  461.             Sheets(lpmSheet).Range(Cells(moduleStart, 45), Cells(moduleStart, 45)).NumberFormat = accountingFormat
  462.            
  463.             'Diff from MAPP
  464.            diffFromMappFormula = "=AD" + CStr(lpmItem) + "-O" + CStr(lpmItem)
  465.             diffMappPercentFormula = "=AS" + CStr(moduleStart) + "/AD" + CStr(moduleStart)
  466.             Sheets(lpmSheet).Cells(lpmItem, 45).Formula = diffFromMappFormula
  467.             Sheets(lpmSheet).Cells(moduleStart + 2, 46).Formula = diffMappPercentFormula
  468.            
  469.             lpmItem = lpmItem + 2
  470.             Exit For
  471.         End If
  472.  
  473.     Next j
  474.    
  475.     If currentWs < mySheets Then
  476.         currentWs = currentWs + 1
  477.     Else
  478.         Sheets(lpmSheet).Activate
  479.     End If
  480.  
  481.  
  482. Next i
  483.  
  484.  
  485. Dim thisSettlement As Long
  486.  
  487. RowCount = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row
  488.  
  489. For i = 16 To RowCount
  490.     testForSettlement = Sheets(2).Cells(i, 3).Value
  491.    
  492.     If testForSettlement = "SETTLEMENT" Then
  493.         thisSettlement = Sheets(2).Cells(i, 10).Value
  494.         Cells(35, 27).Value = Cells(35, 27).Value + thisSettlement
  495.     End If
  496. Next i
  497.  
  498. 'Net Equipment Value
  499. Cells(moduleStart + 10, 27).Value = Cells(moduleStart, 15).Value
  500.  
  501. 'New Equipment Value
  502. Cells(moduleStart + 19, 20).Value = Cells(moduleStart + 10, 27).Value
  503.  
  504. 'Invoice price
  505. formulaString = "=SUM(AA"
  506. formulaString = formulaString + CStr(moduleStart + 4)
  507. formulaString = formulaString + ":"
  508. formulaString = formulaString + "AA"
  509. formulaString = formulaString + CStr(moduleStart + 12)
  510. formulaString = formulaString + ")"
  511. formulaString = CStr(formulaString)
  512. Range(Cells(moduleStart + 14, 27), Cells(moduleStart + 14, 27)).Formula = formulaString
  513.  
  514. 'Settlement
  515. settlementFormulastring = "=AA"
  516. settlementFormulastring = settlementFormulastring + CStr(moduleStart + 11)
  517. Range(Cells(moduleStart + 23, 20), Cells(moduleStart + 23, 20)).Formula = settlementFormulastring
  518.  
  519.  
  520. '%of Air
  521. If Cells(moduleStart + 23, 20).Value = 0 Then
  522.     Cells(moduleStart + 23, 34).Value = 0
  523. Else
  524.     airFormula = "=T" + CStr(moduleStart + 23) + "/AA" + CStr(moduleStart + 14)
  525.     Air = Cells(moduleStart + 23, 20).Value / Cells(moduleStart + 14, 27).Value
  526.     Cells(moduleStart + 23, 34).Formula = airFormula
  527. End If
  528.  
  529. 'Number Formats
  530. Range(Cells(moduleStart + 23, 34), Cells(moduleStart + 23, 34)).NumberFormat = "0.00%"
  531. Range(Cells(moduleStart + 4, 27), Cells(moduleStart + 14, 27)).NumberFormat = accountingFormat
  532. 'Soft Costs
  533. softCostFormula = "=Sum(AA" + CStr(moduleStart + 4) + ":AA" + CStr(moduleStart + 9) + ")" + "+AA" + CStr(moduleStart + 12)
  534. Cells(moduleStart + 25, 20).Formula = softCostFormula
  535.  
  536. 'Total Costs
  537. totalCostFormula = "=Sum(T" + CStr(moduleStart + 19) + ":" + "T" + CStr(moduleStart + 25) + ")"
  538. Cells(moduleStart + 27, 20).Formula = totalCostFormula
  539.  
  540. 'Total Amount Financed
  541. Cells(moduleStart + 31, 20).Formula = totalCostFormula
  542.  
  543. 'Lock cells and protect sheet
  544. Range(Cells(moduleStart, 1), Cells(moduleStart + 30, 58)).Locked = False
  545. Range(Cells(1, 1), Cells(moduleStart - 1, 58)).Locked = True
  546. ActiveSheet.Protect Password:="sherpadoc1"
  547.  
  548. 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!