sample/karimoto-tyosa - konkensyu @ ウィキ

Option Explicit

Sub 仮元帳チェックマクロ()
'
'エラーの際は「エラー処理」へ
On Error GoTo エラー処理

'変数定義

   Dim CYALL As Variant, 行数 As Variant, 列数 As Variant, データ名 As Variant
   

'変数の値代入

   CYALL = Worksheets(2).Name
   行数 = Worksheets(1).Range("A1").End(xlDown).Row
   列数 = Worksheets(Worksheets.Count).Range("B26")
   データ名 = ActiveWorkbook.Name
   

'元帳操作

   With Worksheets(1)
       .Range("O1").Value = "ST_部門ｺｰﾄﾞ"
       .Range("P1").Value = "ST_部門名称"
       .Range("Q1").Value = "判定"

       .Range("O2").Formula = "=VLOOKUP($K2,'" & CYALL & "'!$A$11:$AG$2000," & 列数 - 1 & ",0)"
       .Range("P2").Formula = "=VLOOKUP($K2,'" & CYALL & "'!$A$11:$AG$2000," & 列数 & ",0)"
       .Range("Q2").Formula = "=IF(OR(K2=0,K2=810002,K2=810064）,""OK"",IF(I2 = O2,""OK"",""NG""))"
       
       .Range("O2:Q2").Copy
       .Paste Range(.Range("O2"), .Cells(行数, 15))

'OK行以外をオートフィル

       .Range("A1").AutoFilter Field:=17, Criteria1:="<>" & "OK"
   End With

Exit Sub

'エラー発生時の処理
エラー処理:

   MsgBox Err.Number & ":" & Err.Description

End Sub