Excel Monthly Spread sheet.

T

Troy Walt

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?
 
This still is not working. The sheet reopens in the last tab that I was in instead of the current day
 
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?
 
Troy Walt said:
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?
Click to expand...
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....
 
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
 
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

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"
 
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.
 
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
 
Back
Top