SHARE
TWEET

Untitled

a guest Apr 25th, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Dim mySheets As Long
  2. Dim myActiveSheet As String
  3. Dim standardSheetNumber As Long
  4. Dim moduleStart As Long
  5.  
  6. Dim accountDetails As String
  7. Dim accountNumber As Long
  8. Dim Model As String
  9. Dim CompanyName As String
  10. Dim Address As String
  11. Dim City As String
  12. Dim State As String
  13. Dim ZipCode As String
  14.  
  15. moduleStart = 0
  16. standardSheetNumber = 19
  17. mySheets = Worksheets.Count - 3
  18.  
  19. 'Get account details
  20. accountDetails = Sheets(1).Name
  21. accountNumber = Sheets(accountDetails).Cells(17, 2).Value
  22.  
  23. 'Create Layout
  24. For i = standardSheetNumber To mySheets
  25.  
  26. Cells(moduleStart + 2, 1).Value = "Contract Type: CPC"
  27. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Merge
  28. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Font.Bold = True
  29.  
  30. Cells(moduleStart + 3, 1).Value = "Model"
  31. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Merge
  32. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Font.Bold = True
  33.  
  34. Cells(moduleStart + 5, 1).Value = "Additional Fixed Charge"
  35. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Merge
  36. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True
  37.  
  38. Cells(moduleStart + 6, 1).Value = "NO"
  39. Cells(moduleStart + 7, 1).Value = "Fixed Charge Description (if applicable):"
  40. Range(Cells(moduleStart + 7, 1), Cells(moduleStart + 7, 9)).Merge
  41.  
  42. Cells(moduleStart + 9, 1).Value = "Blk"
  43. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True
  44. Range(Cells(moduleStart + 8, 1), Cells(moduleStart + 10, 9)).Borders.LineStyle = xlContinuous
  45.  
  46. Cells(moduleStart + 10, 1).Value = "Clr"
  47. Cells(moduleStart + 12, 1).Value = "SHIP TO:"
  48. Range(Cells(moduleStart + 12, 1), Cells(moduleStart + 12, 9)).Merge
  49. Cells(moduleStart + 12, 1).Font.Bold = True
  50.  
  51. Cells(moduleStart + 13, 1).Value = "Account #:"
  52. Cells(moduleStart + 14, 1).Value = "Name"
  53. Cells(moduleStart + 15, 1).Value = "Address"
  54. Cells(moduleStart + 19, 1).Value = "Special Provisions:"
  55. Range(Cells(moduleStart + 19, 1), Cells(moduleStart + 19, 8)).Merge
  56.  
  57. Cells(moduleStart + 6, 2).Value = "YES"
  58. Cells(moduleStart + 8, 2).Value = "Image Charge"
  59. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 9)).Font.Bold = True
  60. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 4)).HorizontalAlignment = xlCenter
  61. Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 4)).Merge
  62.  
  63. Cells(moduleStart + 9, 2).Value = 0
  64. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 4)).HorizontalAlignment = xlCenter
  65. Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 4)).Merge
  66.  
  67. Cells(moduleStart + 10, 2).Value = 0
  68. Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 4)).HorizontalAlignment = xlCenter
  69. Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 4)).Merge
  70.  
  71. Cells(moduleStart + 3, 4).Value = "Serial#"
  72. Range(Cells(moduleStart + 4, 4), Cells(moduleStart + 4, 5)).Interior.ColorIndex = 6
  73. Range(Cells(moduleStart + 3, 4), Cells(moduleStart + 3, 5)).Merge
  74. Range(Cells(moduleStart + 3, 4), Cells(moduleStart + 3, 9)).Font.Bold = True
  75.  
  76. Cells(moduleStart + 5, 4).Value = "Fix Change Amount"
  77. Range(Cells(moduleStart + 5, 4), Cells(moduleStart + 5, 9)).Font.Bold = True
  78. Range(Cells(moduleStart + 6, 4), Cells(moduleStart + 6, 5)).Merge
  79. Range(Cells(moduleStart + 6, 4), Cells(moduleStart + 6, 8)).Interior.ColorIndex = 6
  80.  
  81. Cells(moduleStart + 8, 5).Value = "Agreed Volume"
  82. Range(Cells(moduleStart + 8, 5), Cells(moduleStart + 8, 5)).HorizontalAlignment = xlCenter
  83. Range(Cells(moduleStart + 8, 5), Cells(moduleStart + 8, 7)).Merge
  84.  
  85. Cells(moduleStart + 9, 5).Value = 0
  86. Range(Cells(moduleStart + 9, 5), Cells(moduleStart + 9, 7)).HorizontalAlignment = xlCenter
  87. Range(Cells(moduleStart + 9, 5), Cells(moduleStart + 9, 7)).Merge
  88.  
  89. Cells(moduleStart + 10, 5).Value = 0
  90. Range(Cells(moduleStart + 10, 5), Cells(moduleStart + 10, 5)).HorizontalAlignment = xlCenter
  91. Range(Cells(moduleStart + 10, 5), Cells(moduleStart + 10, 7)).Merge
  92.  
  93. Cells(moduleStart + 3, 6).Value = "Installed Date"
  94. Range(Cells(moduleStart + 3, 6), Cells(moduleStart + 3, 8)).Merge
  95. Range(Cells(moduleStart + 4, 6), Cells(moduleStart + 4, 8)).Merge
  96.  
  97. Cells(moduleStart + 5, 6).Value = "Billing Frequency"
  98. Range(Cells(moduleStart + 5, 6), Cells(moduleStart + 5, 8)).Merge
  99. Range(Cells(moduleStart + 6, 6), Cells(moduleStart + 6, 8)).Merge
  100.  
  101. Cells(moduleStart + 13, 6).Value = "Meter Read Contact:"
  102. Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 9)).Merge
  103. Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  104.  
  105. Cells(moduleStart + 14, 6).Value = "Phone #:"
  106. Range(Cells(moduleStart + 14, 7), Cells(moduleStart + 14, 9)).Merge
  107. Range(Cells(moduleStart + 14, 7), Cells(moduleStart + 14, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  108.  
  109. Cells(moduleStart + 15, 6).Value = "Fax #:"
  110. Range(Cells(moduleStart + 15, 7), Cells(moduleStart + 15, 9)).Merge
  111. Range(Cells(moduleStart + 15, 7), Cells(moduleStart + 15, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  112.  
  113. Cells(moduleStart + 16, 6).Value = "Email:"
  114. Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Merge
  115. Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Borders(xlEdgeBottom).LineStyle = xlDash
  116.  
  117. 'Cells at bottom of Module
  118. Range(Cells(moduleStart + 17, 7), Cells(moduleStart + 17, 9)).Merge
  119. Range(Cells(moduleStart + 18, 1), Cells(moduleStart + 18, 9)).Merge
  120. Range(Cells(moduleStart + 20, 1), Cells(moduleStart + 20, 9)).Merge
  121.  
  122. Cells(moduleStart + 14, 7).Value = 0
  123. Cells(moduleStart + 15, 7).Value = 0
  124. Cells(moduleStart + 15, 7).Value = 0
  125.  
  126. Cells(moduleStart + 8, 8).Value = "Meter Start"
  127. Range(Cells(moduleStart + 8, 8), Cells(moduleStart + 8, 8)).HorizontalAlignment = xlCenter
  128. Range(Cells(moduleStart + 8, 8), Cells(moduleStart + 8, 9)).Merge
  129.  
  130. 'under meter start
  131. Range(Cells(moduleStart + 9, 8), Cells(moduleStart + 9, 8)).HorizontalAlignment = xlCenter
  132. Range(Cells(moduleStart + 9, 8), Cells(moduleStart + 9, 9)).Merge
  133.  
  134. 'second cell under meter start
  135. Range(Cells(moduleStart + 10, 8), Cells(moduleStart + 10, 8)).HorizontalAlignment = xlCenter
  136. Range(Cells(moduleStart + 10, 8), Cells(moduleStart + 10, 9)).Merge
  137.  
  138. Cells(moduleStart + 3, 9).Value = "Service Fee"
  139.  
  140. Cells(moduleStart + 4, 9).Interior.ColorIndex = 6
  141.  
  142. Cells(moduleStart + 5, 9).Value = "Initial Period (Mths)"
  143.  
  144. Cells(moduleStart + 6, 9).Value = 60
  145. Cells(moduleStart + 6, 9).HorizontalAlignment = xlLeft
  146.  
  147. Cells(moduleStart + 19, 9).Value = "Customer Intial:"
  148.  
  149.     'Get Sheet name
  150.    myActiveSheet = Sheets(i).Name
  151.  
  152.     'Get Model
  153.    Range(Cells(moduleStart + 4, 1), Cells(moduleStart + 4, 3)).Merge
  154.     Model = Sheets(myActiveSheet).Cells(16, 2).Value
  155.     Cells(moduleStart + 4, 1).Value = Model
  156.    
  157.     'insert Account number
  158.    Range(Cells(moduleStart + 13, 2), Cells(moduleStart + 13, 5)).Merge
  159.     Range(Cells(moduleStart + 13, 2), Cells(moduleStart + 13, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  160.     Cells(moduleStart + 13, 2).Value = accountNumber
  161.     Cells(moduleStart + 13, 2).HorizontalAlignment = xlLeft
  162.    
  163.     'Get CompanyName from sheet
  164.    Range(Cells(moduleStart + 14, 2), Cells(moduleStart + 14, 5)).Merge
  165.     Range(Cells(moduleStart + 14, 2), Cells(moduleStart + 14, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  166.     CompanyName = Sheets(myActiveSheet).Cells(7, 2).Value
  167.     Cells(moduleStart + 14, 2).Value = CompanyName
  168.    
  169.     'Get Address from sheet
  170.    Range(Cells(moduleStart + 15, 2), Cells(moduleStart + 15, 5)).Merge
  171.     Range(Cells(moduleStart + 15, 2), Cells(moduleStart + 15, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  172.     Address = Sheets(myActiveSheet).Cells(8, 2).Value
  173.     Cells(moduleStart + 15, 2).Value = Address
  174.    
  175.     'Get City from sheet
  176.    Range(Cells(moduleStart + 16, 2), Cells(moduleStart + 16, 5)).Merge
  177.     Range(Cells(moduleStart + 16, 2), Cells(moduleStart + 16, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  178.     City = Sheets(myActiveSheet).Cells(9, 2).Value
  179.     Cells(moduleStart + 16, 2).Value = City
  180.    
  181.     'Get State from sheet
  182.    State = Sheets(myActiveSheet).Cells(10, 2).Value
  183.    
  184.     'Get ZipCode from sheet
  185.    Range(Cells(moduleStart + 17, 2), Cells(moduleStart + 17, 5)).Merge
  186.     Range(Cells(moduleStart + 17, 2), Cells(moduleStart + 17, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
  187.     ZipCode = Sheets(myActiveSheet).Cells(11, 2).Value
  188.     Cells(moduleStart + 17, 2).Value = State & " - " & ZipCode
  189.    
  190.     moduleStart = moduleStart + 20
  191.  
  192. Next i
RAW Paste Data
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!
 
Top