+ Reply to Thread
Results 1 to 21 of 21

Format all worksheets in workbook as a table

  1. #1
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Question Format all worksheets in workbook as a table

    I have a workbook that has ~300 worksheets in it. They are all formatted exactly the same as far as layout goes, and I am trying to figure out a way to format all of the worksheets as a table at once, instead of clicking on every single worksheet and selecting "Format as Table". The format as table option becomes greyed-out when I select all of the worksheets at once, so I cannot accomplish it that way. Any help is greatly appreciated.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    Quote Originally Posted by Mumps1 View Post
    Try:
    Please Login or Register  to view this content.
    This worked absolutely perfectly. Thank you so much; you have no idea how much time this has saved me!

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    You are very welcome.

  5. #5
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    Quote Originally Posted by Mumps1 View Post
    You are very welcome.
    I am hoping you might be able to point me in the right direction on one more thing related to this. I am now trying to sort all of the tables at once by the same column across the board. I have tried the following but it gives me a debug error:

    Please Login or Register  to view this content.
    What am I missing? Thank you again for your time and knowledge!

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    Try:
    Please Login or Register  to view this content.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,139

    Re: Format all worksheets in workbook as a table

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

  8. #8
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    Mumps, thanks so much for that and please pardon my delay in replying. When I run that I get, "Run-time error '1004': A table can't overlap another table." and the debug highlights the line "Set tbl = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)"

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    When I tried the macro on some dummy sheets, it worked properly. Can you attach a copy of your file (de-sensitized if necessary)?

  10. #10
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    Attached for your review. I put 20-ish worksheets on that one, but the full file has 370. Thanks so much for the help!
    Attached Files Attached Files

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    In the file you posted, the data in all the sheets is already in table form. Can you post a copy of your file before the data is changed to tables?

  12. #12
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    I am sorry for the confusion and I see my error now... I had thought that the second one you had posted was for sorting all of the worksheets the same way AFTER they had been converted to tables. If I try to run it against the original file (attached) I get "Sort method of Range class failed"
    Attached Files Attached Files

  13. #13
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    This macro worked as you requested on the last file you posted. I modified it slightly to exclude Sheet1 since it was blank. If you run the macro a second time, you will get an error because the data has already been changed to a table.
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    That worked perfectly! I sincerely appreciate your time. I really need to get better at learning these capabilities so I can make better use of Excel when I have these massive files. Thank you!

  15. #15
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    My pleasure.

  16. #16
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    Quote Originally Posted by Mumps1 View Post
    My pleasure.
    Sorry to resurrect a bit of an old thread, but would you be able to tell me how to edit the first script from Post #2 in this thread so that it only creates tables for a worksheet(s) whose headers/data start in row 3/4 (Headers/Titles 3, Data 4)?

    Thank you!

  17. #17
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Format all worksheets in workbook as a table

    Are all the cells in rows 2 and 3 blank? Do you want me to modify the macro from Post #2 or Post #13?

  18. #18
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    @Mumps1 the cells in rows 1 and 2 are the title/header of the worksheet, row 3 contains the name of each column (Last Name, First name, etc), line 4 is an example line for filling in the worksheet, and the actual live data starts on line 5. If you could modify the macro from Post #2 that would be fantastic! I really appreciate your time!

  19. #19
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822
    Quote Originally Posted by 7684010035 View Post
    @Mumps1 the cells in rows 1 and 2 are the title/header of the worksheet, row 3 contains the name of each column (Last Name, First name, etc), line 4 is an example line for filling in the worksheet, and the actual live data starts on line 5. If you could modify the macro from Post #2 that would be fantastic! I really appreciate your time!
    Does cell A3 contain the text “Last Name”?

  20. #20
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    A3 actually contains the text 'Line #'

    Do you need the text of all of the cells/headings in Column A?

  21. #21
    Registered User
    Join Date
    02-06-2020
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    13

    Re: Format all worksheets in workbook as a table

    Also, if it would be helpful I can post a blank copy of the file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lookup value on multiple worksheets on different workbook and format cell if present
    By jamesmullens in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-14-2017, 11:31 AM
  2. Replies: 3
    Last Post: 08-15-2015, 05:26 AM
  3. [SOLVED] Loop through all worksheets and format data found as table
    By jvr2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2014, 09:52 PM
  4. [SOLVED] Consolidate selected worksheets into new one and format as table
    By Mad-Dog in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-17-2014, 02:21 AM
  5. Macro to format all worksheets and then print workbook
    By Student1990 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2014, 01:15 AM
  6. [SOLVED] HOW DO I FORMAT WORKSHEETS IN A WORKBOOK TO NUMBER IN A SEQUENCE?
    By Lee S in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2006, 12:25 AM
  7. [SOLVED] How do I format all worksheets in a workbook at one time ?
    By EBrock in forum Excel General
    Replies: 3
    Last Post: 02-09-2005, 02:09 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1