excel sheet name

[mikeExcel]

Hi there,

I’m doing my accounting with Excel, in a work book, I have 12 sheets
per book, one for each months, the name of each sheet is the month &
year e.g. Feb11 ( for February 2011).
I’m looking a way to automatically up date the sheets’ name to the
appropriate year as every year I’m using the same workbook, just
change the name & values. I have 7 workbooks with 12 sheets (months/
year name) each so changing this manually it’s a pain. Any help would
be appreciated.

 

[Don Guillett]

Hi there,

I’m doing my accounting with Excel, in a work book, I have 12 sheets
per book, one for each months, the name of each sheet is the month &
year e.g. Feb11 ( for February 2011).
I’m looking a way to automatically up date the sheets’ name to the
appropriate year as every year I’m using the same workbook, just
change the name & values. I have 7 workbooks with 12 sheets (months/
year name) each so changing this manually it’s a pain. Any help would
be appreciated.

I would have had all months on ONE sheet 2011 and filtered as needed
with subtotals but, if you insist then this macro will do it for each
file. There are other ways to do this
by reference to a central cell with 2011 changing to 2012 but more
complicated.

Sub renamesheets()
For Each sh In Worksheets
sh.Name = Left(sh.Name, 3) & "12"
Next
End Sub

 

[GS]

mikeExcel was thinking very hard :

Hi there,

I’m doing my accounting with Excel, in a work book, I have 12 sheets
per book, one for each months, the name of each sheet is the month &
year e.g. Feb11 ( for February 2011).
I’m looking a way to automatically up date the sheets’ name to the
appropriate year as every year I’m using the same workbook, just
change the name & values. I have 7 workbooks with 12 sheets (months/
year name) each so changing this manually it’s a pain. Any help would
be appreciated.

My simple bookkeeping addin has all months on each
Income/Expenses/Summary sheet and the workbook period is 1 fiscal year.
That happens to be Jan to Dec since it matches a personal tax year, but
it could be set to any 12 month period.

The reason the workbook covers only 1 fiscal year i because it has
multiple sheets and so too many to track more than 1 fiscal year. (In
addition to the above 3 sheets there's Lists/Customers/Vendors/Taxes
and however many bank ledger sheets I need as I use a separate bank
ledger sheet for each account)

Since this is an addin there's no fuss turning over a new fiscal year
because it happens with 1 click of a menu once the current year is
greater than the stored year. This implements a perpetual workbook
where the previous fiscal year is 'saved as' the current year's working
file and all input ranges are cleared, bank ledger sheets replaced, and
the INI file is updated with new info. The addin also does similar for
invoices but these are kept in a separate workbook also for the
appropriate fiscal year. A new invoice file is started from a default
template stored in the INI file.

This is repeated for each organization (company) the addin is used for.
(It supports multiple orgs and invoice templates that can be switched
to on the fly) The file naming convention is...

OrgName_YYYY.xls - the Income/Expense workbook file
OrgName_YYYY_Invoices.xls - the invoice workbook file

Very simple to use; just enter expenses/income on the appropriate
sheets and everything calcs automatically. Also supports 2 taxes and
multiple currency. All this is easy to manage in a fiscal-based system.
I can't imagine why you want to handle months on separate sheets. My
month columns automatically collect amounts based on transaction date,
meaning I don't even have to enter transactions in a specific order if
I don't want. However, I can insert transaction rows wherever I need
them so keeping data grouped by month and sorted by date is really
easy.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion