Do Saveas copy Only Specific Sheets with .Xlsm, Instead of Entire .xlsm Workbook

[Gunasekaran]

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

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

 

[Marc L]

Hi, no sense as 'a particular sheet' means the same as 'an entire worksheet' !​

​

To copy only some sheets then use an array as the sheets names reference like Sheets(Array("sheetname1","sheetname2")).Copy …​

​

 

[Gunasekaran]

@Marc L - Yes, I need to split the Main Excel file with. Xlsm as Comp Wise ( with Build Macro Code) in But only Some Sheets only.

Instead of Entire workbook as Saveas new Name...

If need Work, i will attached. please confirm.

 

[Marc L]

( with Build Macro Code) in But only Some Sheets only.

So duplicate the workbook via SaveCopyAs then open the duplicated workbook,​

delete the unwanted worksheets, save and close it, easy with the Macro Recorder …​

​

 

[Gunasekaran]

Sir there is no anyother straight forward code or best code. Instead of this method...

 

[Marc L]

As this is the 'straight forward' method …​

Another way is to copy the desired worksheets to a new workbook created from a template macro workbook …​

​

Try any method manually then once you make your choice, activate the Macro Recorder and redo the same operations​

in order to get your own code base.​

​

 

[Gunasekaran]

OK Thanks for your valuable information.