EXCEL - automaticaly create sheet at start of each month

[Diezz]

Hello,

I would like to create automaticaly create a sheet with the name of the month at the start of each month in various XLS files.

For example in a.xls, at 1 june, i would like to be created the sheet named "june", and so on for b,c,d and total.xls.

I think it can be made in vba but i don`t have any ideeas for the code.

Anyone can help me?

Thanks

 

[SkipVought]

Say it ain't so, Diezz!

A separate sheet or workbook for similar data is a typical mistake that many Excel users make. It makes the data almost as useless as a bank statement in a file cabinet; useless, that is, relative to your ability to access, analyze and report the data in Excel.

I can't count the number of times that users seek help because their data is chopped up into many sheets, and what they want to do it to be able to COMBINE it. I will guarantee that at some time, your boss, director or VP will ask a question like, "What's the year-to-date...???" And there you are with each month's data in a separate sheet.

With the tools & features that Excel has available to you, it is much easier to create a report for a month's worth of stuff, than to combine separate months into one talbe for the purpose of analysis.

Please reconsider your current design.

Skip,

_[red][/red]

 

[KenWright]

100% in agreement with Skip. Makes it soooooooo much easier to analyse if all on one sheet. That having been said, if for some reason you really really do have to go this route, then the following code could be put either inside your personal.xls or inside the file itself:-

Sub Add_Date_Sheet()

    Dim sDate As String
    Dim Sh As Worksheet
    Dim ShtChk As Boolean
    Dim NewSht As Worksheet
    
    sDate = Format(Now(), "mmm")
    For Each Sh In ActiveWorkbook.Sheets
        If Sh.Name = sDate Then
            ShtChk = True
            Exit For
        End If
    Next Sh
    If ShtChk <> True Then
        Set NewSht = Worksheets.Add
        NewSht.Name = sDate
    Else: MsgBox "Error - Sheet already exists"
    End If
End Sub

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Just to augment your demise, call Ken's procedure in your Workbook_Open event and it will "automatically" add a sheet the first time you open each month. Also, comment out the MsgBox error statement if that annoys you.

CAVEAT: Only works in a given workbook for one year.

Skip,

_[red][/red]

 

[firefytr]

Eh, I'd like to third the recommendation of rethinking your data structure. The intent is generally made in good faith/efforts, but not necessarily the best to employ.

Another thing you may want to consider is the seperation of Input, Output and Calculation data. These have very different aspects and relations to each other. Although they are inexorabaly intertwined, they serve very different purposes and keeping this information segregated should make it easier for the end user(s) in the end.

Here are some good references for good spreadsheet design:

http://www.exceluser.com/tools/agile1.htm

http://www.xl-logic.com/pages/design_notes.html

http://www.meadinkent.co.uk/xltestingandmapping.htm

http://www.geekgirls.com/spreadsheet02.htm

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

http://www.jegsworks.com/lessons/numbers/design/analysis.htm

http://www.sysmod.com/sslinks.htm

In the end it's what works for you, of course, but there are some basic principles and methods you should consider before diving in head first. And if you're looking for a good book on this topic, check out Stephen Bullen and Rob Bovey's book, Professional Excel Development. Link found here:

http://www.oaltd.co.uk/

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Diezz]

Hello all and thanks for your quick reponses.

The reason why i want different xls files it's because more than 1 person use them.

6 collegues are using 6 xls files, in the same time. I would really like if all of us were using the same file and the same sheet, the problem is that these individual sheets must be updated at every phone received (sometimes 30phones/agent/day) and as far as i know you can't save a xls file if someone already opened it and forgot to close it.

Saving it would be the main problem, but if anyone has ideeas i'm listening.

 

[Fenrirshowl]

Diezz

Skip's first solution is the easiest - an access database. Set up the Table including all the data fields in your spreadsheet, then set up a Query if required (possibly to select certain months) and finally a Form your front end users can populate. (The Query and the Form should be easy to set up using the in built Wizards).

Once this is completed, select Tools/Database Utilities/Database Splitter

This will split the database in two (one denoted with a _be addition to the name). This is the back-end database that holds the information. The other is a front-end database. Multiple users can log in to the front-end at the same time and add new records.

It saves a lot of time when compared to logging in to Excel, storing passwords etc.

If different users need different access to data you can set up a users table to contain names, passwords, and a flag for the data entries that are to be available/user level (effectively allowing access to certain queries and forms only). You can then a) use Visual Basic to open certain forms depending on the user, or b) open a form, check the user level and then disable certain fields on the forms depending on that user level.

Even if you have to stay with excel for the minute it's worth looking at for future similar problems.

Fen