VBA - How to insert a dynamic sheet name?

W

Want2BExcel

Board Regular
Joined
Nov 24, 2021
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
Hi everybody,

I have this workbook where my template sheet can be copied and renamed with a buttonclick (The full code is posted in the bottom).

My problem is in the end of the full code. It's this part just below this text. In this example it creates "JAN.24" and therefor I have to change the formula in the cells below to get data from the previous sheet "DEC.23". This work fine, but....

I'm trying to find a way to avoid using "DEC.23" and instead use something dynamic. Because next sheet I create will be "FEB.24" which need "JAN.24" in the formula and so forth

VBA Code: 
     With ActiveSheet
        wsName = Sheets("DEC.23").Name
     
        Range("AS8").Formula = "=" & wsName & "!AX$8 "
        Range("AS9").Formula = "=" & wsName & "!AX$9"
        Range("AS10").Formula = "=" & wsName & "!AX$10"
        Range("AS11").Formula = "=" & wsName & "!AX$11"
        Range("AS12").Formula = "=" & wsName & "!AX$12"
        Range("AS13").Formula = "=" & wsName & "!AX$13"
        Range("AS14").Formula = "=" & wsName & "!AX$14"
        Range("AS15").Formula = "=" & wsName & "!AX$15"
        Range("AS16").Formula = "=" & wsName & "!AX$16"
        Range("AS17").Formula = "=" & wsName & "!AX$17"
        Range("AS18").Formula = "=" & wsName & "!AX$18"
        Range("AS19").Formula = "=" & wsName & "!AX$19"
        Range("AS20").Formula = "=" & wsName & "!AX$20"
        Range("AS21").Formula = "=" & wsName & "!AX$21"
    
        
     End With

New sheets will always be inserted to the left of "DataSheet"!
Udklip.JPG


The full code:

VBA Code: 
Sub SkiftTIDSINFOformula()

     Dim sh    As Worksheet
     Dim answer As Integer
     
     s = [ScMonth]
     y = [ScYear]
     
     
     
    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 = Left(s, 3) & "." & Right(y, 2)
    ActiveSheet.Unprotect
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Select
    Selection.Delete
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 3")).Select
    Selection.Delete
    
    Range("B1:F1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F6:AP7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("B1:C1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Range("I1:N1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    
     With ActiveSheet
        wsName = Sheets("DEC.23").Name
     
        Range("AS8").Formula = "=" & wsName & "!AX$8 "
        Range("AS9").Formula = "=" & wsName & "!AX$9"
        Range("AS10").Formula = "=" & wsName & "!AX$10"
        Range("AS11").Formula = "=" & wsName & "!AX$11"
        Range("AS12").Formula = "=" & wsName & "!AX$12"
        Range("AS13").Formula = "=" & wsName & "!AX$13"
        Range("AS14").Formula = "=" & wsName & "!AX$14"
        Range("AS15").Formula = "=" & wsName & "!AX$15"
        Range("AS16").Formula = "=" & wsName & "!AX$16"
        Range("AS17").Formula = "=" & wsName & "!AX$17"
        Range("AS18").Formula = "=" & wsName & "!AX$18"
        Range("AS19").Formula = "=" & wsName & "!AX$19"
        Range("AS20").Formula = "=" & wsName & "!AX$20"
        Range("AS21").Formula = "=" & wsName & "!AX$21"
    
        
     End With
    Range("AS8").Select
'    ActiveSheet.Protect
        'activate template sheet
          Worksheets("Template").Activate
     End With

End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
You are getting the month and year of your latest sheet from "ScMonth" and "ScYear" , where are you setting these? , because the obvious way to solve your probelm is to set the previous month in the same place, or work it out in a similar way. then combine as string in the same way and wsname to that
 
Upvote 0
offthelip said:
You are getting the month and year of your latest sheet from "ScMonth" and "ScYear" , where are you setting these? , because the obvious way to solve your probelm is to set the previous month in the same place, or work it out in a similar way. then combine as string in the same way and wsname to that
Click to expand...
ScMonth and ScYear is cells the sheet Template, containing the full month name and the full year.
These are used to rename the new sheet with this
VBA Code: 
.Name = Left(s, 3) & "." & Right(y, 2)
In this example
s=January
y=2024

I did look at how I could use that, but the problem is that, in this case, where I'm creating JAN.24 I not only need the previous month but because I also need previous year to get DEC.24 and I just don't know how.
And I don't know either how to "just point" to the sheet just left of this newly created sheet
 
Upvote 0
if you just want to fine the previous month from the current date then this will do it for you
VBA Code: 
mon = Month(Now())
yer = Year(Now())
If mon = 1 Then
 mon = 12
 yer = yer - 1
End If
yer = yer - 2000
mont = Left(MonthName(mon), 3)
MsgBox mont & yer
wsName=Mont & yer
End Sub
I realised that you can use your S and Y instead of mon and yer to calcualte from Scmonth and scyear
 
Upvote 0
offthelip said:
if you just want to fine the previous month from the current date then this will do it for you
VBA Code: 
mon = Month(Now())
yer = Year(Now())
If mon = 1 Then
 mon = 12
 yer = yer - 1
End If
yer = yer - 2000
mont = Left(MonthName(mon), 3)
MsgBox mont & yer
wsName=Mont & yer
End Sub
I realised that you can use your S and Y instead of mon and yer to calcualte from Scmonth and scyear
Click to expand...
thank you offthelip. I wasn't able to get it to work with your suggestion, but it gave me an idea which worked out, so thank you for that.
 
Upvote 0
My solution:

Since ScMonth actually isn't recognized by excel as a month, but just plain text. I chose to find an unused cell and named it tMonth. Filling in these If statements, resulting in the previous month in 3 letters.
Excel Formula: 
=IF(ScMonth="JANUAR";"DEC";IF(ScMonth="FEBRUAR";"JAN";IF(ScMonth="MARTS";"FEB";IF(ScMonth="APRIL";"MAR";IF(ScMonth="MAJ";"APR";IF(ScMonth="JUNI";"MAJ";IF(ScMonth="JULI";"JUN";IF(ScMonth="AUGUST";"JUL";IF(ScMonth="SEPTEMBER";"AUG";IF(ScMonth="OKTOBER";"SEP";IF(ScMonth="NOVEMBER";"OKT";IF(ScMonth="DECEMBER";"NOV";""))))))))))))

Thereafter I did the somewhat the same with ScYear. I chose to find an unused cell and named it tYear. Filling in these If statements, resulting in the last 2 numbers in the year
Excel Formula: 
=IF(ScMonth="JANUAR";ScYear-2001;IF(ScMonth="FEBRUAR";ScYear-2000;IF(ScMonth="MARTS";ScYear-2000;IF(ScMonth="APRIL";ScYear-2000;IF(ScMonth="MAJ";ScYear-2000;IF(ScMonth="JUNI";ScYear-2000;IF(ScMonth="JULI";ScYear-2000;IF(ScMonth="AUGUST";ScYear-2000;IF(ScMonth="SEPTEMBER";ScYear-2000;IF(ScMonth="OKTOBER";ScYear-2000;IF(ScMonth="NOVEMBER";ScYear-2000;IF(ScMonth="DECEMBER";ScYear-2000;""))))))))))))

Finally I changed the VBA code to this
VBA Code: 
With ActiveSheet
            tM = [tMonth]
            tY = [tYear]
            tName = (tM) & "." & (tY)
            
            
            Range("AS8").Formula = "=" & tName & "!AX$8 "
            Range("AS9").Formula = "=" & tName & "!AX$9"
            Range("AS10").Formula = "=" & tName & "!AX$10"
            Range("AS11").Formula = "=" & tName & "!AX$11"
            Range("AS12").Formula = "=" & tName & "!AX$12"
            Range("AS13").Formula = "=" & tName & "!AX$13"
            Range("AS14").Formula = "=" & tName & "!AX$14"
            Range("AS15").Formula = "=" & tName & "!AX$15"
            Range("AS16").Formula = "=" & tName & "!AX$16"
            Range("AS17").Formula = "=" & tName & "!AX$17"
            Range("AS18").Formula = "=" & tName & "!AX$18"
            Range("AS19").Formula = "=" & tName & "!AX$19"
            Range("AS20").Formula = "=" & tName & "!AX$20"
            Range("AS21").Formula = "=" & tName & "!AX$21"
            
            
        End With

I'm very aware that it's properly not the correct way to do it and there's properly an easier way....a more pretty way to solve this. But it works :)
If someone comes up with the correct way to solve this, I'll happy to give the points ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
using sorting and array to deal with big data for expected result
Replies
3
Views
301
Hasson
H
Unlucky
  • Question Question
SUM, INDEX, MATCH, etc.
Replies
1
Views
615
Unlucky
Unlucky
M
  • Solved
Count consecutive 0’s and 1’s
2
Replies
14
Views
576
KRice
K
N
  • Solved
Vlookup help
Replies
4
Views
477
Eric W
Eric W
H
  • Question Question
collecting two sheets in listbox on userform
Replies
2
Views
422
Hasson
H

Forum statistics

Threads
1,224,828
Messages
6,181,214
Members
453,024
Latest member
Wingit77

Share this page

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