ngdaug
02-23-2009, 09:53 AM
I currently have a process for closing my month ends. Per the attached xls file, you can see that I have a master worksheet and then various worksheets that denote different categories. Journal entries are posted to the master with an alphabetic category and then an array recognizes the entry from the master and automatically copies it over to the individual tab. For example, some one may enter that some auto repairs were done for $100 on the 19th of March. On the master worksheet they will put A in the Category column, and that entry is copied over to the individual worksheet marked Auto. At the month end close, each individual worksheet's entries have to be added up for that specific month. Manually, I bold the final entry for that month in the total column, I had the three letter month abbreviation (ie Mar) in the column beside the total month and the cell directly beneath it I add the simple formula: =IF(C8="","",C8), thereby "restarting" the next months total column. If this is confusing please refer to the attached xls file.
Here is the tricky part. I have attempted to do that, per the vba code titled, "PrepareForNextMonth" and "EndofMonth", but I ran into a couple of problems:
First, our month end closes are done by a bank's month end close. Hence, some months it is the 15, others the 17 day of the month. It is NOT the final day of a month. For example, March's month close is April 17th. (March 15-April 17th). Currently, my vba looks at the date in the worksheet to determine which abbreviation to use . For example, if on the Auto sheet the last entry before the month end close was April 7th, it would put Apr beside that entry which is incorrect and should be Mar. I believe an apporpriate solution would be for the VBA to look at what I wrote on the Master and copy that as the appropriate month but haven't figured out how to do that intelligently.
Second, the vba code needs something written on each worksheet or errors out. There are a number of categories that are not used until the middle of the year, such as our Debt tab.
Third, there are times where we don't always post an entry for a particular category during the month. Hence our Groceries worksheet will have an entry for March, but not another one until May. I found that at times, my VBA would get confused if this occured.
Finally, I couldn't find a way to tell the VBA the exception to the rule. There is a category called Corp Capital, that we keep a running tally of, (an Income Statement account) instead of closing out the month. (In other words, just like the Master Worksheet, I want this tab left untouched.) I couldn't figure out a way to keep the code simple, yet keep this worksheet from being touched.
I realize that this might be confusing, but any help would be greatly appreciated. If you have any questions please please please feel free to respond to my post and I'll update you.
Thanks again everyone,
Nate
Here is the tricky part. I have attempted to do that, per the vba code titled, "PrepareForNextMonth" and "EndofMonth", but I ran into a couple of problems:
First, our month end closes are done by a bank's month end close. Hence, some months it is the 15, others the 17 day of the month. It is NOT the final day of a month. For example, March's month close is April 17th. (March 15-April 17th). Currently, my vba looks at the date in the worksheet to determine which abbreviation to use . For example, if on the Auto sheet the last entry before the month end close was April 7th, it would put Apr beside that entry which is incorrect and should be Mar. I believe an apporpriate solution would be for the VBA to look at what I wrote on the Master and copy that as the appropriate month but haven't figured out how to do that intelligently.
Second, the vba code needs something written on each worksheet or errors out. There are a number of categories that are not used until the middle of the year, such as our Debt tab.
Third, there are times where we don't always post an entry for a particular category during the month. Hence our Groceries worksheet will have an entry for March, but not another one until May. I found that at times, my VBA would get confused if this occured.
Finally, I couldn't find a way to tell the VBA the exception to the rule. There is a category called Corp Capital, that we keep a running tally of, (an Income Statement account) instead of closing out the month. (In other words, just like the Master Worksheet, I want this tab left untouched.) I couldn't figure out a way to keep the code simple, yet keep this worksheet from being touched.
I realize that this might be confusing, but any help would be greatly appreciated. If you have any questions please please please feel free to respond to my post and I'll update you.
Thanks again everyone,
Nate