Help with Macros in Excel

Copy this code into a Module in your PO file

Sub ImportProdSchedule()
Dim PO As String
Dim i As Integer
Dim j As Integer
Dim EndIdx As Integer

Dim POwbk As Workbook
Dim POsht As Worksheet
Dim SCHEDwbk As Workbook
Dim SCHEDsht As Worksheet

Dim SchedulePath As String
Dim SavedPath As String
Dim SchedSheetName As String
Dim SchedFileName As String


Set POwbk = ThisWorkbook
Set POsht = POwbk.Sheets("yourPOsheet")


SchedulePath = "C:\tempupdate\ScheduleTemplateName.xlsx"
          '     "\\SERVER\Folder\ScheduleTemplateName.xlsx"
          ' or  "C:\Folder\ScheduleTemplateName.xlsx"

SchedSheetName = "NameOfScheduleSheet"
SavedPath = "C:\tempupdate\FolderToSave\"
SchedFileName = "Production Schedule" '.xlsx    followed by the PO number

PO = ""
i = 1
j = 1

' u/Cleev : You need to change the correct range where the PO numbers are below, where it's
'           it's written G2:G10000. We want to largely overshoot the maximum number of cells in that range
EndIdx = POsht.Range("G2:G10000").Cells.SpecialCells(xlCellTypeConstants).Count

Set SCHEDwbk = Workbooks.Open(SchedulePath)
Set SCHEDsht = SCHEDwbk.Sheets(SchedSheetName)

 PO = POsht.Range("G2").Offset(i - 1, 0).Value
 SCHEDsht.Range("A5:P5").Offset(i - 1, 0).Value = POsht.Range("A2:P2").Offset(i - 1, 0).Value
 SCHEDwbk.SaveAs SavedPath & SchedFileName & " - " & PO & ".xlsx"


For i = 1 To (EndIdx - 1)

    If POsht.Range("G2").Offset(i, 0).Value <> PO Then


        SCHEDwbk.Close
        j = 1
        Set SCHEDwbk = Workbooks.Open(SchedulePath)
        Set SCHEDsht = SCHEDwbk.Sheets(SchedSheetName)

        PO = POsht.Range("G2").Offset(i, 0).Value
        SCHEDwbk.SaveAs SavedPath & SchedFileName & " - " & PO & ".xlsx"


        SCHEDsht.Range("A5:P5").Offset(j - 1, 0).Value = POsht.Range("A2:P2").Offset(i, 0).Value
        SCHEDwbk.Save


    Else
        SCHEDsht.Range("A5:P5").Offset(j, 0).Value = POsht.Range("A2:P2").Offset(i, 0).Value
        SCHEDwbk.Save
        j = j + 1

    End If

Next i


SCHEDwbk.Close
End Sub
/r/vba Thread Parent