document.write('
Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1. Sub rumus()
  2.     Dim JmlFile As Integer
  3.     Dim NamaFU, NamaFB, NamaFTa, PathFT
  4.     Dim sh As Worksheet
  5.     FT = Application.GetOpenFilename("Excel File (*.xlsx),*.xlsx,XLS Files (*.xls),*.xls", _
  6.         Title:="Pilih file..", MultiSelect:=True)
  7.     If VarType(FT) = vbBoolean Then
  8.         Exit Sub
  9.     End If
  10.     NamaFU = ActiveWorkbook.Name
  11.     JF = UBound(FT)
  12.     For i = 1 To JF
  13.    
  14.         Application.DisplayAlerts = False
  15.        
  16.         Workbooks.Open FT(i)
  17.        
  18.         NamaFT = ActiveWorkbook.Name
  19.         PathFT = ActiveWorkbook.Path
  20.         Application.StatusBar = "Working.. " & NamaFT & " (" & i & " / " & JF & ")"
  21.         If Right(NamaFT, 4) = "xlsx" Then
  22.                 ActiveWorkbook.SaveAs Filename:=PathFT & "\\" & _
  23.                 Left(NamaFT, Len(NamaFT) - 5) & ".xls", _
  24.                 FileFormat:=51, CreateBackup:=False
  25.        
  26.         Else
  27.                 ActiveWorkbook.SaveAs Filename:=PathFT & "\\" & _
  28.                 Left(NamaFT, Len(NamaFT) - 4) & ".xls", _
  29.                 FileFormat:=56, CreateBackup:=False
  30.        
  31.         End If
  32.        
  33.         ActiveSheet.Unprotect
  34.         Range("Z1").Select
  35.         ActiveCell.Value = "KD 1"
  36.         Range("Z2").Select
  37.         ActiveCell.Value = "KD 2"
  38.         Range("Z3").Select
  39.         ActiveCell.Value = "KD 3"
  40.         Range("Z4").Select
  41.         ActiveCell.Value = "KD 4"
  42.         Range("Z5").Select
  43.         ActiveCell.Value = "KD 5"
  44.         Range("AB1").Select
  45.         ActiveCell.Value = "KD 6"
  46.         Range("AB2").Select
  47.         ActiveCell.Value = "KD 7"
  48.         Range("AB3").Select
  49.         ActiveCell.Value = "KD 8"
  50.         Range("AB4").Select
  51.         ActiveCell.Value = "KD 9"
  52.         Range("AB5").Select
  53.         ActiveCell.Value = "KD 10"
  54.         Range("Z7").Select
  55.         ActiveCell.Formula = "=IFERROR(ROUND((G7+I7+K7+M7+O7+Q7+S7+U7+W7+Y7)/AA$1,0),0)"
  56.         Selection.Copy
  57.         Range("Z8:Z42").Select
  58.         ActiveSheet.Paste
  59.        
  60.         Range("Z43").Select
  61.         ActiveCell.Formula = "=IFERROR(ROUND((G43+I43+K43+M43+O43+Q43+S43+U43+W43+Y43)/AA$2,0),0)"
  62.         Selection.Copy
  63.         Range("Z44:Z78").Select
  64.         ActiveSheet.Paste
  65.        
  66.         Range("Z79").Select
  67.         ActiveCell.Formula = "=IFERROR(ROUND((G79+I79+K79+M79+O79+Q79+S79+U79+W79+Y79)/AA$3,0),0)"
  68.         Selection.Copy
  69.         Range("Z80:Z114").Select
  70.         ActiveSheet.Paste
  71.        
  72.         Range("Z115").Select
  73.         ActiveCell.Formula = "=IFERROR(ROUND((G115+I115+K115+M115+O115+Q115+S115+U115+W115+Y115)/AA$4,0),0)"
  74.         Selection.Copy
  75.         Range("Z116:Z150").Select
  76.         ActiveSheet.Paste
  77.        
  78.         Range("Z151").Select
  79.         ActiveCell.Formula = "=IFERROR(ROUND((G151+I151+K151+M151+O151+Q151+S151+U151+W151+Y151)/AA$5,0),0)"
  80.         Selection.Copy
  81.         Range("Z152:Z186").Select
  82.         ActiveSheet.Paste
  83.        
  84.         Range("Z187").Select
  85.         ActiveCell.Formula = "=IFERROR(ROUND((G187+I187+K187+M187+O187+Q187+S187+U187+W187+Y187)/AC$1,0),0)"
  86.         Selection.Copy
  87.         Range("Z188:Z222").Select
  88.         ActiveSheet.Paste
  89.        
  90.         Range("Z223").Select
  91.         ActiveCell.Formula = "=IFERROR(ROUND((G223+I223+K223+M223+O223+Q223+S223+U223+W223+Y223)/AC$2,0),0)"
  92.         Selection.Copy
  93.         Range("Z224:Z258").Select
  94.         ActiveSheet.Paste
  95.        
  96.         Range("Z259").Select
  97.         ActiveCell.Formula = "=IFERROR(ROUND((G259+I259+K259+M259+O259+Q259+S259+U259+W259+Y259)/AC$3,0),0)"
  98.         Selection.Copy
  99.         Range("Z260:Z294").Select
  100.         ActiveSheet.Paste
  101.        
  102.         Range("Z295").Select
  103.         ActiveCell.Formula = "=IFERROR(ROUND((G295+I295+K295+M295+O295+Q295+S295+U295+W295+Y295)/AC$4,0),0)"
  104.         Selection.Copy
  105.         Range("Z295:Z330").Select
  106.         ActiveSheet.Paste
  107.        
  108.         Range("Z331").Select
  109.         ActiveCell.Formula = "=IFERROR(ROUND((G331+I331+K331+M331+O331+Q331+S331+U331+W331+Y331)/AC$5,0),0)"
  110.         Selection.Copy
  111.         Range("Z332:Z330").Select
  112.         ActiveSheet.Paste
  113.        
  114.         Range("F7").Select
  115.         ActiveWindow.FreezePanes = True
  116.          
  117.         Range("AA6").Select
  118.         ActiveCell.Value = "NA"
  119.              
  120.         Range("AD5").Select
  121.         ActiveCell.Value = "jumlah KD"
  122.        
  123.         Range("AA7").Select
  124.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  125.         Selection.Copy
  126.         Range("AA8:AA42").Select
  127.         ActiveSheet.Paste
  128.      
  129.         Range("AA43").Select
  130.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  131.         Selection.Copy
  132.         Range("AA44:AA78").Select
  133.         ActiveSheet.Paste
  134.        
  135.         Range("AA79").Select
  136.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  137.         Selection.Copy
  138.         Range("AA80:AA114").Select
  139.         ActiveSheet.Paste
  140.        
  141.         Range("AA115").Select
  142.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  143.         Selection.Copy
  144.         Range("AA116:AA150").Select
  145.         ActiveSheet.Paste
  146.        
  147.         Range("AA151").Select
  148.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  149.         Selection.Copy
  150.         Range("AA152:AA186").Select
  151.         ActiveSheet.Paste
  152.        
  153.         Range("AA187").Select
  154.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  155.         Selection.Copy
  156.         Range("AA188:AA222").Select
  157.         ActiveSheet.Paste
  158.        
  159.         Range("AA223").Select
  160.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  161.         Selection.Copy
  162.         Range("AA224:AA258").Select
  163.         ActiveSheet.Paste
  164.        
  165.                
  166.         Range("AA259").Select
  167.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  168.         Selection.Copy
  169.         Range("AA260:AA294").Select
  170.         ActiveSheet.Paste
  171.        
  172.         Range("AA295").Select
  173.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  174.         Selection.Copy
  175.         Range("AA296:AA330").Select
  176.         ActiveSheet.Paste
  177.        
  178.         Range("AA331").Select
  179.         ActiveCell.Formula = "=ROUND((Z7+Z43+Z79+Z115+Z151+Z187+Z223+Z259+Z295+Z331)/AD$6,0)"
  180.         Selection.Copy
  181.         Range("AA332:AA366").Select
  182.         ActiveSheet.Paste
  183.        
  184.         ActiveWorkbook.Save
  185.        
  186.         ActiveWorkbook.Close
  187.        
  188.         Application.StatusBar = "Done.. "
  189.        
  190.         Application.DisplayAlerts = True
  191.     Next i
  192.     MsgBox "Membuat Rumus selesai!"
  193.    
  194. End Sub
');