VBA: Input current YYYY-MM(+1) into a specific cell

[larinda4]

Good morning,

I'm having a hard time trying to figure out a macro that will update the yyyy-mm(+1) in a specific cell. It needs to include the ' at the beginning as that year-month is used to update the table below it.

Example: It is currently September 2022 right now. I would need the macro to select cell C2, and update it to '2022-10

I found this code below that I was thinking maybe I could alter?

Sub TestDate()
   Dim dtToday As String
   dtToday = Format (Date, "yyyy mm")
End Sub

Any help would be appreciated!

 

[Joe4]

Try this:

Sub TestDate()
   Dim dtToday As String
   dtToday = Format(DateAdd("m", 1, Date), "yyyy-mm")
   Range("C2").Value = dtToday
End Sub

Note that this will put a Text value in cell C2, not a Date value.
If you actually need it to be a date (to use in calculations), you would NOT use the FORMAT function on it in VBA, and instead apply a custom date format to cell C2 directly.

 

[Flaiban]

Bom Dia,

Estou tendo dificuldade em tentar descobrir uma macro que atualizará o yyyy-mm(+1) em uma célula específica. Ele precisa incluir o ' no início, pois esse ano-mês é usado para atualizar a tabela abaixo dele.

Exemplo: atualmente é setembro de 2022 agora. Eu precisaria da macro para selecionar a célula C2 e atualizá-la para '2022-10

Encontrei este código abaixo que estava pensando que talvez pudesse alterar?

[CÓDIGO=vba]
Sub TestData()
Dim dtToday As String
dtToday = Formato (Data, "aaaa mm")
Finalizar Sub
[/CÓDIGO]

Qualquer ajuda seria apreciada!

Ou , caso prefira ir de acordo conforme a data atual em uma célula , no caso , =today() na célula A1 , segue um outro modelo :

Sub Next_Month_based_on_a_Date()
'declare a variable

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
'return the next month based on a specific date

ws.Range("C2") = Format(DateAdd("m", 1, ws.Range("A1")), "yyyy mm")

End Sub

 

[Joe4]

Ou , caso prefira ir de acordo conforme a data atual em uma célula , no caso , =today() na célula A1 , segue um outro modelo :

Sub Next_Month_based_on_a_Date()
'declare a variable

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
'return the next month based on a specific date

ws.Range("C2") = Format(DateAdd("m", 1, ws.Range("A1")), "yyyy mm")

End Sub

Welcome to the Board!

Please post in English when posting to the "Excel Questions" forum. You can use "Google Translate" to convert your response to English.
The only forum where posts in other languages should be posted is the "Questions in Other Languages" forum.

Thank you.
The Moderators


Portuguese translation:
Bem-vindo ao Conselho!

Por favor, poste em inglês ao postar no fórum "Perguntas do Excel". Você pode usar o "Google Translate" para converter sua resposta para o inglês.
O único fórum onde postagens em outros idiomas devem ser postados é o fórum "Perguntas em outros idiomas".

Obrigada. Os moderadores

 

[larinda4]

Try this:

Sub TestDate()
   Dim dtToday As String
   dtToday = Format(DateAdd("m", 1, Date), "yyyy-mm")
   Range("C2").Value = dtToday
End Sub

Note that this will put a Text value in cell C2, not a Date value.
If you actually need it to be a date (to use in calculations), you would NOT use the FORMAT function on it in VBA, and instead apply a custom date format to cell C2 directly.

It worked perfectly! Thank you so much, Joe. Have a good one!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[larinda4]

You are welcome.
Glad we were able to help!

Hey Joe, could I bug you again?

I have this part of my code:

ActiveSheet.Name = Format(Date + Day(Date), "mmmm yyyy" & "Final - By Region")

but the sheet name turns out like this "October 2022Fi0al - B287 Regio0"

Do you know what I did wrong?

 

[Joe4]

You want to have your Concatenation part OUTSIDE of the Format function, i.e.

ActiveSheet.Name = Format(Date + Day(Date), "mmmm yyyy") & "Final - By Region"

Not sure what you are trying to do by adding the "Day" to the Date though.
That is taking the current date, and adding the same number of days to it.
So today, it would be Sept 22, 2022 plus 22 days, which would get you to Oct 14, 2022.
But on October 1, it would be Oct 1, 2022 plus 1 day, which would get you to Oct 2, 2022.
So depending on the actual date, sometimes it will return the following month, and sometimes it won't.

What are you really trying to do there?

 

[larinda4]

You want to have your Concatenation part OUTSIDE of the Format function, i.e.

ActiveSheet.Name = Format(Date + Day(Date), "mmmm yyyy") & "Final - By Region"

Not sure what you are trying to do by adding the "Day" to the Date though.
What is the purpose of that?

Man, you're the best! Thank you so much.

In regards to the "Day", to be honest, I'm not entirely too sure. I found the code on another post in this forum but I was having a hard time locating it again to ask my question. So I posted it here instead.

Does the "Day" not need to be part of the code? It does what I need it to do so I would've never known it isn't required to be there.

 

[Joe4]

Man, you're the best! Thank you so much.

In regards to the "Day", to be honest, I'm not entirely too sure. I found the code on another post in this forum but I was having a hard time locating it again to ask my question. So I posted it here instead.

Does the "Day" not need to be part of the code? It does what I need it to do so I would've never known it isn't required to be there.

It really depends on what it is you are trying to do.
If this follows you original question, and you ALWAYS want the following month, then use the logic from the line of code I gave you originally, i.e.

ActiveSheet.Name = Format(DateAdd("m", 1, Date), "yyyy mm") & "Final - By Region"