Naming Worksheet to Jan 2009, Feb 2009 etc.

Microsoft Book: office excel 2003 inside...
October 14, 2009 at 01:12:04
Specs: Windows XP
I have two workbooks, one of them is named as MPR 2009-10.
When i run the following code in Book1, the worksheets in the MPR 2009-10 workbook is named as January, February etc. How to name it as Jan 2009, Feb 2009?. Further I have seen that when i change the system date to1st february 2010, the worksheet called january should have been there in the MPR workbook but it is missing(failed to copy from Book1). Please help.


Private Sub Workbook_Open()
'Copies MPR sheet to MPR 2009-10 workbooks the
'first time this workbook opens in a given month.

Dim LastOpened As String

LastOpened = Worksheets("Sheet5").Range("B1").Text

If LastOpened = "" Then
LastOpened = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End If

If Month(Date) > Month(CDate(LastOpened)) Then
'first time opened this month: save sheets
Workbooks.Open ("D:\MPR 2009-10.xls")
With Workbooks("MPR 2009.xls")
ThisWorkbook.Worksheets("MPR").Copy after:=.Sheets(.Sheets.Count)
.ActiveSheet.Name = MonthName((Month(Date) + 11) Mod 12)
.Close True
End With

MsgBox MonthName((Month(Date) + 11) Mod 12) & " sheets saved."
End If

'update date
Worksheets("Sheet5").Range("B1") = CStr(Date)
ThisWorkbook.Save

End Sub


See More: Naming Worksheet to Jan 2009, Feb 2009 etc.

Report •


#1
October 14, 2009 at 04:17:21
The line
With Workbooks("MPR 2009.xls")
should have been written as

With Workbooks("MPR 2009-10.xls")


Report •

#2
October 14, 2009 at 05:00:39
Hi,

To get the name for your new worksheet use:
.ActiveSheet.Name = Format(Now(), "mmm_yyyy")

By changing the "mmm_yyyy" you can easily change the sheet name e.g. "mmmm-yy" gives October-09 .

For convenience add a new variable Dim strSheetName
Then
strSheetName=Format(Now(), "mmm_yyyy")
and use strSheetName to name your new sheet and for your message.

I haven't looked at your other issue regarding your test in 2010

Regards


Report •

#3
October 14, 2009 at 05:35:13
Hi,

Is the problem with creating new worksheets due to the test you do to see if a new worksheet is required.

The following line tests if the current month is > the last opened month

If Month(Date) > Month(CDate(LastOpened)) Then

But when you get to January (=1), it is less than the previous month December (=12), not greater than, and thus does not create the new worksheet.

You will need an extra test for 'this month = 1 and last opened month =12'


An alternative approach would be to create the name for the current month worksheet e.g., October_09,
then iterate through all the worksheets in the 2009-10 workbook to see if there is already an October_09 worksheet, and if not, create one.

dim ws as Worksheet
dim blnExist as Boolean
blnExist=False

for each ws in Workbooks("MPR_2009-10.xls)
if ws.name = "<This months name.>" then blnExist = True
next
If blnExist=False then
<create new worksheet>
end if

This isn't tested code, just an outline for how you might do the test

With this test you shouldn't need to save last opened and save a value in B1
Just open the workbook, test if this months worksheet exists and if not, create it.

Regards



Report •

Related Solutions

#4
October 15, 2009 at 02:32:29
Humar, Thanks for your reply

Actually What I am trying to do is if I am opening book1 in any date of February for the first time, the MPR Sheet of Book1 should be copied to MPR 2009-10 workbook and named its sheet as Jan 2009 and so on.The line ".ActiveSheet.Name = Format(Now(), "mmm_yyyy") " copies the current month.

About your response No.3, I want to tell you that I am very very new to VBA. I got the above code through google.

I want to use the workbook for more than one year but the code works for a single year i.e. worksheets of Book1 of year 2010 are not copying to MPR 2009-10 workbook.


Report •

#5
October 15, 2009 at 08:40:16
Hi,

If I understand correctly

a. You have a 'Source' workbook with a worksheet named 'MPR'
b. You have a Workbook 'MPR_2009-10.xls' that contains a worksheet for each completed month
c. At the start of a new month the last months data will be copied from the MPR worksheet in the Source workbook to a new worksheet e.g. Oct-09 in the MPR_2009-10.xls workbook.

The solution offered does the following:
a. When the Source workbook is opened it creates the name for the worksheet to be saved based on last month. So in October 2009 it will create the name 'Sep-09'.
b. The last saved worksheet has been recorded in Cell B1 of the Source workbook on a worksheet named 'Main'
c. If the calculated name does not match the last saved name, the Year workbook is opened.
d. The name of the Year workbook is calculated from todays date based on the assumption that the Year workbooks run from April in one year to March of the following year. This can be changes by changing one line in the code.

'set last month of Fiscal year
intLastMFisc = 3

e. The appropriate Year workbook is opened and a new worksheet is created and named with the Month/year e.g. Oct-09
f. The data from the MPR worksheet in the Source workbook is copied to the new worksheet in the Year workbook.
g. The last saved value in B1 is updated and both workbooks are saved.

1. The data copied from Source to year uses the 'UsedRegion' range. This can be changed to a specific range if required
2. The code does not check for or create new Year files, e.g., 2010-11. This could be added to the code, or the new workbook could be created manually each year.
3. There is no error checking
4. The path to the Year workbook must be changed to match your path. Only one line needs to be changed:

'set path to MPR Year workbook
strMPRPath = "C:\Temp\"

5. As two workbooks are in use, I have used the full names, rather than ActiveWorkbook or ActiveSheet, to avoid confusion.
6. Note that some lines of code have been split onto two lines using ' _'
This should work OK when copied.

Here is the code in the Workbook_Open() event code for the Source workbook named 'MPR_Source.xls'

Option Explicit

Private Sub Workbook_Open()
'Copies MPR sheet to a new worksheet in the MPR 'Year' workbook
'the first time this workbook opens in a given month.

On Error GoTo ErrHnd

Dim strMPRSource As String		'name of source workbook
Dim strMPRYear As String		'name of MPR Year workbook
Dim strMPRPath As String		'path to MPR Year workbook
Dim strLastSaved As String		'name of last worksheet saved
Dim strLastMonth As String		'name of worksheet for last completed month
Dim intNumWS As Integer			'number of worksheets in MPR workbook
Dim intLastMFisc As Integer		'number of last month in the fiscal year

'set last month of Fiscal year
intLastMFisc = 3

'set name of Source (This) workbook
strMPRSource = ActiveWorkbook.Name

'set path to MPR workbook
strMPRPath = "C:\Temp\"

'create MPR Year name (for fiscal years April to March)
If Month(Now()) > intLastMFisc Then
    strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) & "-" & _
        Right(Year(eomonth(Now(), -1)) + 1, 2) & ".xls"
    Else
    strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) - 1 & _
        "-" & Right(Year(eomonth(Now(), -1)), 2) & ".xls"
End If

'get/create MPR worksheet names
strLastSaved = Format(Workbooks(strMPRSource).Worksheets("Main").Range("B1").Value, "mmm_yy")
strLastMonth = Format(eomonth(Now(), -1), "mmm_yy")

'test if last completed month has been saved, if not create, name and save
If strLastSaved <> strLastMonth Then
    Workbooks.Open strMPRPath & strMPRYear
        With Workbooks(strMPRYear)
            'get number of worksheets
            intNumWS = .Worksheets.Count
            'add and name
            .Worksheets.Add After:=.Worksheets(intNumWS)
            .Worksheets(intNumWS + 1).Name = strLastMonth
            'copy MPR worksheet to new worksheet in Year workbook
            Workbooks(strMPRSource).Worksheets("MPR").UsedRange.Copy _
                Destination:=.Worksheets(strLastMonth).Range("A1")
        End With
    'save updated year workbook
    Workbooks(strMPRYear).Save
    'update last saved
    Workbooks(strMPRSource).Worksheets("Main").Range("B1").Value = strLastMonth
    MsgBox strLastMonth & " worksheet saved."
    'save Source workbook
    Workbooks(strMPRSource).Save
End If
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub

I have not tested this extensively, so please test it in your environment.
It should work for subsequent years
Let me know if it works or not. If not say what doesn't work/any error messages

Regards


Report •

#6
October 16, 2009 at 00:10:55
Gives this error "Sub or Function not defined" and it points to eomonth

I want to make following point clear
1. I have only two workbooks viz. Book1(you refer as MPR_Source) and MPR 2009-10. I have to say this because I am bit confused at numbers of workbooks you are refering to such as MPR year, Year and MPR Source(Book1 in my case) workbooks.I just consider MPR year means MPR 2009-10. Is that right?

2. Book1 contains worksheets called MPR, Form64, Demand, Utilization, Sheet5(you refer as Main), etc. Thus there is only single MPR sheet in Book1 which I used to edit each and every month and want it to be copied every month to MPR 2009-10 workbook for fiscal year say, 2009-10.

Anyway, Thanks for your time and effort put for me.


Report •

#7
October 16, 2009 at 04:44:01
Hi,

Yes, there are only two workbooks in the code I provided.

One workbook, Book 1 or 'Source', has a sheet named MPR which is copied every month. It also has a separate sheet that holds the last month copied information in cell B1 (I named it Main, but you could edit the code to save it to any cell on any worksheet in Book 1).

Each month the MPR sheet is copied to a new worksheet in the MPR 2009-10 workbook, naming the new sheet with the month and year e.g., Oct-09. I referred to this as the Year workbook because the code will generate a new name at the start of each fiscal year.

The error with eomonth is easily corrected.

Open Excel.
From the toolbar select Tools - Add-Ins
Check the Analysis ToolPak-VBA entry
click OK and try opening your Book1 again.

If it still gives an error close and reopen Excel and try again.

Regards


Report •

#8
October 18, 2009 at 21:56:33
Sorry to inform you that same error exist and it still points to eomonth.

The analysis ToolPak - VBA i/c without VBA was already installed.

I have tried all; checked for MPR and Main worksheet in the Source file along with the file name including case & space and file directory both in my disk drive and in the code you provided. At last I also tried to modify the code a little bit i/c strMPRPath to "C:\Temp\MPR 2009-10" where i placed both file in Temp folder in C: drive.

The solution is out of sight at my level of knowledge.



Report •

#9
October 19, 2009 at 04:52:32
Hi,

Put the following formula in any cell:
=EOMONTH(NOW(),0)

and it should return 31-Oct-09.

Please let me know what result you get.

Regards


Report •

#10
October 19, 2009 at 05:13:48
Hi,

Hopefully this will do it:

Open the VBA code window (Alt + f11), and without stepping into the code, goto Tools - References.

Check the entry: atpvbaen.xls

Regards


Report •

#11
October 22, 2009 at 23:39:16
Sorry, I was on hiking. checked reponse No.9 and seems ok. But when i check atpvbaen.xls (about response No.10) the code does noting at all, no error either. when i uncheck atpvbaen.xls then i am still stuck at eomonth.

I noticed one strange things, though it may not relate to the code you provided, that when i open the source workbook and try to close it without even clicking anywhere else on the page, it pop up a message to save the workbook while other workbooks doesn't do this

I just formatted my C drive and clean install OS and Office 2003 with Analysis ToolPak-VBA installed then tried to run the code without success. Also tried at three different computers. It put my mind spinning.


Report •

#12
October 23, 2009 at 04:47:20
Hi,

The message about saving occurs because the code I gave you runs every time the workbook opens, and Excel assumes that the workbook has changed and therefore asks whether to save the changes.

Can you comment out the On Error line (add a ' at the start) then use the f8 function key to single step through the code, and see where it either gives an error or just stops working.

Also can you check that all the workbook names and worksheet names and the Path, match the names you are using.

Regards


Report •

#13
October 23, 2009 at 23:00:36
I Placed two excel files viz. MPR Source and MPR 2009-10 in C:\Temp. The MPR Source workbook contains worksheets called MPR, Main, Form64, etc. (See reponse No.8) Then I tried to change your code as below:


'set name of Source (This) workbook
strMPRSource = ActiveWorkbook.Name 'Tried changing this as MPR Source.xls

'set path to MPR workbook
strMPRPath = "C:\Temp\MPR 2009-10.xls"


When i put this =EOMONTH(NOW(),0) in a blank cell it gives me 40117, when formatted to date gives 10/31/2009 which seems ok.I also tried para 2 of your response No.12; all error points to eomonth only.


Report •

#14
October 24, 2009 at 08:52:53
Hi,

The source filename and path should be:

'set name of Source (This) workbook
strMPRSource = "MPR Source.xls"
Make sure you have double quotes around the name.

'set path to MPR workbook
strMPRPath = "C:\Temp\"
Don't put the workbook name into it.
I assume you have a folder named Temp on drive C:

I assume that the lines with eomonth are one line - they were posted with the line break character in them:

 strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) & "-" & _
        Right(Year(eomonth(Now(), -1)) + 1, 2) & ".xls"

Here they are, both as one line:

strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) & "-" & Right(Year(eomonth(Now(), -1)) + 1, 2) & ".xls"
Else 
strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) - 1 & "-" & Right(Year(eomonth(Now(), -1)), 2) & ".xls"

There was another line with a line break in it, so please make sure that all those lines are single lines. If any lines of code show up in red, then there is an error.

Did you single step through the code (with atpvbaen.xls checked).

What error was reported and which line reported an error.

If eomonth really is the issue, I will write a user defined function to replace it.

As the code with eomonth works on my PC (Excel 2003 and XP Home and Prof. editions) I feel that the error is somewhere else, hence the need for a single stepping and the error messages.

Regards


Report •

#15
October 25, 2009 at 21:29:28
Yes I did single step through the code with atpvbaen.xls checked but it is not working and no error report either. Still getting error at eomonth with atpvbaen.xls unchecked. Followed your instruction at response No. 14 minutely. I am also using Excel 2003.

Report •

#16
October 25, 2009 at 22:29:28
Hi,

With atpvbaen.xls unchecked you will inevitably get an error.

From what you have said, the problem is not with EOMONTH().

Please post the code you are actually using:
(use the <pre> and </pre> tags.

Regards



Report •

#17
October 26, 2009 at 02:20:04
With atpvbaen.xls unchecked I get error called 'Compile error: Sub or function not defined'. When i single step through the code; the error points to eomonth only.

This is the actual code i am using(Let me remined you that the two workbooks viz. MPR Source and MPR 2009-10 are in Temp folder in C: drive. The MPR Source workbook has also MPR, Main, etc worksheets):

Option Explicit

Private Sub Workbook_Open()
'Copies MPR sheet to a new worksheet in the MPR 'Year' workbook
'the first time this workbook opens in a given month.

On Error GoTo ErrHnd

Dim strMPRSource As String      'name of source workbook
Dim strMPRYear As String        'name of MPR Year workbook
Dim strMPRPath As String        'path to MPR Year workbook
Dim strLastSaved As String      'name of last worksheet saved
Dim strLastMonth As String      'name of worksheet for last completed month
Dim intNumWS As Integer         'number of worksheets in MPR workbook
Dim intLastMFisc As Integer     'number of last month in the fiscal year

'set last month of Fiscal year
intLastMFisc = 3

'set name of Source (This) workbook
strMPRSource = "MPR Source.xls"

'set path to MPR workbook
strMPRPath = "C:\Temp\"

'create MPR Year name (for fiscal years April to March)
If Month(Now()) > intLastMFisc Then
    strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) & "-" & Right(Year(eomonth(Now(), -1)) + 1, 2) & ".xls"
    Else
    strMPRYear = "MPR_" & Year(eomonth(Now(), -1)) - 1 & "-" & Right(Year(eomonth(Now(), -1)), 2) & ".xls"
End If

'get/create MPR worksheet names
strLastSaved = Format(Workbooks(strMPRSource).Worksheets("Main").Range("B1").Value, "mmm_yy")
strLastMonth = Format(eomonth(Now(), -1), "mmm_yy")

'test if last completed month has been saved, if not create, name and save
If strLastSaved <> strLastMonth Then
    Workbooks.Open strMPRPath & strMPRYear
        With Workbooks(strMPRYear)
            'get number of worksheets
            intNumWS = .Worksheets.Count
            'add and name
            .Worksheets.Add After:=.Worksheets(intNumWS)
            .Worksheets(intNumWS + 1).Name = strLastMonth
            'copy MPR worksheet to new worksheet in Year workbook
            Workbooks(strMPRSource).Worksheets("MPR").UsedRange.Copy Destination:=.Worksheets(strLastMonth).Range("A1")
        End With
    'save updated year workbook
    Workbooks(strMPRYear).Save
    'update last saved
    Workbooks(strMPRSource).Worksheets("Main").Range("B1").Value = strLastMonth
    MsgBox strLastMonth & " worksheet saved."
    'save Source workbook
    Workbooks(strMPRSource).Save
End If
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub


Report •

#18
October 26, 2009 at 06:29:50
Hi,

I have loaded the code you posted and I ran it with two workbooks:
One named "MPR Source.xls"
and the other MPR_2009-10

It runs as expected, i.e. nothing happened and no errors, because the Sep_09 worksheet was already present in MPR_2009-10.xls.

I changed the system date to next month (November) and when "MPR Source.xls" was opened from the folder "C:\Temp", it opened "MPR_2009-10.xls" also in "C:\Temp\" and it created a new worksheet in MPR_2009-10.xls, named it Oct_09 and copied the contents of the worksheet "MPR" in the "MPR Source.xls" workbook, to it.

The only differences I can see between my original code and yours are:
a. you changed strMPRSource = ActiveWorkbook.Name
to
strMPRSource = "MPR Source.xls"

b. The name of the source workbook has a space in the name and not an underscore: "MPR Source.xls"

I presume that this is correct, i.e., your source workbook has a space and not an underscore in the name.

Changing ActiveWorkbook.name to the name of your file should not make any difference, as long as the name is correct.

You must have atpvbaen.xls checked, otherwise you will get an error.

You haven't told me what happens when you single step through the code with atpvbaen.xls checked.

Is there an error and where does it stop. If it doesn't stop which is the last line highlighted before it ends?

It would be helpful to look at the two strings:
strLastSaved and
strLastMonth

After the single step takes you past each of these lines, hover the cursor over each string and record the text that shows.

Regards


Report •

#19
October 26, 2009 at 21:56:15
I have to repeat this lines: With atpvbaen.xls unchecked I get error called 'Compile error: Sub or function not defined'. When i single step through the code; the eomonth of the code is selected and it stop there. I tried to comment out the On Error line but again the eomonth of next line is selected and stop there again.

With atpvbaen.xls checked, no error message at all but also the code is not doing anything what it is intended to do.

The problem with me is the fiscal year (April 2009 to March 2010)otherwise i have a solution at my hand by modifying the code i posted at top.

Let us close this chapter here. no more follow up.

Humar, Thanks for your replies and suggestions. I learnt someting from your suggestion thats what i need.


Report •

#20
October 27, 2009 at 05:54:11
Hi,

I appreciate that you need to move on, but I would like to say that with atpvbaen.xls unchecked you will get errors, and any time you spend on trying to stop the error will be wasted.

Leave atpvbaen.xls checked and work on the remaining code.

Good luck

Regards


Report •


Ask Question