Macros

[Matt5353]

I have a sheet with two macros on it that when pressed, lists a list of numbers from the lowest to the highest.
The sheet is dated with today's date (as today is Sep 16th) I wish to copy the sheet, and add it at the end tomorrow morning, then I would have Sep 16th, Sep 17th, etc.
When I copy it the following day the date changes to today's date (Sep 17th) on the Tab, but my macros stop working and I have to reset them again because in the code it shows the previous day and not the updated date in the code.
I'm having to do this every day and it's a real pain when you are doing it for a month at a time.
Help from a genius would be helpful.
I have scoured youtube looking for the right way of doing it and I can find nothing.
I'm sure there must be a way that the macro code can change the date automatically in itself once you click copy.
Thanks again Andrew

 

[Matt5353]

You have a typo possibly somewhere if there is a Syntax Error. Doesn't VBA Editor change the text to red anywhere? Post the modified code please.

Edit: Also, if you click "Compile VBA Project" under "Debug" menu in VBA Editor, you should spot these things easier.

Yes, it does change it to red, but I still cannot fix it, I have no idea how to. Will upload a Mini Sheet

 

[Matt5353]

So Now? I get the macro to work and before I did have an error in Red. So the macro runs but still does not work because the date in the macro does not change to the new date for the macro to actually work. I need some code in the macro to change the date to the current date when I run the macro to add a new sheet. I'm not getting it and it frustrates me. If I had sent you my workbook in the first place it would probably be fixed by now and I would be VERY grateful. I'm going round in circles. I wish to share my workbook with you?

 

[Matt5353]

Hopefully this link works. I have attached my workbook for you to view in my onedrive. It shoud allow you to edit and do what ever to it. Obviously the dates are out of date but that should be easily changed to the current dates.

Contact Tracing September 2021-1 Copy.xlsm

1drv.ms

 

[Gokhan Aycan]

Ok, how you can handle this is:

1. Every sheet has a "Codename" which you can set in VBA Editor. Codename doesn't get affected by sheet name changes. Change the codename of sheet "15 Sep". I will use "Main" for example. As long as this sheet is not deleted, your code will work. Here is where you change it (select the sheet under projects, and open Properties pane if not visible).

2. Modify your code such that whenever you want to do something on this sheet, use "Main" as a reference.

Sub DRoom1()
'
' DRoom1 Macro
'

'
     
    Main.ListObjects("Table1").Sort.SortFields.Clear
    Main.ListObjects("Table1").Sort.SortFields.Add2 _
        Key:=Range("Table1[Club    No]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, CustomOrder:="Smallest to largest", DataOption:=xlSortNormal
    With Main.ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Main.Range("E2").Select
End Sub

Sub TArtie1()
'
' TArtie1 Macro
'

'
    Main.ListObjects("Table2").Sort.SortFields.Clear
    Main.ListObjects("Table2").Sort.SortFields.Add2 _
        Key:=Range("Table2[Club     No]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, CustomOrder:="Smallest to largest", DataOption:=xlSortNormal
    With Main.ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Main.Range("M2").Select
End Sub

Sub activesheetcopy()

Main.Copy After:=Sheets(Sheets.Count)
Main.Name = Format(Date, "dd-mmm")
      
End Sub

 

[Matt5353]

I have done that and the macros work. Will be good to wake up tomorrow and see if it changes to the 21st and the macros work.
Thank you for your help so far. Fingers crossed Andrew

 

[Matt5353]

No, it does not work. On the second day, When you click it adds another sheet with the previous day but the main sheet changes to today's date and the macros do not work. this is my email address a.matthews2453@gmail.com. Please can I send you a working copy?

 

[Gokhan Aycan]

Edit it out if this is your personal e-mail address.

 

[Gokhan Aycan]

Just change this macro, and see if it is what you want. Sort macros look like working, can't say exactly without some data in it.

Sub activesheetcopy()

Dim strCopyName As String

strCopyName = Main.Name

Main.Name = Format(Date, "dd-mmm")

Main.Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = strCopyName
Main.Activate

End Sub