API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 19th, 2019
74
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.39 KB | None | 0 0
raw download clone embed print report
  1. Dim ws1 As Worksheet, ws2 As Worksheet
  2. Dim rTarget As Range
  3. Dim xWs As Worksheet
  4. Dim ws As Worksheet
  5. Dim wkSht As Worksheet
  6.  
  7. If IsEmpty(Range("E9")) = True Then
  8. 'Cell A2 is not blank
  9. MsgBox "Please Add An Employee Number"
  10.  
  11. Else
  12.  
  13.  
  14. For Each wkSht In Sheets
  15.  
  16. If ActiveSheet.Range("e9").Value = wkSht.Name Then
  17.  
  18.  
  19. MsgBox "Employee Already Has A Record Added"
  20.  
  21. End If
  22.  
  23. Next
  24.  
  25.  
  26. Set wh = Worksheets(ActiveSheet.Name)
  27. ActiveSheet.Copy After:=Worksheets(Sheets.Count)
  28. If wh.Range("E9").Value <> "" Then
  29. ActiveSheet.Name = wh.Range("E9").Value
  30. End If
  31. wh.Activate
  32.  
  33.  
  34. Set ws1 = Sheet1
  35. Set ws2 = Sheet4
  36. Set rTarget = ws2.Range("A65536").End(xlUp).Offset(1, 0)
  37. With ws2
  38. rTarget = ws1.Range("E7:G7").Value
  39. rTarget.Offset(0, 1).Value = ws1.Range("E9:G9").Value
  40. rTarget.Offset(0, 2).Value = ws1.Range("E9:G9").Value
  41. rTarget.Offset(0, 3).Value = ws1.Range("K7:M7").Value
  42. rTarget.Offset(0, 4).Value = ws1.Range("K9:M9").Value
  43. rTarget.Offset(0, 5).Value = ws1.Range("K11:M11").Value
  44. rTarget.Offset(0, 6).Value = ws1.Range("K16:M16").Value
  45. rTarget.Offset(0, 7).Value = ws1.Range("K18:M18").Value
  46. rTarget.Offset(0, 8).Value = ws1.Range("E16:G16").Value
  47. rTarget.Offset(0, 9).Value = ws1.Range("E18:G18").Value
  48. rTarget.Offset(0, 10).Value = ws1.Range("E20:G20").Value
  49.  
  50. rTarget.Offset(0, 11).Value = ws1.Range("E23").Value
  51. rTarget.Offset(0, 12).Value = ws1.Range("f23").Value
  52. rTarget.Offset(0, 13).Value = ws1.Range("g23").Value
  53.  
  54. rTarget.Offset(0, 14).Value = ws1.Range("E24").Value
  55. rTarget.Offset(0, 15).Value = ws1.Range("f24").Value
  56. rTarget.Offset(0, 16).Value = ws1.Range("g24").Value
  57.  
  58. rTarget.Offset(0, 17).Value = ws1.Range("E25").Value
  59. rTarget.Offset(0, 18).Value = ws1.Range("f25").Value
  60. rTarget.Offset(0, 19).Value = ws1.Range("g25").Value
  61.  
  62. rTarget.Offset(0, 20).Value = ws1.Range("E26").Value
  63. rTarget.Offset(0, 21).Value = ws1.Range("f26").Value
  64. rTarget.Offset(0, 22).Value = ws1.Range("g26").Value
  65.  
  66. rTarget.Offset(0, 23).Value = ws1.Range("E27").Value
  67. rTarget.Offset(0, 24).Value = ws1.Range("f27").Value
  68. rTarget.Offset(0, 25).Value = ws1.Range("g27").Value
  69.  
  70. rTarget.Offset(0, 26).Value = ws1.Range("E28").Value
  71. rTarget.Offset(0, 27).Value = ws1.Range("f28").Value
  72. rTarget.Offset(0, 28).Value = ws1.Range("g28").Value
  73.  
  74. rTarget.Offset(0, 29).Value = ws1.Range("E29").Value
  75. rTarget.Offset(0, 30).Value = ws1.Range("f29").Value
  76. rTarget.Offset(0, 31).Value = ws1.Range("g29").Value
  77.  
  78. rTarget.Offset(0, 32).Value = ws1.Range("k23").Value
  79. rTarget.Offset(0, 33).Value = ws1.Range("l23").Value
  80. rTarget.Offset(0, 34).Value = ws1.Range("m23").Value
  81.  
  82. rTarget.Offset(0, 35).Value = ws1.Range("k24").Value
  83. rTarget.Offset(0, 36).Value = ws1.Range("l24").Value
  84. rTarget.Offset(0, 37).Value = ws1.Range("m24").Value
  85.  
  86. rTarget.Offset(0, 38).Value = ws1.Range("k25").Value
  87. rTarget.Offset(0, 39).Value = ws1.Range("l25").Value
  88. rTarget.Offset(0, 40).Value = ws1.Range("m25").Value
  89.  
  90. rTarget.Offset(0, 41).Value = ws1.Range("k26").Value
  91. rTarget.Offset(0, 42).Value = ws1.Range("l26").Value
  92. rTarget.Offset(0, 43).Value = ws1.Range("m26").Value
  93.  
  94. rTarget.Offset(0, 44).Value = ws1.Range("k27").Value
  95. rTarget.Offset(0, 45).Value = ws1.Range("l27").Value
  96. rTarget.Offset(0, 46).Value = ws1.Range("m27").Value
  97.  
  98. rTarget.Offset(0, 47).Value = ws1.Range("k28").Value
  99. rTarget.Offset(0, 48).Value = ws1.Range("l28").Value
  100. rTarget.Offset(0, 49).Value = ws1.Range("m28").Value
  101.  
  102. rTarget.Offset(0, 50).Value = ws1.Range("k29").Value
  103. rTarget.Offset(0, 51).Value = ws1.Range("l29").Value
  104. rTarget.Offset(0, 52).Value = ws1.Range("m29").Value
  105.  
  106. rTarget.Offset(0, 53).Value = ws1.Range("E34").Value
  107. rTarget.Offset(0, 54).Value = ws1.Range("f34").Value
  108. rTarget.Offset(0, 55).Value = ws1.Range("g34").Value
  109.  
  110. rTarget.Offset(0, 56).Value = ws1.Range("E35").Value
  111. rTarget.Offset(0, 57).Value = ws1.Range("f35").Value
  112. rTarget.Offset(0, 58).Value = ws1.Range("g35").Value
  113.  
  114. rTarget.Offset(0, 59).Value = ws1.Range("E36").Value
  115. rTarget.Offset(0, 60).Value = ws1.Range("f36").Value
  116. rTarget.Offset(0, 61).Value = ws1.Range("g36").Value
  117.  
  118. rTarget.Offset(0, 62).Value = ws1.Range("E37").Value
  119. rTarget.Offset(0, 63).Value = ws1.Range("f37").Value
  120. rTarget.Offset(0, 64).Value = ws1.Range("g37").Value
  121.  
  122. rTarget.Offset(0, 65).Value = ws1.Range("E38").Value
  123. rTarget.Offset(0, 66).Value = ws1.Range("f38").Value
  124. rTarget.Offset(0, 67).Value = ws1.Range("g38").Value
  125.  
  126. rTarget.Offset(0, 68).Value = ws1.Range("E39").Value
  127. rTarget.Offset(0, 69).Value = ws1.Range("f39").Value
  128. rTarget.Offset(0, 70).Value = ws1.Range("g39").Value
  129.  
  130. rTarget.Offset(0, 71).Value = ws1.Range("E40").Value
  131. rTarget.Offset(0, 72).Value = ws1.Range("f40").Value
  132. rTarget.Offset(0, 73).Value = ws1.Range("g40").Value
  133.  
  134. rTarget.Offset(0, 74).Value = ws1.Range("k34").Value
  135. rTarget.Offset(0, 75).Value = ws1.Range("l34").Value
  136. rTarget.Offset(0, 76).Value = ws1.Range("m34").Value
  137.  
  138. rTarget.Offset(0, 77).Value = ws1.Range("k35").Value
  139. rTarget.Offset(0, 78).Value = ws1.Range("l35").Value
  140. rTarget.Offset(0, 79).Value = ws1.Range("m35").Value
  141.  
  142. rTarget.Offset(0, 80).Value = ws1.Range("k36").Value
  143. rTarget.Offset(0, 81).Value = ws1.Range("l36").Value
  144. rTarget.Offset(0, 82).Value = ws1.Range("m36").Value
  145.  
  146. rTarget.Offset(0, 83).Value = ws1.Range("k37").Value
  147. rTarget.Offset(0, 84).Value = ws1.Range("l37").Value
  148. rTarget.Offset(0, 85).Value = ws1.Range("m37").Value
  149.  
  150. rTarget.Offset(0, 86).Value = ws1.Range("k38").Value
  151. rTarget.Offset(0, 87).Value = ws1.Range("l38").Value
  152. rTarget.Offset(0, 88).Value = ws1.Range("m38").Value
  153.  
  154. rTarget.Offset(0, 89).Value = ws1.Range("k39").Value
  155. rTarget.Offset(0, 90).Value = ws1.Range("l39").Value
  156. rTarget.Offset(0, 91).Value = ws1.Range("m39").Value
  157.  
  158. rTarget.Offset(0, 92).Value = ws1.Range("k40").Value
  159. rTarget.Offset(0, 93).Value = ws1.Range("l40").Value
  160. rTarget.Offset(0, 94).Value = ws1.Range("m40").Value
  161.  
  162. rTarget.Offset(0, 95).Value = ws1.Range("c43:m48").Value
  163.  
  164. End With
  165.  
  166. Range("E7:G7,E9:G9,K7:M7,K9:M9,K11:M11,K16:M16,K18:M18,E16:G16,E18:G18,E20:G20" _
  167. ).Select
  168. Range("E20").Activate
  169. ActiveWindow.SmallScroll Down:=12
  170. Range( _
  171. "E7:G7,E9:G9,K7:M7,K9:M9,K11:M11,K16:M16,K18:M18,E16:G16,E18:G18,E20:G20,E23:G29,K23:M29,K34:M40,E34:G40" _
  172. ).Select
  173. Range("E34").Activate
  174. ActiveWindow.SmallScroll Down:=9
  175. Range( _
  176. "E7:G7,E9:G9,K7:M7,K9:M9,K11:M11,K16:M16,K18:M18,E16:G16,E18:G18,E20:G20,E23:G29,K23:M29,K34:M40,E34:G40,C43:M48" _
  177. ).Select
  178. Range("C43").Activate
  179. Selection.ClearContents
  180. ActiveWindow.SmallScroll Down:=-24
  181. Range("E7:G7").Select
  182. End If
  183.  
  184. Exit Sub
  185.  
  186. no:
  187.  
  188.  
  189. MsgBox "Employee Already Has A Record Added, Please Search To Find"
  190.  
  191.  
  192. 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!