+ Reply to Thread
Results 1 to 15 of 15

Error in Code to Sort, Hide, and PDF Worksheets

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Error in Code to Sort, Hide, and PDF Worksheets

    Hi,

    I'm currently working with the following code to reorder my Excel worksheets based on a list (of certain worksheet names) in a specified range (in this case "WorksheetNames"). Once the worksheets in that range are sorted, the next function hides the remaining worksheets based on a similar list (in this case, the range "Hidden"). Finally, the code should PDF the newly reordered worksheets only.

    QUESTION: This code works (sorts the worksheets, and hides the others, but doesn't yet PDF), but is currently returning a "Select method of Worksheet class failed" error (I believe error 400?), but I'm not quite sure what the issue could be. I'm fairly new to VBA, and have pieced the code together from various sources, so any help in getting it to work properly would be greatly appreciated. Thanks!


    Please Login or Register  to view this content.
    Last edited by mbhart; 04-04-2014 at 12:50 PM.

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    mbhart,

    Welcome to the forum. Please take a moment to read the forum rules: http://www.excelforum.com/forum-rule...rum-rules.html

    Particularly rule #3 which pertains to code tags.

    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    ... and appear like this when posted:




    Please Login or Register  to view this content.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Ah, thanks so much. Apologies for the oversight:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    e/ I didn't error in that location.

    On what line does debug highlight for you?
    Last edited by Solus Rankin; 04-04-2014 at 01:07 PM.

  5. #5
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Thanks so much for the response. Unfortunately, when I try this, it then generates the MsgBox with the name of all the worksheets. Any other thoughts? I really appreciate your help.

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Please attach your workbook as an example showing the order you want the sheets sorted, and the sheets that are supposed to be hidden.

    To attach a workbook, click the 'Go Advanced' button below and use the paperclip in the toolbar to navigate to your document.

  7. #7
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Test.xlsm

    Sorry for the delay, I needed to create a cleaned version of the workbook. I'm not quite getting the same errors in this workbook, but the general questions regarding the code apply. When you run the code, it will give you an error message about the sheet names of the sheets that have been reordered. Ideally, I'd like for this workbook to sort the worksheets in column K of the "appendices" worksheet (also referred to as named range "WorksheetNames"). Then, it should move on to hide the worksheets listed in column N (named range "Hidden"). Finally, once the tabs are in order and all others are hidden, it should send the workbook to PDF (only the visible worksheets). Any help with this would be greatly appreciated. It's close to working.

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Are all the sheets supposed to be "unhidden" afterwards?
    Last edited by Solus Rankin; 04-04-2014 at 03:18 PM.

  9. #9
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Nope, they don't need to be unhidden afterwards.

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Please Login or Register  to view this content.
    Last edited by Solus Rankin; 04-04-2014 at 03:50 PM.

  11. #11
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    If the sheets only have to be hidden on the pdf and not the excel file this you can do it with less loops:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Hmmm, when I run the script, it moves Sheet6 to the front position, but then gives the error "Subscript out of range". Any idea why this might occur?

  13. #13
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Because you're missing sheet9. (I had to add it into your example workbook). I edited the code in post number 10. Try it after adding in sheet9.

  14. #14
    Registered User
    Join Date
    04-02-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    This is perfect. It works great. Now all I need to do is fit the script to a separate workbook with different tabs. Thank you soooo much for all your help with this. I really appreciate it!

  15. #15
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Error in Code to Sort, Hide, and PDF Worksheets

    Glad to help. And welcome to the forum.

    If you are happy with the solution please mark the thread as [SOVLED] using thread tools at the top.

    And on any thread you can show your appreciation to those who have helped you by clicking the * below their post.

+ 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. Help with VBA Code (Hide/Unhide Worksheets)
    By maryren in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2014, 12:43 PM
  2. Help me sort this code layout error! Missing inverted comma!
    By ropbasuel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2013, 12:31 AM
  3. [SOLVED] VBA code to sort data either within one large worksheet or across several worksheets
    By miss_georgina in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2012, 04:52 PM
  4. Excel 2007 Code to Hide/Protect Worksheets
    By mab in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2010, 03:54 PM
  5. [SOLVED] Code to hide worksheets based on Active Directory login?
    By Joe in forum Excel General
    Replies: 0
    Last Post: 02-21-2006, 12:20 AM

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