VBA - Value and current date input

[g_kerkelov]

Hi all,

I`ve checked some forum threads on the topic but could not find one that exactly answers my questions, hence the new post.
Basically, looking to create script to open a worksheet from My Documents path. Then change values in cells in a pre-populated worksheet (mostly strings) including replacing a date value ( in "27081983" format) and appending another three digit number to it.
At the end would look to save it as a new sheet. Any ideas how to start constructing it?

 

[Norie]

Do you want to create a new workbook based on the workbook you are opening but with certain values changed and leaving the original workbook as is?

 

[g_kerkelov]

Do you want to create a new workbook based on the workbook you are opening but with certain values changed and leaving the original workbook as is?

Yes, exactly. Sorry should have clarified this.

 

[Norie]

What you could do is convert the 'base' workbook to a template (xltm) , then instead of opening it you create a new workbook based on it.

The code for that would look something like this and would be in another workbook.

Sub CreateNewWB()
Dim wbNew As Workbook
Dim strTemplatePath As String

    strTemplatePath = "C:\My Documents\MyTemplate.xltm"
    
    wbNew = Workbooks.Add(strTemplatePath)
    
    ' change whatever values are needed in the new workbook
    
    ' example, put current data in A1 on Sheet1
    wbNew.Sheets("Sheet1").Range("A1").Value = Date
    
    wbNew.SaveAs
    
    ' close workbook without saving as we saved it in the previoust step
    wbNew.Close SaveChanges:=False
    
End Sub

 

[g_kerkelov]

What you could do is convert the 'base' workbook to a template (xltm) , then instead of opening it you create a new workbook based on it.

The code for that would look something like this and would be in another workbook.

Sub CreateNewWB()
Dim wbNew As Workbook
Dim strTemplatePath As String

    strTemplatePath = "C:\My Documents\MyTemplate.xltm"
   
    wbNew = Workbooks.Add(strTemplatePath)
   
    ' change whatever values are needed in the new workbook
   
    ' example, put current data in A1 on Sheet1
    wbNew.Sheets("Sheet1").Range("A1").Value = Date
   
    wbNew.SaveAs
   
    ' close workbook without saving as we saved it in the previoust step
    wbNew.Close SaveChanges:=False
   
End Sub

Thank you, but where do you define the current date variable with the appended value of three digits.
Also, is this code supposed to go in the 'base' workbook or in the new one?

 

[Norie]

This code would go in another workbook, not the base workbook or the new one.

As for the 3 digit number, that could come from the workbook the code is in.

 

[Dossfm0q]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, TxT As String, T As Long

If Not Intersect(Target, Range("A13:C20")) Is Nothing Then
    N = 0
    For Each Rng In Target
        If Rng.Value <> "" And Rng.Column = 1 Then
            TxT = Rng.Value
            For T = 1 To Len(TxT)
            N = N + 1
                If IsNumeric((Mid(TxT, T, 1))) = True Then
                Mnth = Format(Mid(TxT, N, Len(TxT) - N + 1), "dd/mm")
                If InStr(1, Mnth, "-") = 0 Then
                NxtMnth = Format(DateSerial(Year(Mnth), Month(Mnth) + 1, Day(Mnth)), "dd mmm")
                Rng = TxT & " - " & NxtMnth
                End If
                Exit For
                Else
                
                End If
            Next
        End If
    Next
End If

End Sub

Book1
	A	B	C	D
1
2
3			Date	January 27, 2021
4				27012021/###
5
6
7
8
9
10
11
12	Descriptiot			Amount
13	Operators Lience Management Fee 23 Dec - 23 Jan
14	Operators Lience Management Fee 15 Oct - 15 Nov
15	Operators Lience Management Fee 18 Jan - 18 Feb
16	Operators Lience Management Fee 03 Mar - 03 Apr
17	Operators Lience Management Fee 10 Apr - 04 Nov
Sheet1

Cell Formulas
Range	Formula
D4	D4	=TEXT(D3,"ddmmyyyy")&"/"&"###"

 

[g_kerkelov]

This code would go in another workbook, not the base workbook or the new one.

As for the 3 digit number, that could come from the workbook the code is in.

Thank you will test it a bit later.

 

[g_kerkelov]

thanks both @Dossfm0q and Norie for your contributions. I supposed i can combine your scripts to get exactly what i wanted.