Deleting multiple worksheets and saving active worksheet

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
518
I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
are the names always the same of the sheets you want to delete/rename?

Code:
    Application.DisplayAlerts = False
    Sheets(Array("Sheet2", "Sheet3")).Delete
    Application.DisplayAlerts = True

    savedir = "C:\Documents and Settings\My Documents\"
    ActiveWorkbook.SaveAs Filename:= _
        savedir & ActiveSheet.Name & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
 
Upvote 0

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
518
zzjasonzz,

Tried running your codes and I got a error message, "Subscript out of range" and the code stop at

Sheets(Array("Sheet1", "Sheet2")).Delete

any idea what is wrong?
 
Upvote 0

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
518
zzjasonzz,

Tried running your codes and I got a error message, "Subscript out of range" and the code stop at

Sheets(Array("Sheet1", "Sheet2")).Delete

any idea what is wrong? For your information the workbook contains sheet named as "summary", " action list", "Title", "Inf001", "Inf002" ..... "Inf050"
 
Upvote 0

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
singcbl

1) how many worksheets do you want to save as new workbook?

2) what are the name(s) of the worksheet that you want to save?
 
Upvote 0

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
518
Jindon-san,

I just need to save the selected worksheet or worksheets and the name of the to be saved workbook should be the same name as the selected worksheet or worksheet. If multiple selection is not possible then just the active worksheet is fine
 
Upvote 0

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
for the single sheet selection
try
Code:
Sub test()
Dim myPath As String, NewName As String
myPath = ThisWorkbook.Path
With ActiveSheet
     NewName = .Name
     .Copy
End With
ActiveWorkbook.SaveAs myPath & "\" & NewName & ".xls"
End Sub
 
Upvote 0

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
518
Jindon-san,

I tried running the codes and it appear to work as it should be, but what surprises me is that the macro modules that were with the original workbook were not copied over as well. My primary objective is to ensure that those modules are copied over.
 
Upvote 0

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Then try
Code:
Sub test()
Dim myPath As String, NewName As String, ws As Worksheet
myPath = ThisWorkbook.Path
NewName = ActiveSheet.Name
Application.displayAlerts = False
For Each ws In Sheets
     If ws.Name <> NewName Then ws.Delete
Next
Thisworkbook.SaveAs myPath & NewName & ".xls"
End Sub
 
Upvote 0

Forum statistics

Threads
1,191,086
Messages
5,984,531
Members
439,895
Latest member
ddkexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top