SHARE
TWEET

Untitled

a guest Apr 24th, 2019 68 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 Model As String
  7. Dim CompanyName As String
  8. Dim Address As String
  9. Dim City As String
  10. Dim State As String
  11. Dim ZipCode As String
  12.  
  13. moduleStart = 0
  14. standardSheetNumber = 19
  15. mySheets = Worksheets.Count - 3
  16.  
  17.  
  18. For i = standardSheetNumber To mySheets
  19. 'Create Layout
  20. Cells(moduleStart + 2, 1).Value = "Contract Type: CPC"
  21. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Merge
  22. Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Font.Bold = True
  23.  
  24.  
  25. Cells(moduleStart + 3, 1).Value = "Model"
  26. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Merge
  27. Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Font.Bold = True
  28.  
  29. Cells(moduleStart + 5, 1).Value = "Additional Fixed Charge"
  30. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Merge
  31. Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True
  32.  
  33. Cells(moduleStart + 6, 1).Value = "NO"
  34. Cells(moduleStart + 7, 1).Value = "Fixed Charge Description (if applicable):"
  35. Cells(moduleStart + 9, 1).Value = "Blk"
  36. Cells(moduleStart + 10, 1).Value = "Clr"
  37. Cells(moduleStart + 12, 1).Value = "SHIP TO:"
  38. Cells(moduleStart + 13, 1).Value = "Account #:"
  39. Cells(moduleStart + 14, 1).Value = "Name"
  40. Cells(moduleStart + 15, 1).Value = "Address"
  41. Cells(moduleStart + 19, 1).Value = "Special Provisions:"
  42.  
  43. Cells(moduleStart + 6, 2).Value = "YES"
  44. Cells(moduleStart + 8, 2).Value = "Image Change"
  45. Cells(moduleStart + 9, 2).Value = 0
  46. Cells(moduleStart + 10, 2).Value = 0
  47.  
  48. Cells(moduleStart + 3, 4).Value = "Serial#"
  49. Cells(moduleStart + 5, 4).Value = "Fix Change Amount"
  50.  
  51. Cells(moduleStart + 8, 5).Value = "Agreed Volume"
  52. Cells(moduleStart + 9, 5).Value = 0
  53. Cells(moduleStart + 10, 5).Value = 0
  54.  
  55. Cells(moduleStart + 3, 6).Value = "Installed Date"
  56. Cells(moduleStart + 5, 6).Value = "Billing Frequency"
  57. Cells(moduleStart + 13, 6).Value = "Meter Read Contact:"
  58. Cells(moduleStart + 14, 6).Value = "Phone #:"
  59. Cells(moduleStart + 15, 6).Value = "Fax #:"
  60. Cells(moduleStart + 15, 6).Value = "Email:"
  61.  
  62. Cells(moduleStart + 14, 7).Value = 0
  63. Cells(moduleStart + 15, 7).Value = 0
  64. Cells(moduleStart + 15, 7).Value = 0
  65.  
  66. Cells(moduleStart + 8, 8).Value = "Meter Start"
  67.  
  68. Cells(moduleStart + 3, 9).Value = "Service Fee"
  69. Cells(moduleStart + 5, 9).Value = "Initial Period"
  70. Cells(moduleStart + 6, 9).Value = 60
  71. Cells(moduleStart + 19, 9).Value = "Customer Intial:"
  72.  
  73.     'Get Sheet name
  74.    myActiveSheet = Sheets(i).Name
  75.  
  76.     'Get Model
  77.    Model = Sheets(myActiveSheet).Cells(16, 2).Value
  78.     Cells(moduleStart + 4, 1).Value = Model
  79.    
  80.     'Get CompanyName from sheet
  81.    CompanyName = Sheets(myActiveSheet).Cells(7, 2).Value
  82.     Cells(moduleStart + 14, 2).Value = CompanyName
  83.    
  84.     'Get Address from sheet
  85.    Address = Sheets(myActiveSheet).Cells(8, 2).Value
  86.     Cells(moduleStart + 15, 2).Value = Address
  87.    
  88.     'Get City from sheet
  89.    City = Sheets(myActiveSheet).Cells(9, 2).Value
  90.     Cells(moduleStart + 16, 2).Value = City
  91.    
  92.     'Get State from sheet
  93.    State = Sheets(myActiveSheet).Cells(10, 2).Value
  94.     Cells(moduleStart + 17, 2).Value = State
  95.    
  96.     'Get ZipCode from sheet
  97.    ZipCode = Sheets(myActiveSheet).Cells(11, 2).Value
  98.     Cells(moduleStart + 18, 2).Value = ZipCode
  99.    
  100.     moduleStart = moduleStart + 20
  101.  
  102. 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
 
Top