Results 1 to 4 of 4

Thread: Microsoft excel formula or macro

  1. #1
    Member
    Join Date
    Jan 2008
    Posts
    353
    Thanks
    7
    Thanked 11 Times in 9 Posts
    Rep Power
    211
    Reputation
    140

    Default Microsoft excel formula or macro

    His. Not sure if what I want to do can be done or not.

    Here is the situation.

    An excel 2016 spreadsheet/workbook is e-mailed from our accounts department on a weekly basis which has around 150 to 200 tabs on the bottom each one uniquely names. .
    Out of all those worksheets I only need 15 of them and they are randomly placed along the bottom.

    If like to run a formula or macro to select all worksheets I require and delete the rest.

    Is this possible and if so, what would be the best way to do it?

    Sent from my SM-G935F using Tapatalk



Look Here ->
  • #2
    Premium Member
    Skepticist's Avatar
    Join Date
    Apr 2009
    Posts
    1,139
    Thanks
    714
    Thanked 670 Times in 525 Posts
    Rep Power
    474
    Reputation
    12780

    Default

    Maybe start recording a macro then go through all the sheets you don't want to see and individually 'hide' them
    Provided the names are always the same, all you'd need to do is run that macro each time
    If the names vary based on date or whatever you'll need to add some more smarts to the code

  • #3
    Premium Member
    Al Bundy's Avatar
    Join Date
    Jan 2008
    Location
    Tazzie
    Posts
    4,470
    Thanks
    2,002
    Thanked 5,178 Times in 1,899 Posts
    Rep Power
    2198
    Reputation
    92550

    Default

    Quote Originally Posted by slick1 View Post
    His. Not sure if what I want to do can be done or not.

    Here is the situation.

    An excel 2016 spreadsheet/workbook is e-mailed from our accounts department on a weekly basis which has around 150 to 200 tabs on the bottom each one uniquely names. .
    Out of all those worksheets I only need 15 of them and they are randomly placed along the bottom.

    If like to run a formula or macro to select all worksheets I require and delete the rest.

    Is this possible and if so, what would be the best way to do it?

    Sent from my SM-G935F using Tapatalk
    As Skepticist has suggested, if the names of the Tabs are always the same record a macro, things to be aware of would be do the pages you want to keep have links to pages you delete? If not then I would suggest actually selecting the Tabs you want to keep, and copy them to a new Workbook, you would need to save the VBA module with recorded macro and load that module into the weekly workbook when you get it, then execute it.
    Cheers
    Ted (Al)

  • #4
    Premium Member
    Skepticist's Avatar
    Join Date
    Apr 2009
    Posts
    1,139
    Thanks
    714
    Thanked 670 Times in 525 Posts
    Rep Power
    474
    Reputation
    12780

    Default

    Those unwanted sheets would almost certainly be linking to cells in other sheets so hiding them preserves the integrity whereas deleting the sheets would result in quite a mess.
    IE All the data would still be intact

    To delete the unwanted sheets you'd need to go through the 'wanted' sheets and individually copy, paste as values only to get 'frozen' data with no formulae before deleting the unwanted sheets. Quite a bit more work but it has the advantage of considerably shrinking the file (I use that method to create archival copies of workbooks sometimes).

  • Bookmarks

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •