Notes

Sub GenerateInOutFiles_ByPlantAndClassCode()
Dim wbInput As Workbook, wbCategory As Workbook
Dim wsInput As Worksheet, wsCategory As Worksheet
Dim plant As Variant, classCode As String
Dim materialData As Range
Dim i As Integer, j As Integer
Dim filePath As String, inOut As String
Dim plantNames As Variant
Dim pgzMap As Object
Dim plantFolderPath As String
Dim classCodeCell As Range
Dim newWb As Workbook, newWs As Worksheet

' ▶ 플랜트 리스트
plantNames = Array("pgz", "glz", "cnz", "ewz", "a2c", "em9", "cmu", "mma", "en7")

' ▶ G열 값 매핑용 Dictionary
Set pgzMap = CreateObject("Scripting.Dictionary")
pgzMap.Add "pgz", "kr010796"
pgzMap.Add "glz", "kr010881"
pgzMap.Add "cnz", "kr006164"
pgzMap.Add "ewz", "kr006298"
pgzMap.Add "a2c", "id001069"
pgzMap.Add "em9", "pl001051"
pgzMap.Add "cmu", "cn002434"
pgzMap.Add "mma", "eg001026"
pgzMap.Add "en7", "pl001024"

' ▶ Category_InputData 열기
Set wbCategory = Workbooks.Open("c:NERP생산table_exportCategory_InputData.xlsx")
Set wsCategory = wbCategory.Sheets("ClassCode")

' ▶ 각 plant 순회
For Each plant In plantNames
plantFolderPath = "c:NERP생산table_export" & plant & ""

' ▶ 데이터 파일 열기
On Error Resume Next
Set wbInput = Workbooks.Open(plantFolderPath & plant & "_data.xlsx")
If wbInput Is Nothing Then
MsgBox plant & "_data.xlsx 파일을 열 수 없습니다."
On Error GoTo 0
GoTo NextPlant
End If
On Error GoTo 0
Set wsInput = wbInput.Sheets(1)

' ▶ material 10개 불러오기 (F열용)
Set materialData = wsInput.Range("A2:A11")

' ▶ 해당 plant의 classcode들만 추출
For Each classCodeCell In wsCategory.Range("A2:A" & wsCategory.Cells(wsCategory.Rows.Count, "A").End(xlUp).Row)
If Trim(classCodeCell.Value) = plant Then
classCode = Trim(classCodeCell.Offset(0, 1).Value)

' ▶ In/Out 각 1개 파일씩 생성
For Each inOut In Array("In", "Out")
Set newWb = Workbooks.Add
Set newWs = newWb.Sheets(1)

' ▶ 10행 생성
For i = 1 To 10
' A열: plant 이름
newWs.Cells(i, 1).Value = plant
' C열: In 또는 Out
newWs.Cells(i, 3).Value = IIf(inOut = "In", "In", "Out")
' D열: classCode
newWs.Cells(i, 4).Value = classCode
' F열: material
newWs.Cells(i, 6).Value = materialData.Cells(i, 1).Value
' G열: 1~5 = 1200 / 6~10 = plant별 지정값
If i <= 5 Then
newWs.Cells(i, 7).Value = "1200"
Else
newWs.Cells(i, 7).Value = pgzMap(plant)
End If
Next i

' ▶ 파일 저장
filePath = plantFolderPath & plant & "_" & inOut & "_" & classCode & ".xlsx"
Application.DisplayAlerts = False ' 기존 파일 덮어쓰기 방지 메시지 비활성화
newWb.SaveAs Filename:=filePath, FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Application.DisplayAlerts = True
Next inOut
End If
Next classCodeCell

wbInput.Close SaveChanges:=False
NextPlant:
Next plant

MsgBox "모든 파일 생성 완료!"
End Sub