Wildcard character for vba

[dpaton05]

I have a spreadsheet that has a button to create a new document for a new financial year. There is a text box where the new financial year will be entered in. I did have it so the new document could be created but only for the following financial year to the current one. Then my supervisor told me he might want to create the document and it may be for any given year, not just the subsequent. I started to change my code but then got stuck. This is the first part of my code that works:

Private Sub cmdNewYear_Click()

Dim newDoc As String
Dim NewDocYear As Integer


    'Worksheets("home").Unprotect Password:="costings"
    NewDocYear = txtYear.Value
    newDoc = "NPSS work allocation sheet " & NewDocYear & " - " & NewDocYear + 1 & ".xlsm"

    'ActiveWorkbook.Save
    ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & newDoc
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & newDoc
    'Workbooks("NPSS work allocation sheet " & year(Now) + 1 &  ".xlsm").Worksheets("home").Unprotect Password:="costings"
    
    With Workbooks("NPSS work allocation sheet " & NewDocYear & " - " & NewDocYear + 1 & ".xlsm").Sheets("home")
        .Range("A18") = "July " & NewDocYear
        .Range("A19") = "August " & NewDocYear
        .Range("A20") = "September " & NewDocYear
        .Range("A21") = "October " & NewDocYear
        .Range("A22") = "November " & NewDocYear
        .Range("A23") = "December " & NewDocYear
        .Range("E18") = "January " & NewDocYear + 1
        .Range("E19") = "February " & NewDocYear + 1
        .Range("E20") = "March " & NewDocYear + 1
        .Range("E21") = "April " & NewDocYear + 1
        .Range("E22") = "May " & NewDocYear + 1
        .Range("E23") = "June " & NewDocYear + 1
    End With

I didn't know how to get the sheet names to change to have the new year reflected. Here is part of the rest of my code:

      With Workbooks(newDoc)
        .Sheets("July " & NewDocYear).Name = "July " & year(Now)
            With Sheets("July " & year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "July " & year(Now)
            End With
            
        .Sheets("August " & Range("D1")).Name = "August " & year(Now)
            With Sheets("August " & year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "August " & year(Now)
            End With

I tried changing the NewYearDoc but it wouldn't work. The second part, relating the the august sheet, is how it used to be.


D1 had this formula

=YEAR(TODAY())-1

How do I get the sheet names to change from the current year in the document to the new year. It could be created from any year document.

Thanks,
Dave

 

[dpaton05]

I don't need this anymore.