CODEWITHSUNDEEP
excelvba
Alexander
Alexander

Reputation: 41

Macro to automate the next month and year based on previous sheet

I'm working on a macro tasked to copy & paste a hidden sheet, enter month and year in a specific cell (based on entered value from previous sheet. I'm currently having issues doing this as the cell from which I obtain (read) my data from contains a month and a year. I have this code so far (given to me by a very helpful community member of stackoverflow):

NextMonth = Format(DateSerial(Year(Date), Month(DateValue("1/" & Range("A1") & "/" & Year(Date))) + 1, 1), "mmmm")

The code works fine if A1 only contains a month. Any ideas how to read in, for example, only "February" although the cell contain "February 2019" and use it with the formula above to get March? With it, I will store the month in a variable "A" and store the year using " B = Year(Now())" in another one. Finally I will assign it to a cell on the desired sheet with " = A & B"

Any thoughts? Regards, Alex

Notice: Code the current (actual) month is not of interest. Also, I have the code to automate the first sheet where the code asks the user for input of the month and years.

Upvotes: 2

Views: 955

Answers (1)

Tim Stack
Tim Stack

Reputation: 3248

The code below allows :
1. the month number
2. the month spelled out
2. a combination of the two points above

In other words, the code would accept the following cell values

  • "February" and "February 2019"
  • "1" and "1 2019"
Sub newmonth()
Dim datecell As Range
Dim d As Variant
Dim newdate As Date

Set datecell = Sheet1.Range("D21")

If IsDate(datecell) Then
    d = datecell
Else
    d = datecell & " " & Year(Now())
End If

newdate = DateAdd("m", 1, d)

Debug.Print Format(newdate, "mmmm yyyy")
'Alternatively, you could put this value in another cell
'Sheet2.Range("A1").Value = Format(newdate, "mmmm yyyy")

End Sub

Make sure you tweak the references so that they match your workbook configuration

Upvotes: 2

Related Questions