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

 

[Logit]

It is possible you need to pastevaluesonly rather than copy/paste.

Post your macro code for review and also post your workbook (no confidential data) to a cloud download site. Then post the link here. Having a working version of you workbook
is the best method of assisting you.

 

[Matt5353]

It is possible you need to pastevaluesonly rather than copy/paste.

Post your macro code for review and also post your workbook (no confidential data) to a cloud download site. Then post the link here. Having a working version of you workbook
is the best method of assisting you.

Why don't I just email you my workbook?

 

[Gokhan Aycan]

I am with Logit on posting the code, and a screenshot of the tabs perhaps. Straight away e-mailing the workbook will not help anyone else later when/if they come to this post.

 

[Matt5353]

Why don't I just email you my workbook?

Sub DRoom1()

' DRoom1 Macro

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table1").Sort.SortFields.Add2 _

Key:=Range("Table1[Club No]"), SortOn:=xlSortOnValues, Order:= _

xlAscending, CustomOrder:="Smallest to largest", DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table1").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

End With

Range("E2").Select

End Sub

__________________________________________________________________________________

Sub TArtie1()

' TArtie1 Macro

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table2").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table2").Sort.SortFields.Add2 _

Key:=Range("Table2[Club No]"), SortOn:=xlSortOnValues, Order:= _

xlAscending, CustomOrder:="Smallest to largest", DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table2").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("M2").Select

End Sub

__________________________________________________________________________________

This piece of code changes the date.

Sub activesheetcopy()

ActiveSheet.Copy After:=Sheets(Sheets.Count)

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

End Sub

 

[Matt5353]

I am with Logit on posting the code, and a screenshot of the tabs perhaps. Straight away e-mailing the workbook will not help anyone else later when/if they come to this post.

Here is my code.
If I take a screenshot of my sheet it has 337 Pages so not really possible
My first Tab is my Data Base of 8000 names with a membership number
My second Tab has Sep 16th
My third Tab has Sep 17th


Sub DRoom1()

' DRoom1 Macro

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table1").Sort.SortFields.Add2 _

Key:=Range("Table1[Club No]"), SortOn:=xlSortOnValues, Order:= _

xlAscending, CustomOrder:="Smallest to largest", DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table1").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

End With

Range("E2").Select

End Sub

__________________________________________________________________________________

Sub TArtie1()

' TArtie1 Macro

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table2").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table2").Sort.SortFields.Add2 _

Key:=Range("Table2[Club No]"), SortOn:=xlSortOnValues, Order:= _

xlAscending, CustomOrder:="Smallest to largest", DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("15 Sep").ListObjects("Table2").Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("M2").Select

End Sub

__________________________________________________________________________________

This piece of code changes the date.

Sub activesheetcopy()

ActiveSheet.Copy After:=Sheets(Sheets.Count)

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

End Sub

 

[Gokhan Aycan]

I meant screenshot of the Tabs, not the sheets. Sheets may also have sensitive data, so we don't just ask for them unless you are comfortable sharing anyway.

 

[Gokhan Aycan]

Hmm, you want the above code to run on sheet "Sep 16", "Sep 17", etc. also right?

As it is, the macros all reference "Sep 15".
ActiveWorkbook.Worksheets("15 Sep")....

Instead, if you always run the macro on the ActiveSheet, use
ActiveWorkbook.ActiveSheet

or if the macros are also in this workbook, this would be better:
ThisWorkbook.ActiveSheet

 

[Matt5353]

Hmm, you want the above code to run on sheet "Sep 16", "Sep 17", etc. also right?

As it is, the macros all reference "Sep 15".
ActiveWorkbook.Worksheets("15 Sep")....

Instead, if you always run the macro on the ActiveSheet, use
ActiveWorkbook.ActiveSheet

or if the macros are also in this workbook, this would be better:
ThisWorkbook.ActiveSheet

My starting point I should have said Sep 15th. Tried both methods and I get Compile Error Syntax Error. I'm not a programmer etc. All that I have done is learned from other people like yourselves. I believe the only way the macro will work is if it automatically changes when you update the sheet the following day. It's hard to try and explain something to another person without them having access to the workbook. It's much easier to see it for yourself
I'm more than happy for you to view my entire workbook.
Andrew

 

[Gokhan Aycan]

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.