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

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
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
 

g_kerkelov

New Member
Joined
Jun 19, 2013
Messages
16

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
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

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,940
Messages
5,621,728
Members
415,853
Latest member
Newlife72

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
Top