VBA: Change a random tablename and useraction to a formula

[Want2BExcel]

I can't figure out how to make this work without manually interaction in the code before every run.
The reason is these two main problems. Marked MAIN PROBLEM #1 and #2 at the bottom of the code (specific the part in BOLD, because they change al the time)

#1 ActiveSheet.ListObjects("Budget_TEMPLATE25").Name = [A1]
#2 ActiveCell.FormulaR1C1 = "=SUM(R[-1]C+Budgetkonto_2024[@December])"

.....but I've also commented two small problems that I'm trying to figure out, so if anybody have a solution for those, don't hold back The macro works despite of these!!!!

Sub New_Year()

‘ 
Sheets("Budget_TEMPLATE").Select
Sheets("Budget_TEMPLATE").Copy Before:=Sheets(2)
Sheets("Budget_TEMPLATE (2)").Select
' SMALL PROBLEM #2 It annoys me, that the sheets are placed backwards, e.g. Budgetkonto_2024-2023-2022 etc. but it's the only way I could come up with, where I don't have to 
' change the 2 in "Before:Sheets(2)" in the code. I would prefer to automate it, if it's possible. If not then maybe it could be solved ' with user-interaction, to choose, 
' where it should be placed. The sheets are always named Budgetkonto_YEAR and I would like, if they come continuously, after the previous year. 
' 
newname = InputBox("Name your sheet: Budgetkonto_YYYY")
ActiveSheet.Name = newname
' SMALL PROBLEM #1 Is it possible to make the first part, [Budgetkonto_], of the sheetname fixed, so the user only could type in a year, like 2023?
'
Range("A1").Select
ActiveCell.Value = newname
' Sets the value in cell A1 to the new sheetname, with the purpose to make the next line work.
'
ActiveSheet.ListObjects("Budget_TEMPLATE25").Name = [A1]
' MAIN PROBLEM #1, I try to rename the tablename (DesignTab). But it doesn't work!!! I have to manually change this part “Budget_TEMPLATE25” or else the rest dosn't work.
' I'm looking for a way to use a random TableName OR retrieve the active TableName and using that. I'm sure the text part would never change, but the numbers in the end, could be whatever, depending
' of who's using the workbook. 
' Right now, every time I copy the sheet, it gets a different tablename, e.g. here "Budget_TEMPLATE25" etc. which generates and error, next time I run the macro, without
' changing to "Budget_TEMPLATE26" manually. 
'
Range("D107").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C+Budgetkonto_2024[@December])"
' MAIN PROBLEM #2 This last line links the new year account balance, to the previous year. I don't know just yet, how this can be solved. Maybe with some kind of
' option for the user? "Which year do you want to link to?" with an option to 'choose from the available sheets e.g. "Budgetkonto_XXXX"



End Sub

 

[BSALV]

you want to add a new sheet and sort your "Budget_Templatexx" sheets ascending?

 

[Want2BExcel]

you want to add a new sheet and sort your "Budget_Templatexx" sheets ascending?

Yes, after renaming they should be sorted ascending e.g. Budgetkonto_2022, Budgetkonto_2023 etc.

 

[BSALV]

small problem 1 (without sorting the sheets)

Option Compare Text

Sub Make_New_BudgetKonto()
     Dim sh    As Worksheet
     s = "Budgetkonto_"

     newname = Application.InputBox("What year ?", UCase("Name your sheet: Budgetkonto_YYYY"), Year(Now), , , , , 1)     'what year with initial year=actual year
     If Application.Median(newname, 2020, 2050) <> newname Then     'check range
          MsgBox "error, wrong year": Exit Sub
     Else
          On Error Resume Next
          Set sh = Sheets(s & Format(newname, "0000"))          'check sheet exists already
          On Error GoTo 0
          If Not sh Is Nothing Then MsgBox "worksheet existst already", vbCritical: Exit Sub

          i1 = 1
          For i = 1 To Worksheets.Count                         'loop through all worksheets
               shname = Worksheets(i).Name                      'name of that sheet
               If shname Like s & "####" Then                   'similar name
                    If --Right(shname, 4) > py And --Right(shname, 4) < newname Then     'greater then previous found year, but year smaller then wanted year
                         i1 = i: py = --Right(shname, 4)        'indexnumber of that sheet and year of that sheet
                    End If
               End If
          Next
     End If

     Sheets("Budget_TEMPLATE").Copy after:=Sheets(i1)           'copy your template after that sheet
     With ActiveSheet
          .Name = s & newname                                   'rename sheet
          .Range("A1").Value = .Name 
          .Listobjects(1).name= .name             'rename 1st listobject of that sheet 
          .Range("D107").FormulaR1C1 = "=SUM(R[-1]C+Budgetkonto_" & newname - 1 & "[@December])"     'calculated guess !!!
     End With

End Sub

if the listobject isn't the first, give me a cell within that listobject.

 

[Want2BExcel]

small problem 1 (without sorting the sheets)

Option Compare Text

Sub Make_New_BudgetKonto()
     Dim sh    As Worksheet
     s = "Budgetkonto_"

     newname = Application.InputBox("What year ?", UCase("Name your sheet: Budgetkonto_YYYY"), Year(Now), , , , , 1)     'what year with initial year=actual year
     If Application.Median(newname, 2020, 2050) <> newname Then     'check range
          MsgBox "error, wrong year": Exit Sub
     Else
          On Error Resume Next
          Set sh = Sheets(s & Format(newname, "0000"))          'check sheet exists already
          On Error GoTo 0
          If Not sh Is Nothing Then MsgBox "worksheet existst already", vbCritical: Exit Sub

          i1 = 1
          For i = 1 To Worksheets.Count                         'loop through all worksheets
               shname = Worksheets(i).Name                      'name of that sheet
               If shname Like s & "####" Then                   'similar name
                    If --Right(shname, 4) > py And --Right(shname, 4) < newname Then     'greater then previous found year, but year smaller then wanted year
                         i1 = i: py = --Right(shname, 4)        'indexnumber of that sheet and year of that sheet
                    End If
               End If
          Next
     End If

     Sheets("Budget_TEMPLATE").Copy after:=Sheets(i1)           'copy your template after that sheet
     With ActiveSheet
          .Name = s & newname                                   'rename sheet
          .Range("A1").Value = .Name
          .Listobjects(1).name= .name             'rename 1st listobject of that sheet
          .Range("D107").FormulaR1C1 = "=SUM(R[-1]C+Budgetkonto_" & newname - 1 & "[@December])"     'calculated guess !!!
     End With

End Sub

if the listobject isn't the first, give me a cell within that listobject.

SOLUTION to small problem #1

It works BEAUTIFUL!!!!! ? Outstanding work BSALV!
Thank you! ?

 

[Want2BExcel]

small problem 1 (without sorting the sheets)

Option Compare Text

Sub Make_New_BudgetKonto()
     Dim sh    As Worksheet
     s = "Budgetkonto_"

     newname = Application.InputBox("What year ?", UCase("Name your sheet: Budgetkonto_YYYY"), Year(Now), , , , , 1)     'what year with initial year=actual year
     If Application.Median(newname, 2020, 2050) <> newname Then     'check range
          MsgBox "error, wrong year": Exit Sub
     Else
          On Error Resume Next
          Set sh = Sheets(s & Format(newname, "0000"))          'check sheet exists already
          On Error GoTo 0
          If Not sh Is Nothing Then MsgBox "worksheet existst already", vbCritical: Exit Sub

          i1 = 1
          For i = 1 To Worksheets.Count                         'loop through all worksheets
               shname = Worksheets(i).Name                      'name of that sheet
               If shname Like s & "####" Then                   'similar name
                    If --Right(shname, 4) > py And --Right(shname, 4) < newname Then     'greater then previous found year, but year smaller then wanted year
                         i1 = i: py = --Right(shname, 4)        'indexnumber of that sheet and year of that sheet
                    End If
               End If
          Next
     End If

     Sheets("Budget_TEMPLATE").Copy after:=Sheets(i1)           'copy your template after that sheet
     With ActiveSheet
          .Name = s & newname                                   'rename sheet
          .Range("A1").Value = .Name
          .Listobjects(1).name= .name             'rename 1st listobject of that sheet
          .Range("D107").FormulaR1C1 = "=SUM(R[-1]C+Budgetkonto_" & newname - 1 & "[@December])"     'calculated guess !!!
     End With

End Sub

if the listobject isn't the first, give me a cell within that listobject.

It does the sorting of the sheets BSALV, so it's actual SOLUTION to ALL the problems, both main problem#1 AND #2 and small problem #1 AND #2 ??BEAUTIFUL!!!
You are THE MAN BSALV!! ???

 

[BSALV]

you're welcome