SHARE
TWEET

Untitled

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