+ Reply to Thread
Results 1 to 5 of 5

Deleting worksheets, printing to Adobe, and moving to a new workbook

  1. #1
    Registered User
    Join Date
    02-21-2008
    Posts
    9

    Deleting worksheets, printing to Adobe, and moving to a new workbook

    Hi,

    I am a n00b with programming VBA for Excel. There are three issues that I seem to be having with my code. I am writing code that will ultimately create multiple worksheet graphs at the click of a button to save time in the future.

    (1)

    My first issue is deleting worksheets that are ALREADY in existence in the workbook. These worksheets have been created earlier by the code I wrote. I have triple checked and the worksheet names are not misspelled. I wrote this code so that if there is a problem with any of the graphs, the values can be changed and then the graphs be remade. Here is the code:

    Please Login or Register  to view this content.
    I get the error:
    Run-time error '9':
    Subscript out of range


    ... with the line "Worksheets("3.1 - Blender Speed").Delete" highlighted yellow during debug. An "ActiveX" button is used to initiate the code from the worksheet called "3.0 - Surface - Emulsion".

    (2)

    My second issue is more or less one of no information. I read up on how to print files off. However, I was wondering if it is possible to have the VBA code print the file as an Adobe file, printing off every worksheet at once. Also, when printing the file as an adobe file (by selecting "Adobe PDF" as the printer, as opposed to selecting "print to file") is it possible to choose "Adobe PDF Document Properties" and change the "Default Settings" of the "Adobe PDF Settings" tab all using the VBA code?

    (3)

    My final issue seems to be documented well, but I cannot seem to get it to work. I am attempting to use the following code to move specific worksheets (the active one) to a NEW workbook (order doesn't matter as I can use code to sort them when they have all been added):

    Please Login or Register  to view this content.
    Where Book1.xlsx is the default "new", yet uncreated and unopened workbook I wish to send the newly created worksheets to. Also, is it possible to just create all of my charts and then have them sent to the new workbook at once? Once again, the charts are not embedded and take their own worksheet up.

    Thanks to everyone for their time and help.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    1. Is the workbook that contains those sheets active when the problematic line executes?

    2. Probably. I expect you'd need to set a reference to Acrobat (there are several libraries) and then manipulate its object model.

    3. Just
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello baconcow,

    With regard to issue (1), the worksheets are most likely Chart Sheets. You must use Sheets(<sheet name>).Delete to remove them. Chart sheets are not part of the Worksheets object collection.

    Sincerely,
    Leith Ross

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    Good catch, Leith.

  5. #5
    Registered User
    Join Date
    02-21-2008
    Posts
    9
    Thank you very much, shg and Leith Ross.

    Using both;
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    worked flawlessly for my issues!

    I did not realize the difference between a regular worksheet and a chart sheet. To me, they both appear as worksheet tabs in Excel so I thought they would be recognized as the same.

    With regards to the Adobe PDF aspect, it is not extremely important at this time that I put it into the program. I will look further into this, though.

    Thanks again for both of your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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