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.
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.
This worked absolutely perfectly. Thank you so much; you have no idea how much time this has saved me!Originally Posted by Mumps1
Try:
Please Login or Register to view this content.
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:You are very welcome.
What am I missing? Thank you again for your time and knowledge!Please Login or Register to view this content.
Try:
Please Login or Register to view this content.
Administrative Note:
Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks
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)"
When I tried the macro on some dummy sheets, it worked properly. Can you attach a copy of your file (de-sensitized if necessary)?
Attached for your review. I put 20-ish worksheets on that one, but the full file has 370. Thanks so much for the help!
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?
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"
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.
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!
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)?My pleasure.
Thank you!
Are all the cells in rows 2 and 3 blank? Do you want me to modify the macro from Post #2 or Post #13?
@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”?
A3 actually contains the text 'Line #'
Do you need the text of all of the cells/headings in Column A?
Also, if it would be helpful I can post a blank copy of the file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks