Split 1 worksheet into many excel files

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

When you say that you want each smaller file to contain the header, do you mean the first 9 rows? What name do you want to use to save each file? What is the full path to the folder where the new files should be saved?

Copy/paste this macro in a standard module in the master workbook and run it from there.

Sub CreateWorkbooks()
    Application.ScreenUpdating = False
    Dim ProdDesc As Range, srcWB As Workbook, srcWS As Worksheet
    Set srcWB = ThisWorkbook
    Set srcWS = srcWB.Sheets("Master File")
    For Each ProdDesc In srcWS.Range("C10", srcWS.Range("C" & srcWS.Rows.Count).End(xlUp))
        With Workbooks.Add
            srcWS.Rows("1:9").Copy Cells(1, 1)
            ProdDesc.EntireRow.Copy Cells(10, 1)
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=srcWB.Path & Application.PathSeparator & "HBG IPD 201901-" & ProdDesc, FileFormat:=51
            Application.DisplayAlerts = True
            ActiveWorkbook.Close False
        End With
    Next ProdDesc
    Application.ScreenUpdating = True
End Sub

You are very welcome. I'm glad it worked out.