Wildcard character for vba

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,699
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:

Code:
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:

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
Code:
=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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top