Opening Template as new workbook and not Blank Workbook from listbox

Originally Posted by MB_vba

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?

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.)

Private Sub btn_StartNewSeason_Click()
Dim wbNew As Workbook
Dim wsNew As Worksheet
Dim Rng As Range
Dim i As Long
Dim J As Long

    Set wsNew = ThisWorkbook.Worksheets.Add ' This line adds a new worksheet to ThisWorkbook, which is the workbook containing this code
                                            ' There is no new workbook created here.
    wsNew.Name = "Client List"
    Set Rng = wsNew.Range("A1")
    For i = 0 To lstBrandsNewSeason.ListCount - 1
        For J = 0 To lstBrandsNewSeason.ColumnCount - 1
            Rng.Offset(i, J).Value = lstBrandsNewSeason.List(i, J)
        Next J
    Next i
    wsNew.Move
    Set wbNew = ActiveWorkbook ' This does not create or open a workbook. ActiveWorkbook could be the same
                               ' as ThisWorkbook, or it could be a different one. Not possible to tell from this code,
                               ' it depends on what was going on when this code is called.
                               ' (That's why I discourage the use of ActiveWorkbook unless it's a general purpose macro
                               '  called by the user to apply to the currently active workbook)
    wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & txtNewSeasonName.Value
    Unload Me
End Sub

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.

wsNew.Move

If you want to create a new workbook from a template.

Set wbNew = Workbooks.Add("C:\TheTemplate.xlsm")

    ' code to add data from listbox to sheet in new workbook

   ' code to save new workbook

Hey Jazzer,

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

Private Sub btn_StartNewSeason_Click()

Dim wbNew As Workbook
Dim wsNew As Worksheet
Dim Rng As Range
Dim i As Long
Dim J As Long

    Set wbNew = Workbooks.Add("C:\templateseason.xlsm")
    Set wsNew = Workbooks("templateseason.xlsm").Worksheets("Client List")
    Set Rng = wsNew.Range("A1")
    For i = 0 To lstBrandsNewSeason.ListCount - 1
        For J = 0 To lstBrandsNewSeason.ColumnCount - 1
            Rng.Offset(i, J).Value = lstBrandsNewSeason.List(i, J)
        Next J
    Next i
    
    wbNew.SaveAs Filename:="C:\ ....\"& txtNewSeasonName.Value
    wbNew.Close
    Unload Me
End Sub

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

Set wsNew = Workbooks("templateseason.xlsm").Worksheets("Client List")

Where Am I going wrong here? Thanks for your input!