Gunasekaran
Member
Hi All,
How to make Saveas copy only a particular sheet rather than the entire worksheet With.Xlsm, because after splitting the master Excel file, the user must enter forecast information before running the Final macro once more.
Therefore, they require Module.code in a save-as worksheet. (Reason - Because my master data file more than 12 MB, same Split file as created this Size.)
I need only "BPC_Master", "BPC_Template" & "Segment TB workings" Sheets alone
How to make Saveas copy only a particular sheet rather than the entire worksheet With.Xlsm, because after splitting the master Excel file, the user must enter forecast information before running the Final macro once more.
Therefore, they require Module.code in a save-as worksheet. (Reason - Because my master data file more than 12 MB, same Split file as created this Size.)
I need only "BPC_Master", "BPC_Template" & "Segment TB workings" Sheets alone
Code:
Sub FileSplit()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
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("A1", Range("A1").End(xlDown)).Rows.Count
Add1 = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Add1) = Evaluate("IF(" & Add1 & "="""","""",TRIM(" & Add1 & "))")
i = 2
Do Until i > numrows
Set IndName = Worksheets("Sdata").Cells(i, 1)
With ActiveWorkbook
.SaveCopyAs Filename:="C:\Statutory Audit Report\SourceFiles\" & IndName.Value & ".xlsm"
End With
i = i + 1
Loop
Call FileSplitwithDelete
End Sub
Last edited: