Guest User

Untitled

a guest
Oct 16th, 2018
72
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 21.39 KB | None | 0 0
  1. Public Sub CommandButton1_Click()
  2.  
  3. ' Record job, modular code, multiple customers.
  4.  
  5. Dim counter As Integer
  6. Dim PadPercentage As Single
  7. Dim Charactercounter As Integer
  8. Dim Date1 As String
  9. Dim Date2 As String
  10. Dim fd As FileDialog
  11. Dim vrtSelectedItem As Variant
  12. Dim Designcounter As Integer
  13. Dim Customer As String
  14. Dim Chemicals As String
  15. Dim Chemcounter As Integer
  16. Dim column As String
  17. Dim Sand As Integer
  18. Dim FindRow As Range
  19.  
  20.  
  21.  
  22.  
  23. Set fd = Application.FileDialog(msoFileDialogFilePicker)
  24. Designcounter = -1
  25.  
  26.  
  27. With fd
  28.  
  29. If .Show = -1 Then
  30.  
  31. For Each vrtSelectedItem In .SelectedItems
  32. Designcounter = Designcounter + 1
  33. Workbooks.Open Filename:=vrtSelectedItem
  34. Sheets("Interval Summary").Select
  35. counter = 4
  36. Charactercounter = 1
  37.  
  38.  
  39. ' Find and Copy date from Interval Summary.
  40.  
  41. Set FindRow = Cells.Find(What:="Date:", LookAt:=xlPart)
  42. FindRow.Select
  43. ActiveCell.Offset(0, 3).Select
  44. Selection.Copy
  45.  
  46. Windows("2014 GJ PE Engineering Job Logs - Iteration 2.xls").Activate
  47. Range("A" & CStr(counter)).Select
  48.  
  49.  
  50. ' Search for first blank cell in column A.
  51. Do While ActiveCell.Value <> ""
  52. counter = counter + 1
  53. Range("A" & CStr(counter)).PasteSpecial xlPasteValuesAndNumberFormats
  54. Loop
  55.  
  56. ' Paste date onto job recording sheet.
  57. Range("A" & CStr(counter)).Select
  58. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  59. :=False, Transpose:=False
  60. Selection.UnMerge
  61. Selection.NumberFormat = "m/d/yyyy"
  62.  
  63. ' Record previous engineer name on job recording sheet.
  64. Range("B" & CStr(counter - 1)).Select
  65. Selection.Copy
  66. Range("B" & CStr(counter)).Select
  67. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  68. :=False, Transpose:=False
  69.  
  70. ' Copy customer name onto reporting sheet.
  71. ActiveWindow.ActivatePrevious
  72. Worksheets("Actual Design").Range("C1").Select
  73. Customer = ActiveCell.Value
  74. Selection.Copy
  75. ActiveWindow.ActivatePrevious
  76. Range("E" & CStr(counter)).Select
  77. ActiveSheet.Paste
  78.  
  79. ' Paste SO from design onto recording sheet.
  80. ActiveWindow.ActivateNext
  81. If Customer = "Noble Energy Inc." Then
  82. Worksheets("Design").Range("O1").Select
  83. Else
  84. Worksheets("Design").Range("Q1").Select
  85. End If
  86. Selection.Copy
  87. ActiveWindow.ActivatePrevious
  88. Range("C" & CStr(counter)).Select
  89. ActiveSheet.Paste
  90. Selection.UnMerge
  91.  
  92.  
  93. Call Lease_Pad_Well_Copy(Customer, counter)
  94.  
  95.  
  96.  
  97. ' Find and Copy Interval # from Well Data
  98. With Worksheets("Well Data")
  99. Set FindRow = .Range("B:B").Find(What:="Date", LookIn:=xlValues)
  100. Windows("2014 GJ PE Engineering Job Logs.xls").Activate
  101. Range("A" & CStr(counter)).Select
  102. End With
  103.  
  104.  
  105. ' Copy mid perf depth to reporting sheet.
  106.  
  107. Worksheets("Actual").Range("C40").Select
  108. Selection.Copy
  109. ActiveWindow.ActivatePrevious
  110. Range("I" & CStr(counter)).Select
  111. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  112. :=False, Transpose:=False
  113.  
  114.  
  115. ' Copy mid perf depth TVD to reporting sheet.
  116.  
  117. Worksheets("Actual").Range("C40").Select
  118. Selection.Copy
  119. ActiveWindow.ActivatePrevious
  120. Range("I" & CStr(counter)).Select
  121. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  122. :=False, Transpose:=False
  123.  
  124.  
  125. ' Copy Top perf depth to reporting sheet.
  126.  
  127. Worksheets("Actual").Range("C40").Select
  128. Selection.Copy
  129. ActiveWindow.ActivatePrevious
  130. Range("I" & CStr(counter)).Select
  131. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  132. :=False, Transpose:=False
  133.  
  134.  
  135. ' Copy Bottom perf depth to reporting sheet.
  136.  
  137. Worksheets("Actual").Range("C40").Select
  138. Selection.Copy
  139. ActiveWindow.ActivatePrevious
  140. Range("I" & CStr(counter)).Select
  141. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  142. :=False, Transpose:=False
  143.  
  144.  
  145. ' Copy formation name to reporting sheet.
  146. ActiveWindow.ActivateNext
  147. Worksheets("Design").Range("C3").Select
  148. Selection.Copy
  149. ActiveWindow.ActivatePrevious
  150. Range("J" & CStr(counter)).Select
  151. ActiveSheet.Paste
  152.  
  153.  
  154. ' Copy fluid system.
  155. Range("K" & CStr(counter - 1)).Select
  156. Selection.Copy
  157. Range("K" & CStr(counter)).Select
  158. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  159. :=False, Transpose:=False
  160.  
  161. ' Copy crew from previous job.
  162. Range("L" & CStr(counter - 1)).Select
  163. Selection.Copy
  164. Range("L" & CStr(counter)).Select
  165. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  166. :=False, Transpose:=False
  167.  
  168. If Customer = "Williams Prod RMT" Or Customer = "Chevron" Then
  169. Call Copy_Williams_Data(Customer, counter)
  170. End If
  171.  
  172. If Customer = "Noble Energy Inc." Then
  173. Call Copy_Noble_Data(Customer, counter)
  174. End If
  175.  
  176. If Customer = "Bill Barrett Corp." Then
  177. Call Copy_BBC(Customer, counter)
  178. End If
  179.  
  180. ' Copy slurry volume
  181.  
  182. If Customer = "Williams Prod RMT" Then
  183. ActiveWindow.ActivateNext
  184. Sheets("Actuals").Select
  185. Worksheets("Actuals").Range("H30").Select
  186. Selection.Copy
  187. Else
  188. ActiveWindow.ActivateNext
  189. Sheets("Design").Select
  190. Worksheets("Design").Range("H30").Select
  191. Selection.Copy
  192. End If
  193. ActiveWindow.ActivatePrevious
  194. Range("S" & CStr(counter)).Select
  195. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  196. :=False, Transpose:=False
  197.  
  198.  
  199.  
  200. ' Copy chemicals from design to Job recording sheet.
  201. ActiveWindow.ActivateNext
  202. Chemcounter = 78
  203. column = Chr(Chemcounter)
  204. Sheets("Well Data").Select
  205. Worksheets("Design").Range(column & "5").Select
  206. Do While ActiveCell.Value <> ""
  207. If Chemcounter < 79 Then Chemicals = ActiveCell.Value
  208. If Chemcounter > 78 Then Chemicals = Chemicals & ", " & ActiveCell.Value
  209. Chemcounter = Chemcounter + 1
  210. column = Chr(Chemcounter)
  211. Worksheets("Well Data").Range(column & "5").Select
  212. Loop
  213. ActiveWindow.ActivatePrevious
  214. Range("P" & CStr(counter)).Select
  215. ActiveCell.Value = Chemicals
  216.  
  217.  
  218.  
  219. ' Switch back to and close design
  220. ActiveWindow.ActivateNext
  221. ActiveWorkbook.Save
  222. ActiveWindow.Close
  223.  
  224. Next vrtSelectedItem
  225. End If
  226. End With
  227.  
  228. ' Format job log entries.
  229. ActiveWindow.ActivatePrevious
  230. Range("A" & CStr(counter - Designcounter) & ":AE" & CStr(counter)).Select
  231. Application.CutCopyMode = False
  232. With Selection.Font
  233. .Name = "Arial"
  234. .Size = 10
  235. .Strikethrough = False
  236. .Superscript = False
  237. .Subscript = False
  238. .OutlineFont = False
  239. .Shadow = False
  240. .Underline = xlUnderlineStyleNone
  241. .ColorIndex = xlAutomatic
  242. End With
  243. Selection.Font.Bold = False
  244. Rows(CStr(counter) & ":" & CStr(counter)).Select
  245. Selection.RowHeight = 13.5
  246.  
  247.  
  248.  
  249. End Sub
  250.  
  251.  
  252.  
  253. Sub Lease_Pad_Well_Copy(Customer, counter)
  254.  
  255. Dim Wellstrng As String
  256. Dim Pad As String
  257. Dim Wellpad As String
  258. Dim Lease As String
  259. Dim Well As String
  260.  
  261.  
  262.  
  263. If Customer = "Williams Prod RMT" Or Customer = "Chevron" Or Customer = "Noble Energy Inc." Or Customer = "Bill Barrett Corp." Then
  264. ' Sort lease, well, and pad number and copy to reporting sheet.
  265. ActiveWindow.ActivateNext
  266. Worksheets("Design").Range("C2").Select
  267. If ActiveCell.Value <> "" Then
  268. Wellstrng = ActiveCell.Value
  269. Lease = Left(Wellstrng, CLng(InStr(Wellstrng, " ")) - 1)
  270. Pad = Right(Wellstrng, Len(Wellstrng) - CLng(InStrRev(Wellstrng, "-")))
  271. Wellpad = Left(Wellstrng, CLng(InStr(Wellstrng, "-")) - 1)
  272. Well = Right(Wellpad, Len(Wellpad) - CLng(InStrRev(Wellpad, " ")))
  273.  
  274.  
  275.  
  276. If Customer = "Noble Energy Inc." Then
  277.  
  278. Wellstrng = ActiveCell.Value
  279. Lease = Left(Wellstrng, CLng(InStr(Wellstrng, " ")) - 1)
  280. Wellpad = Right(Wellstrng, Len(Wellstrng) - CLng(InStr(Wellstrng, " ")))
  281. Wellpad = Left(Wellpad, Len(Wellpad) - CLng(InStrRev(Wellpad, " -")))
  282. Pad = Left(Wellpad, CLng(InStr(Wellpad, "-")) - 1)
  283. Wellpad = Left(Wellstrng, CLng(InStr(Wellstrng, " -")) - 1)
  284. Well = Right(Wellpad, Len(Wellpad) - CLng(InStrRev(Wellpad, "-")))
  285.  
  286. End If
  287.  
  288. If Customer = "Bill Barrett Corp." Then
  289. Wellstrng = ActiveCell.Value
  290. Lease = Left(Wellstrng, CLng(InStr(Wellstrng, " ")) - 1)
  291. Pad = Right(Wellstrng, Len(Wellstrng) - CLng(InStr(Wellstrng, "-")))
  292. Wellpad = Left(Wellstrng, CLng(InStr(Wellstrng, "-")) - 1)
  293. Well = Right(Wellpad, Len(Wellpad) - CLng(InStrRev(Wellpad, " ")))
  294. End If
  295.  
  296.  
  297.  
  298. ActiveWindow.ActivatePrevious
  299.  
  300.  
  301.  
  302. ' Copy lease name onto reporting sheet.
  303. Range("F" & CStr(counter)).Select
  304. ActiveCell.Value = Lease
  305.  
  306. ' Copy well number onto reporting sheet.
  307. Range("G" & CStr(counter)).Select
  308. ActiveCell.Value = Well
  309.  
  310. ' Copy pad onto reporting sheet.
  311. Range("H" & CStr(counter)).Select
  312. ActiveCell.Value = Pad
  313. ActiveWindow.ActivateNext
  314. End If
  315.  
  316. End If
  317.  
  318.  
  319. End Sub
  320.  
  321.  
  322.  
  323.  
  324. Sub Copy_BBC(Customer, counter)
  325.  
  326. Dim Twosands As String
  327. Dim Sandint As Integer
  328.  
  329. ' Copy average rate
  330. ActiveWindow.ActivateNext
  331. Sheets("Database").Select
  332. Worksheets("Database").Range("B16").Select
  333. Selection.Copy
  334. ActiveWindow.ActivatePrevious
  335. Range("M" & CStr(counter)).Select
  336. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  337. :=False, Transpose:=False
  338.  
  339. ' Copy average pressure
  340. ActiveWindow.ActivateNext
  341. Worksheets("Database").Range("B17").Select
  342. Selection.Copy
  343. ActiveWindow.ActivatePrevious
  344. Range("N" & CStr(counter)).Select
  345. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  346. :=False, Transpose:=False
  347.  
  348. ' Copy perfs open.
  349. ActiveWindow.ActivateNext
  350. Worksheets("Database").Range("G18").Select
  351. Selection.Copy
  352. ActiveWindow.ActivatePrevious
  353. Range("W" & CStr(counter)).Select
  354. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  355. :=False, Transpose:=False
  356.  
  357. ' Copy actual sand
  358. ActiveWindow.ActivateNext
  359. Worksheets("Database").Range("B26").Select
  360. Twosands = ActiveCell.Value
  361. Twosands = Twosands & " / "
  362. Worksheets("Database").Range("B28").Select
  363. Twosands = Twosands & ActiveCell.Value
  364. ActiveWindow.ActivatePrevious
  365. Range("Q" & CStr(counter)).Select
  366. ActiveCell.Value = Twosands
  367.  
  368.  
  369. ' Copy initial frac gradient
  370. ActiveWindow.ActivateNext
  371. Sheets("Database").Select
  372. Worksheets("Database").Range("B21").Select
  373. Selection.Copy
  374. ActiveWindow.ActivatePrevious
  375. Range("V" & CStr(counter)).Select
  376. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  377. :=False, Transpose:=False
  378.  
  379. ' Copy final frac gradient
  380. ActiveWindow.ActivateNext
  381. Worksheets("Database").Range("B23").Select
  382. Selection.Copy
  383. ActiveWindow.ActivatePrevious
  384. Range("Y" & CStr(counter)).Select
  385. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  386. :=False, Transpose:=False
  387.  
  388. ' Copy ISIP
  389. ActiveWindow.ActivateNext
  390. Worksheets("Database").Range("B20").Select
  391. Selection.Copy
  392. ActiveWindow.ActivatePrevious
  393. Range("U" & CStr(counter)).Select
  394. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  395. :=False, Transpose:=False
  396.  
  397. ' Copy ISDP
  398. ActiveWindow.ActivateNext
  399. Worksheets("Database").Range("B22").Select
  400. Selection.Copy
  401. ActiveWindow.ActivatePrevious
  402. Range("X" & CStr(counter)).Select
  403. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  404. :=False, Transpose:=False
  405.  
  406.  
  407.  
  408. End Sub
  409.  
  410. Sub Copy_Williams_Data(Customer, counter)
  411.  
  412.  
  413. ' Copy average rate to reporting sheet.
  414. ActiveWindow.ActivateNext
  415. Sheets("Actuals").Select
  416. Worksheets("Actuals").Range("G63").Select
  417. Selection.Copy
  418. ActiveWindow.ActivatePrevious
  419. Range("M" & CStr(counter)).Select
  420. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  421. :=False, Transpose:=False
  422.  
  423. ' Copy average pressure to reporting sheet.
  424. ActiveWindow.ActivateNext
  425. Worksheets("Actuals").Range("F63").Select
  426. Selection.Copy
  427. ActiveWindow.ActivatePrevious
  428. Range("N" & CStr(counter)).Select
  429. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  430. :=False, Transpose:=False
  431.  
  432. ' Copy perfs open.
  433. ActiveWindow.ActivateNext
  434. Worksheets("Actuals").Range("D64").Select
  435. Selection.Copy
  436. ActiveWindow.ActivatePrevious
  437. Range("W" & CStr(counter)).Select
  438. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  439. :=False, Transpose:=False
  440.  
  441. ' Copy actual sand
  442. ActiveWindow.ActivateNext
  443. Worksheets("Actuals").Range("D65").Select
  444. Selection.Copy
  445. ActiveWindow.ActivatePrevious
  446. Range("Q" & CStr(counter)).Select
  447. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  448. :=False, Transpose:=False
  449.  
  450. ' Copy initial frac gradient
  451. ActiveWindow.ActivateNext
  452. Sheets("Actuals").Select
  453. Worksheets("Design").Range("D61").Select
  454. Selection.Copy
  455. ActiveWindow.ActivatePrevious
  456. Range("V" & CStr(counter)).Select
  457. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  458. :=False, Transpose:=False
  459.  
  460. ' Copy final frac gradient
  461. ActiveWindow.ActivateNext
  462. Worksheets("Actuals").Range("D63").Select
  463. Selection.Copy
  464. ActiveWindow.ActivatePrevious
  465. Range("Y" & CStr(counter)).Select
  466. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  467. :=False, Transpose:=False
  468.  
  469. ' Copy ISIP
  470. ActiveWindow.ActivateNext
  471. Worksheets("Actuals").Range("D60").Select
  472. Selection.Copy
  473. ActiveWindow.ActivatePrevious
  474. Range("U" & CStr(counter)).Select
  475. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  476. :=False, Transpose:=False
  477.  
  478. ' Copy ISDP
  479. ActiveWindow.ActivateNext
  480. Worksheets("Actuals").Range("D62").Select
  481. Selection.Copy
  482. ActiveWindow.ActivatePrevious
  483. Range("X" & CStr(counter)).Select
  484. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  485. :=False, Transpose:=False
  486.  
  487. End Sub
  488.  
  489. Sub Copy_Noble_Data(Customer, counter)
  490.  
  491. Dim SandColor As String
  492. Dim Sieve As String
  493. Dim Sandtemp As String
  494. Dim Sandtype As String
  495.  
  496. ' Copy average rate to reporting sheet.
  497. ActiveWindow.ActivateNext
  498. Sheets("Actuals Design").Select
  499. Worksheets("Actual Design").Range("H63").Select
  500. Selection.Copy
  501. ActiveWindow.ActivatePrevious
  502. Range("M" & CStr(counter)).Select
  503. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  504. :=False, Transpose:=False
  505.  
  506. ' Copy average pressure to reporting sheet.
  507. ActiveWindow.ActivateNext
  508. Worksheets("Actual Design").Range("H62").Select
  509. Selection.Copy
  510. ActiveWindow.ActivatePrevious
  511. Range("N" & CStr(counter)).Select
  512. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  513. :=False, Transpose:=False
  514.  
  515. ' Copy Total perfs open.
  516. ActiveWindow.ActivateNext
  517. Worksheets("Actual Design").Range("E65").Select
  518. Selection.Copy
  519. ActiveWindow.ActivatePrevious
  520. Range("W" & CStr(counter)).Select
  521. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  522. :=False, Transpose:=False
  523.  
  524. ' Copy actual sand.
  525. ActiveWindow.ActivateNext
  526. Worksheets("Design").Range("M61").Select
  527. Greensand = ActiveCell.Value
  528. Worksheets("Design").Range("M60").Select
  529. Whitesand = ActiveCell.Value & " / "
  530. Combinedsand = Whitesand & Greensand
  531. ActiveWindow.ActivatePrevious
  532. Range("Q" & CStr(counter)).Select
  533. ActiveCell.Value = Combinedsand
  534.  
  535.  
  536. ' Copy initial frac gradient
  537. ActiveWindow.ActivateNext
  538. Sheets("Interval Summart").Select
  539. Worksheets("Design").Range("E64").Select
  540. Selection.Copy
  541. ActiveWindow.ActivatePrevious
  542. Range("V" & CStr(counter)).Select
  543. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  544. :=False, Transpose:=False
  545.  
  546. ' Copy final frac gradient
  547. ActiveWindow.ActivateNext
  548. Worksheets("Design").Range("H65").Select
  549. Selection.Copy
  550. ActiveWindow.ActivatePrevious
  551. Range("Y" & CStr(counter)).Select
  552. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  553. :=False, Transpose:=False
  554.  
  555. ' Copy ISIP
  556. ActiveWindow.ActivateNext
  557. Worksheets("Design").Range("E63").Select
  558. Selection.Copy
  559. ActiveWindow.ActivatePrevious
  560. Range("U" & CStr(counter)).Select
  561. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  562. :=False, Transpose:=False
  563.  
  564. ' Copy ISDP
  565. ActiveWindow.ActivateNext
  566. Worksheets("Design").Range("H64").Select
  567. Selection.Copy
  568. ActiveWindow.ActivatePrevious
  569. Range("X" & CStr(counter)).Select
  570. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  571. :=False, Transpose:=False
  572.  
  573.  
  574. End Sub
  575.  
  576. Sub FindingDollarsSpent()
  577. Dim i As Long
  578. Dim l As Long
  579. l = 1
  580. For i = 2 To Sheets.Count
  581. For Each r In Sheets(i).UsedRange
  582. If r.Value = "Dollars spent" Then
  583. With Sheets(1)
  584. .Cells(l, 1).Value = Sheets(i).Name
  585. .Cells(l, 2).Value = r.Offset(0, 1).Value
  586. End With
  587. l = l + 1
  588. End If
  589. Next r
  590. Next i
  591. End Sub
Add Comment
Please, Sign In to add comment