Macros

Matt5353

New Member
Joined
Nov 30, 2018
Messages
30
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,238
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

New Member
Joined
Nov 30, 2018
Messages
30
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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 30, 2018
Messages
30

ADVERTISEMENT

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

New Member
Joined
Nov 30, 2018
Messages
30
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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 30, 2018
Messages
30
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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,611
Messages
5,725,302
Members
422,608
Latest member
bswg5882

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
Top