+ Reply to Thread
Results 1 to 7 of 7

Opening Template as new workbook and not Blank Workbook from listbox

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Opening Template as new workbook and not Blank Workbook from listbox

    The code below allows me to take items from a list and open them in a New Blank Workbook on New sheet and place the items from list box in range A. The New workbook name Saves as my listbox header, which is perfect. ...PROBLEM IS: I would like this new workbook to open not from a blank new book but from a Template Book....Am at a loss on how to do this! Thanks for any help!

    Please Login or Register  to view this content.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Opening Template as new workbook and not Blank Workbook from listbox

    Quote Originally Posted by MB_vba View Post
    The code below allows me to take items from a list and open them in a New Blank Workbook on New sheet
    The code below does not open a new blank workbook, only a new blank worksheet in the existing workbook. Opening a template as a new workbook is a different problem than copying a template to a new worksheet in and existing workbook.

    Can you please clarify what you need to do?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Opening Template as new workbook and not Blank Workbook from listbox

    hey Jazzer,

    Thanks for coming back to me...

    Um,not quite sure what you mean as I assure you it opens, creates, sends info, and then closes a new workbook...Anyway, that's not really the issue, let me clarify my question.

    From a VBA listBox in WorsksheetA on workbookA, I select one or more items in my drop down list. When I hit the command button, the selected items are then sent to a worksheetD( on a new workbook B(this workbook has never been created), in the next available columns. The new workbook name is a variant from a comboBox. The new workbook comes from a template, where there are 5 sheets and certain vbas already assigned...

    I hope this clears up my question. Thanks for any input!

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Opening Template as new workbook and not Blank Workbook from listbox

    It sounds like your code creates a new workbook, but you want it to use a template instead. The way to do that is to open the template, instead of creating a new workbook, then immediately do a SaveAs with the desired name/path. Then everything else is the same. Then you Save again before closing.

    However, I can't reconcile that with the code. If you are creating a new workbook, you're not doing it in the code below; see my notes in green. Do you have other code as well?

    (I am still unclear on your overall problem because you said, "...sent to a worksheetD( on a new workbook B(this workbook has never been created)...". I don't understand how you can do anything in a workbook that has never been created.)

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Opening Template as new workbook and not Blank Workbook from listbox

    Seems to me you are creating a new worksheet, wsNew in the current workbook.

    You then add the data from the listbox to the new worksheet.

    Then you have this which creates a new workbook with just that worksheet.
    Please Login or Register  to view this content.
    If you want to create a new workbook from a template.
    Please Login or Register  to view this content.
    Last edited by Norie; 01-21-2013 at 01:41 PM.
    If posting code please use code tags, see here.

  6. #6
    Registered User
    Join Date
    01-17-2013
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Opening Template as new workbook and not Blank Workbook from listbox

    Hey Jazzer,

    I see what you're saying about previous code. NORIE, thjanks, I have amended my code to the following:


    Please Login or Register  to view this content.
    I see what you guys are saying ... I believe the code above is getting there, but now I am now getting a subscript out of range error...I believe that it can't find

    Please Login or Register  to view this content.
    Where Am I going wrong here? Thanks for your input!
    Last edited by MB_vba; 01-21-2013 at 04:16 PM.

  7. #7
    Registered User
    Join Date
    01-17-2013
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Opening Template as new workbook and not Blank Workbook from listbox

    OK, nevermind, figured it out:

    Please Login or Register  to view this content.
    This was the proper code needed...Thanks to both of you!!

+ 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