How to save the Active Excel Workbook as a different list of names in the location of the Active Workbook

[Gunasekaran]

I tried to save my Active work with .Xlsm but I got an Out of Memory error or my Active work was automatically closed. Can't accomplish this task?
As a result of making multiple .Xlsm workbooks. The end user will update the information, then run the final macro. I need to keep this active workbook module.Code in split files as well.

    Sub FileSplit()

Set ws = ThisWorkbook.Worksheets("Segment TB workings")
    On Error Resume Next
    ThisWorkbook.Sheets("Sdata").Delete
    On Error GoTo 0
    
    ws.Activate
    ws.AutoFilterMode = False
    Dim i As Long, sh As Worksheet, sh2 As Worksheet
    Set sh2 = Sheets.Add(After:=ws)
    sh2.Name = "Sdata"
    Sheets("Segment TB workings").Range("O1:O100000").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Sdata").Range("A1"), CopyToRange:=Range("A1"), _
    Unique:=True
    
        lr = Cells(Rows.Count, "A").End(xlUp).Row 'find last row
        For i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headers
            If Cells(i, "A").Text = "#N/A" Then Rows(i).EntireRow.Delete
        Next i
    
     numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
     i = 2
    Do Until i > numrows
    Set IndName = Worksheets("Sdata").Cells(i, 1)
    
    With ActiveWorkbook
    
    .SaveAs Filename:="C:\Statutory Audit Report\SourceFiles\" & IndName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    .Close 0
    End With
    i = i + 1
    Loop       
    End Sub

 

[Marc L]

Try SaveCopyAs rather than SaveAs and delete the Close codeline …​

​

 

[Gunasekaran]

Thank you for your advice, Macr L.

I need to delete this "Sdata" Sheet when I savecopyAs before or after. The purpose of this sheet is to remove duplicate "Legal Entity" columns. Create a new sheet instead of this one. Is this a task we can accomplish? based on this sheet Info, I will create workbook Sir.... refer above VBA Code....

Here is the Temporary File, I actually have more than 8 sheets, and this "Segment TB workings" had a lot of Vlookup and Index Match formula from different sheets...

The language I use and the questions I ask should be understandable to you?

 

[Marc L]

Create first the new workbook via SaveCopyAs then open it and remove all the not needed & close / save.​

In case of the new workbook must contain only a single sheet then create it via the Worksheet.Copy method as you can see in VBA help …​

​

 

[Gunasekaran]

Marc, if I create a new book, I get a blank file. To meet my requirement, I need to save this or split it only by Legal Entity. Instead of saving the same Master file as a copy with a different name. I would like to know what code I should write here. I would appreciate your guidance, sir....

 

[Marc L]

As you can't get a blank file if you just follow my posts …​

​