macro help

  • Thread starter Thread starter Mark W.
  • Start date Start date
M

Mark W.

I have a workbook with 52 sheets (52 weeks).
In cell A1 I have a week number "1", "2" etc.
I want the sheets to change their names according to that number,
i.e. "Group #.... and the number is taken from A1.
How do I do this?
This is the workbook that gives me a list of people on vacation on a
given week. I have everything else worked out just want to rename
those tabs so next year when I enter group # in A1 on the first sheet
it will change the all the names of tabs according to it (I have to
update this manually every year because it not always starts with
group #1). Other sheets automatically update once I enter the group
number on the first one (simple formula).
Oh, and one more thing:
Every sheet has a custom header that says "Vacation 2004"
Is there any way that the year number updates automatically according
to a year we are in? How to do this?
Right now I have to update every sheet manually every december.
If I had those things resolved I would have the whole file fill out
automatically.
Any help greatly appreciated

Mark
mw2@mindspringdotcom
 
Mark,

A macro could do what you want, and we'll write you one. But let me suggest
this first. Consider putting all the data into one sheet. It's a one-time
copy-paste operation, adding a column for week number. The advantage is
that all kinds of summariy tools will now be available that will not be when
the data is in multiple sheets. This includes pivot tables, subtotals,
autofilters. It's the stuff that makes Excel so powerful. An autofilter on
the week # column can reduce it to the equivalent of one of your week
sheets. And you can sort the sheet in a number of useful ways for
presentation of the data. Consider this. You'll be glad you did.
 
Mark,

A macro could do what you want, and we'll write you one. But let me suggest
this first. Consider putting all the data into one sheet. It's a one-time
copy-paste operation, adding a column for week number. The advantage is
that all kinds of summariy tools will now be available that will not be when
the data is in multiple sheets. This includes pivot tables, subtotals,
autofilters. It's the stuff that makes Excel so powerful. An autofilter on
the week # column can reduce it to the equivalent of one of your week
sheets. And you can sort the sheet in a number of useful ways for
presentation of the data. Consider this. You'll be glad you did.
Click to expand...


Well, it's not so easy.
There is a lot more info on every sheet so if I do what you suggest
it would make the sheet so wide..... it'd be a problem to print it
(I have to print it once it's set).
Besides to redo the whole thing now... no, forget it. It's just easier
to put the macro, I just don't know how to write it, don't know VBA.
Also I have another workbook where I'd like to use macro for renaming
sheets, just don't know how to start.
Thanks

Mark
mw2@mindspringdotcom
 
Mark,

Let me try again. I presume your weekly sheets have the same layout (same
column headings). In that case, you simply paste each week's rows, one
batch under another, into a single sheet. One set of column headings at the
top. It'll be no wider, but a lot longer as you add week's of stuff. As
you do that, put the week number into an added Week column, and copy it down
with the fill handle. That identifies the week for each record (row). You
turn on Autofilter, and when you want to deal with a single week's stuff,
you use that. You can print it, etc. There are ways to set it up for
totals, counts, etc., of the week(s) your currently working with. It's
quite dandy.

I do beg you reconsider. You'll almost certainly run into difficulties
later when you want to work with your data across multiple weeks, and it's
easy in a single table, and not easy at all in multiple tables.

We usually hide in the bushes of folks who resist, and when they come out,
we accost them and take them inside and make them change it. Then we have
coffee and they always thank us later.
 
Mark,

Let me try again. I presume your weekly sheets have the same layout (same
column headings). In that case, you simply paste each week's rows, one
batch under another, into a single sheet. One set of column headings at the
top. It'll be no wider, but a lot longer as you add week's of stuff. As
you do that, put the week number into an added Week column, and copy it down
with the fill handle. That identifies the week for each record (row). You
turn on Autofilter, and when you want to deal with a single week's stuff,
you use that. You can print it, etc. There are ways to set it up for
totals, counts, etc., of the week(s) your currently working with. It's
quite dandy.

I do beg you reconsider. You'll almost certainly run into difficulties
later when you want to work with your data across multiple weeks, and it's
easy in a single table, and not easy at all in multiple tables.

We usually hide in the bushes of folks who resist, and when they come out,
we accost them and take them inside and make them change it. Then we have
coffee and they always thank us later.
Click to expand...

OK Earl, I see what you mean.
I'll try that and see how it works. I think you are right.
It's easier to do anything on one sheet that multiple.
I have formulas referring to the previous sheet so if next year I
change the week # on the first sheet and the formulas change every
other sheet and then macro changes the names of the sheets I would
have to go and change all those sheets referring to the previous once
because they change their names.
Thanks for convincing me to do that.
I'll try and see how it works. It'll take me some time to redo the
whole workbook but with copy and paste it may not be so bad.
Thanks again

Mark
 
Back
Top