I have a master workbook with one sheet that I need to be broken into many workbooks that each have a single worksheet.

These newly created workbooks will be created when rows in the "Master" worksheet have the same content in column B.

I need these workbooks to be saved to the same specific local directory with the file name being the content of column B & ".csv" to make the file a CSV file rather than an XLSX file.

Here is what I have so far (a lot of this came from another upvoted thread on this site with a few tweaks by me).

Sub Splitter()
    Dim Master As Workbook
    Set Master = Workbooks("Master").Worksheets("Master") 'This declares the target worksheet.

    last = 1
    For i = 1 To 2000 'This defines the amount of rows
        If Range("B" & i) <> Range("B" & (i + 1)) Then
            Range("A" & last & ":F" & i).Copy
            Set NewBook = Workbooks.Add
            NewBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

            Rows(1).EntireRow.Insert Shift:=xlDown
            Range("A1").Value = "Header1"
            Range("B1").Value = "Header2"
            Range("C1").Value = "Header3"
            Range("D1").Value = "Header4"
            Range("E1").Value = "Header5"
            Range("F1").Value = "Header6"

            last = i + 1
            Master.Activate
        End If
    Next i
End Sub

This code will create hundreds, if not thousands, of workbooks with single worksheets from the "Master" Workbook.

I'm having a couple issues here:

1. This code:

   Rows(1).EntireRow.Insert Shift:=xlDown
   Range("A1").Value = "Header1"
   Range("B1").Value = "Header2"
   Range("C1").Value = "Header3"
   Range("D1").Value = "Header4"
   Range("E1").Value = "Header5"
   Range("F1").Value = "Header6"
   
   

   appears to be adding the header row correctly but it seems to be copying the entire contents of the spreadsheet and pasting it at the next available row. It then overwrites the contents of row 1.

   Example: The macro pulls the following rows to a new workbook:

   Bat
   Bat
   Bat
   
   

   When the above section of code runs, the final product is:

   Header
   Bat
   Bat
   Bat
   Bat
   Bat
   
   

   It appears to be replicating the content and then pasting over row 1.

2. The second issue, as alluded to earlier, is that the newly created workbooks/worksheets will not be automatically saved (CSV) and closed, and I will need to go in and close/save them each myself.

   They seem to be being created correctly (with the exception of the issue in problem #1). They are simply left open and I need to name and save all of them. Since I am sure there will be a great many of the newly created workbooks, this lack of functionality will make my life very difficult ...

Any help with this would be greatly appreciated. I am fairly new to this, but am picking it up pretty quickly.

My apologies for the long post, I wanted to be as clear as I could as to not waste the readers time.

1. Because you are still in CopyMode from Range("A" & last & ":F" & i).Copy the .Insert will insert the copied rows again. Therefore put a Application.CutCopyMode = False right before Rows(1).EntireRow.Insert to stop inserting copied rows again.

2. You need Workbook.SaveAs Method and Workbook.Close Method to save and close the workbooks.

   NewBook.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
   NewBook.Close(SaveChanges, Filename, RouteWorkbook)
   
   

   eg. This should work:

   NewBook.SaveAs FileName:="C:\Temp\MyFileName.csv", FileFormat:=xlCSV
   NewBook.Close SaveChanges:=False
   
   

3. You should specify any Rows() and Range() with a worksheet like Master.Rows() or NewBook.Worksheets("Sheet1").Range() so that is clear in which workbook\worksheet the range/row is. Then you don't need Master.Activate