VBA - Value and current date input

g_kerkelov

New Member
Joined
Jun 19, 2013
Messages
16
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?
 

Attachments

  • Capture.PNG
    Capture.PNG
    40.2 KB · Views: 12

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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?
 

Attachments

  • Capture 1.PNG
    Capture 1.PNG
    11.7 KB · Views: 9
Upvote 0
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.
 
Upvote 0
VBA Code:
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
ABCD
1
2
3DateJanuary 27, 2021
427012021/###
5
6
7
8
9
10
11
12DescriptiotAmount
13Operators Lience Management Fee 23 Dec - 23 Jan
14Operators Lience Management Fee 15 Oct - 15 Nov
15Operators Lience Management Fee 18 Jan - 18 Feb
16Operators Lience Management Fee 03 Mar - 03 Apr
17Operators Lience Management Fee 10 Apr - 04 Nov
Sheet1
Cell Formulas
RangeFormula
D4D4=TEXT(D3,"ddmmyyyy")&"/"&"###"

Value and current date input.gif
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top