Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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 WithMsgBox MonthName((Month(Date) + 11) Mod 12) & " sheets saved."
End If
'update date
Worksheets("Sheet5").Range("B1") = CStr(Date)
ThisWorkbook.Save
End Sub
0 
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
1
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)) ThenBut 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=Falsefor 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 ifThis 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
0
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.
0
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 SubI 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 messagesRegards
0
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.
0
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
0
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.
0
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
0
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
0
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.
1
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
0
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.
0
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
0
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.
0
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
0
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
0
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-10It 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
strLastMonthAfter the single step takes you past each of these lines, hover the cursor over each string and record the text that shows.
Regards
0
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.
0
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
0  |
 |
 |
| Login or Register to Reply | |
| Login | Register |
| Ads by Google |
