Code to create a copy of the workbook I'm in with a new name

[WStockel]

I want to make a copy of the workbook I'm in to a new workbook.
Then save this new workbook in the same unknown location that the original workbook is in.
I want to name the new workbook "NASCAR " & worksheet("Menu").cells(1,1).value in the old workbook.
Then I want to clear some ranges of cells on a few of the worksheets in the new workbook.
Then open the new workbook and close the old workbook.
I'm not sure all this is possible.

 

[WStockel]

So I figured out how to make the new workbook with the file name I wanted.
But how do I clear some sheets in the new workbook from a macro in the old workbook?

Dim sFullName As String ' worksheet
Dim nShtName As String

nShtName = "\" & "NASCAR " & CBYear.Text & ".xls"
sFullName = ThisWorkbook.Path & nShtName
ActiveWorkbook.SaveCopyAs sFullName


This is the worksheet I want to clear but I want to do it in the new workbook
Worksheets("Players").Range("A2:D10").Clear

 

[WStockel]

I figured it out.

    Set wb = Workbooks.Open(nShtName)
    
    ' update header on the Menu sheet with new year
    wb.Worksheets("Menu").Cells(1, 1).Value = CBYear.Text ' header on Menu sheet with new year
    
    ' Week number to 1
    wb.Worksheets("Menu").Cells(6, 4).Value = 1
    
    ' erase players page
    wb.Worksheets("Players").Range("A2:E41").Clear