• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Saving only specific tab on a multi-tab work book in a Macro

ireland13752

ireland13752

Member
Hello, I need to save selected tabs to a new file and then when finished delete those tabs. I have it all worked out except it is not saving only the tabs I have highlighted it is saving all tabs remaining in the file. Can you please assist me? Below is the code that I am using to save the tabs and then delete them.


Code: 
 Sheets(Array("DataD", "DCT")).Select
    Sheets("DataD").Activate
    ActiveSheet.SaveAs FileFormat:=xlWorkbookDefault, Filename:=Application.GetSaveAsFilename("DCT Weekly Report Review MM-DD-YY.xlsx")
    Sheets(Array("DataD", "DCT")).Select
    ActiveWindow.SelectedSheets.Delete
    
    Sheets(Array("DataE", "EGH")).Select
    Sheets("DataE").Activate
    ActiveSheet.SaveAs FileFormat:=xlWorkbookDefault, Filename:=Application.GetSaveAsFilename("EGH Weekly Report Review MM-DD-YY.xlsx")
    Sheets(Array("DataE", "EGH")).Select
    ActiveWindow.SelectedSheets.Delete
    
    Sheets(Array("DataS", "SGH")).Select
    Sheets("DataS").Activate
    ActiveSheet.SaveAs FileFormat:=xlWorkbookDefault, Filename:=Application.GetSaveAsFilename("SGH Weekly Report Review MM-DD-YY.xlsx")
    Sheets(Array("DataS", "SGH")).Select
    ActiveWindow.SelectedSheets.Delete
   
    Sheets(Array("DataP", "PRMC")).Select
    Sheets("DataP").Activate
    ActiveSheet.SaveAs FileFormat:=xlWorkbookDefault, Filename:=Application.GetSaveAsFilename("PRMC Weekly Report Review MM-DD-YY.xlsx")
    Sheets(Array("DataP", "PRMC")).Select
    ActiveWindow.SelectedSheets.Delete
    
    Sheets(Array("DataL", "LCMC")).Select
    Sheets("DataL").Activate
    ActiveSheet.SaveAs FileFormat:=xlWorkbookDefault, Filename:=Application.GetSaveAsFilename("LCMC Weekly Report Review MM-DD-YY.xlsx")
    Sheets(Array("DataL", "LCMC")).Select
    ActiveWindow.SelectedSheets.Delete
    
    Sheets(Array("Review Summary", "Data")).Select
    Sheets("Review Summary").Activate
    ActiveSheet.SaveAs FileFormat:=xlWorkbookDefault, Filename:=Application.GetSaveAsFilename("SL Weekly Report Review MM-DD-YY.xlsx")
 
If you have perfectly all sheets present in the workbook and no need to check any validation for "Sheet Name Not Exist" / "File already exits in the dest location" then, you can test below one too..

Code: 
Sheets(array("DataD", "DCT")).move
Activeworkbook.SaveAs Thisworkbook.Path & "\DCT Weekly Report Review MM-DD-YY.xlsx"
Activeworkbook.close False

If your worksheets are arrange in a manner, then you can use loops also, which will avoid to write above code 6 times for each block of sheet.
 
Last edited:
Hi Debra, thank you for responding. I am sorry, I do not understand what you are saying. "If you have perfectly all sheets present in the workbook and no need to check any validation for "Sheet Name Not Exist" / "File already exits in the dest location" then, you can test below one too.." My workbook has 14 tabs when it starts and then as it saves the two, it then deletes them and then continues until there are only 4 left... What I need it to do is to save the two that are highlighted and ignore the others. Currently it is not doing that, it is saving all 14 and then deleteing the two highlighted then saving 12 ... so and so forth...

With the code you have listed above
Code (vb):
Sheets(array("DataD", "DCT")).move
Activeworkbook.SaveAs Thisworkbook.Path & "\DCT Weekly Report Review MM-DD-YY.xlsx"
Activeworkbook.close False

Will this only work if the sheets are already in a specific folder? If so, that won't work because they are saved each week with a new name...
 
You must log in or register to reply here.
Back
Top