Want2BExcel
Board Regular
- Joined
- Nov 24, 2021
- Messages
- 112
- Office Version
- 2016
- Platform
- Windows
I'm trying to figure out how to only use part of the string to rename sheets. I have this but the result is January2022. I want it to be Jan.22. Can someone help me to figure it out...
Sub Copy_3MdrTemplate_Sheet()
Dim sh As Worksheet
Dim answer As Integer
s = [ScMonth]
y = [ScYear]
Sub Copy_3MdrTemplate_Sheet()
Dim sh As Worksheet
Dim answer As Integer
s = [ScMonth]
y = [ScYear]
VBA Code:
answer = MsgBox("BEMÆRK! Du vil ikke kunne ændre måned på det nye ark" & vbCrLf & "Er måned og år, som du ønsker?" & vbCrLf & "Er du sikker på du ønsker at forsætte?", vbQuestion + vbYesNo, "Opret nyt månedsark")
If answer = vbYes Then
Else: Exit Sub
End If
On Error Resume Next
'check sheet exists already
Set sh = Sheets(s & y)
On Error GoTo 0
If Not sh Is Nothing Then MsgBox "Den måned findes allerede. Prøv igen", vbCritical: Exit Sub
'copy your template before that sheet
Sheets("Template").Copy before:=Sheets("DataSheet")
With ActiveSheet
'rename sheet
.Name = s & y