Notes

Sub ProcessMRPMaterialList()
Dim plantNames As Variant
Dim plant As Variant
Dim srcFilePath As String, destFilePath As String, destFolder As String
Dim wb As Workbook, newWb As Workbook
Dim ws As Worksheet, newWs As Worksheet
Dim rng As Range, cell As Range
Dim rowNum As Long, i As Integer

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

' 각 플랜트에 대해 처리
For Each plant In plantNames
' 원본 파일 경로
srcFilePath = "C:\nerp\생산\mrp_material_list\zmmtr2500_" & plant & ".xlsx"
' 대상 폴더 및 파일 경로
destFolder = "C:\nerp\생산\Table_Export\" & plant
destFilePath = destFolder & "\" & plant & "_data.xlsx"

' 폴더가 없으면 생성
If Dir(destFolder, vbDirectory) = "" Then MkDir destFolder

' 파일 열기
On Error Resume Next
Set wb = Workbooks.Open(srcFilePath)
On Error GoTo 0
If wb Is Nothing Then
MsgBox "파일을 열 수 없습니다: " & srcFilePath, vbExclamation
Exit Sub
End If

Set ws = wb.Sheets(1) ' 첫 번째 시트 사용

' 새로운 파일 생성
Set newWb = Workbooks.Add
Set newWs = newWb.Sheets(1)

' 헤더 복사
ws.Rows(1).Copy newWs.Rows(1)

rowNum = 2
Dim materialDict As Object
Set materialDict = CreateObject("Scripting.Dictionary")

' 전체 Material 목록 저장 (중복 없이)
Dim materialRows As Object
Set materialRows = CreateObject("Scripting.Dictionary")

For Each cell In ws.Range("F2:F" & ws.Cells(Rows.Count, "F").End(xlUp).Row)
If cell.Value <> "" And Not materialDict.exists(cell.Value) Then
materialDict.Add cell.Value, cell.Row
materialRows.Add materialRows.Count, cell.Row
End If
Next cell

' 랜덤으로 10개 선택 (Material 중복 없이)
Dim selectedMaterials As Collection
Set selectedMaterials = New Collection

Dim totalMaterials As Integer
totalMaterials = materialRows.Count

If totalMaterials >= 10 Then
Dim randIndex As Object
Set randIndex = CreateObject("Scripting.Dictionary")

Randomize
Do While selectedMaterials.Count < 10
i = Int(totalMaterials * Rnd)
If Not randIndex.exists(i) Then
randIndex.Add i, Nothing
selectedMaterials.Add materialRows(i)
End If
Loop
Else
MsgBox "Material 데이터가 충분하지 않습니다: " & srcFilePath, vbExclamation
wb.Close False
Exit Sub
End If

' 선택된 Material의 행을 복사하여 저장
Dim matRow As Variant
For Each matRow In selectedMaterials
ws.Rows(CInt(matRow)).Copy newWs.Rows(rowNum)
newWs.Cells(rowNum, 1).Value = plant ' 1열에 Plant 정보 입력
rowNum = rowNum + 1
Next matRow

' 파일 저장
newWb.SaveAs destFilePath, FileFormat:=xlOpenXMLWorkbook
newWb.Close False
wb.Close False
Next plant

MsgBox "모든 파일 처리가 완료되었습니다.", vbInformation
End Sub