How to break master file into multiple wb and rename them based on file name coulmn

[Learning1]

I have a quick question, I have 50K lines in master files and trying to break 6000 line per file. However, I want to save the file name based on file name column.



For example: lets say I have 10K lines for company name “XYZ” but I want break the file into two files , first file with xyz___00001 and with 6000k lines and second file XYZ_00002 but please notice the column file name is blank after the first till next company name. Other file name should be based on the new customer name “DCB_00001” and so on. Is this possible?



My current VBA code breaks up the files into 6kLines and rename it test_00001 and so on. Any help would be much appreciated. Thanks in advance for your’s response! Below is my VBA code without showing you the declared variables.



'Number of rows to create a new workbook

limit = 6



Set sht = ActiveSheet

Set head = sht.Rows(1)

Set cll = sht.Cells(2, 1)

Do Until cll.Value = ""

i = i + 1

Set rng = cll.Resize(limit, Cells(cll.Row, Columns.Count).End(xlToLeft).Column)





Set wrk = Application.Workbooks.Add

head.Copy wrk.Worksheets(1).Cells(1, 1)

rng.Copy wrk.Worksheets(1).Cells(2, 1)

wrk.SaveAs ThisWorkbook.Path & Application.PathSeparator & prefix & "_" & Format(i, "0000") 'Trying to make filename zero padded

wrk.Close

Set cll = cll.Offset(limit)

Loop



Application.ScreenUpdating = True



End Sub