Excel Monthly Spread sheet.


Joined
Nov 26, 2019
Messages
7
Reaction score
0
I have a monthly spread sheet that numerous people have to enter information on each day of the Month. Is it possible to create a Macro that will automatically open work book to the tab referring to the current date each day?
 
Ad

Advertisements

Joined
Nov 26, 2019
Messages
7
Reaction score
0
This still is not working. The sheet reopens in the last tab that I was in instead of the current day
 
Ad

Advertisements

Joined
Nov 26, 2019
Messages
7
Reaction score
0
The Macro works when I run it however I have to re-run it every time I open the spreadsheet. shouldn't this be automatic?
 
Joined
Feb 21, 2018
Messages
209
Reaction score
84
The Macro works when I run it however I have to re-run it every time I open the spreadsheet. shouldn't this be automatic?
That means you have wrongly pasted the vba code .. Please make sure you insert a new module e. g module1 and shift the sub auto_open() to that new module....
 
Joined
Mar 6, 2013
Messages
3
Reaction score
0
Code:
Sub testthis_2()
    Dim wbFile As Workbook, wsFile As Worksheet
    Dim wbFullName As String, wbName As String, wsName As String
    
    wbFullName = "C:\Users\SomeUser\Documents\Book2.xlsx"
    wbName = "Book2.xlsx"   ' Or wbName = Mid(wbFullName, InStrRev(wbFullName, "\") + 1)
    wsName = Format(now(), "MM-DD")
    
    On Error Resume Next
    
    Set wbFile = Workbooks.Open(wbFullName, UpdateLinks:=False)
    Set wsFile = Workbooks(wbName).Worksheets(wsName)
    wsFile.Activate
    
    On Error GoTo 0
End Sub
 
Joined
Nov 26, 2019
Messages
7
Reaction score
0
Thank you for this. One last question. I want to be able to open the spread sheet and change the date on the Sheet Name. Sheet 1 A1 and have it automatically update the tab names is this possible? I have included a sceen shot of my First Sheet and the Coding that is currently running.
 

Attachments

Joined
Mar 6, 2013
Messages
3
Reaction score
0
You want to update every tab name in the workbook (file) as well as cell A1? What do you want the tab names to be? You can also access the tab names directly from cell A1 without any VBA.
Code:
=MID(CELL("filename", $A$1), SEARCH("]", CELL("filename", $A$1))+1, 100)
Or are you wanting to change just a sheet called "Sheet 1" for its name and the contents of A1? You can also do this to change the sheet name:
Code:
ActiveSheet.Name = "Test_Sheet"
 
Ad

Advertisements

Joined
Nov 26, 2019
Messages
7
Reaction score
0
I currently have the sheet set up for December and each sheet in named 12-1, 12-2, Etc. I would like to be able to update the sheet name that is in Cell A1 on 12-1 and have the tab names update to a new month.
 
Ad

Advertisements

Joined
Nov 26, 2019
Messages
7
Reaction score
0
I have input code that duplicated the Master sheet and named them correctly for each day of the month.

Now how do I get cell A1 on each sheet to Duplicate the sheet Name?

This is what I used to create the tabs for each day of the month.

Sub Add_Sheet()
Application.ScreenUpdating = False
Dim ans As Date
Dim i As Long
On Error GoTo M
ans = InputBox("12-01") 'Range("A1").Value
For i = 1 To 31
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(DateAdd("d", i - 1, ans), "MMM dd")
Next
Sheets("Master").Activate
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "Improper date entered in InputBox"
Application.ScreenUpdating = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top