Prev: Conditional Format to highlight rows
Next: if worksheet protected it return "text1"
From: Dave Peterson on 28 Feb 2010 13:13
First, I find this kind of stuff very dangerous. Too many things can contribute
to break the process. For instance, if you move a sheet, then it may get
renamed incorrectly.

But if you want, you could run a routine like this.

It assumes that the first sheet (leftmost) shouldn't be touched.
The second sheet (2nd from the left) contains the names in a1:Axx
And the 3rd (and the rest) should be named in that order.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wCtr As Long
Dim HowManyNames As Long

With Sheets(2) 'second position!
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

HowManyNames = myRng.Cells.Count

If HowManyNames <> ActiveWorkbook.Sheets.Count - 2 Then
MsgBox "the number of names doesn't match the number of sheets!"
Exit Sub
End If

wCtr = 3 'since we want to start with the 3rd sheet
For Each myCell In myRng.Cells
On Error Resume Next
Sheets(wCtr).Name = myCell.Value
If Err.Number <> 0 Then
Err.Clear
MsgBox "Error renaming the sheets"
Exit Sub
End If
On Error GoTo 0
wCtr = wCtr + 1
Next myCell

End Sub

You'll type in the names and then run the macro. If the list changes, you'll
have to rerun the macro.



If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Carl wrote:
>
> I am making a program in excel for some time cards then workbook starts with
> sheet one to change the date next sheet has the list of names with all the
> info that needs to be put on the timesheet that lists all the names in
> alphabetical order and the sheet that follow are the timecards i can get it
> to put all the info itto the sheet but what i need to do is get the sheet
> name tab to change to what the name from the list is so the tab names changes
> as the list changes can someone help please.
>
>

--

Dave Peterson
From: Bill on 28 Feb 2010 15:55
On 2/28/2010 10:13 AM, Dave Peterson wrote:
> First, I find this kind of stuff very dangerous. Too many things can contribute
> to break the process. For instance, if you move a sheet, then it may get
> renamed incorrectly.
>
> But if you want, you could run a routine like this.
>
> It assumes that the first sheet (leftmost) shouldn't be touched.
> The second sheet (2nd from the left) contains the names in a1:Axx
> And the 3rd (and the rest) should be named in that order.
>
> Option Explicit
> Sub testme()
> Dim myRng As Range
> Dim myCell As Range
> Dim wCtr As Long
> Dim HowManyNames As Long
>
> With Sheets(2) 'second position!
> Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
> HowManyNames = myRng.Cells.Count
>
> If HowManyNames<> ActiveWorkbook.Sheets.Count - 2 Then
> MsgBox "the number of names doesn't match the number of sheets!"
> Exit Sub
> End If
>
> wCtr = 3 'since we want to start with the 3rd sheet
> For Each myCell In myRng.Cells
> On Error Resume Next
> Sheets(wCtr).Name = myCell.Value
> If Err.Number<> 0 Then
> Err.Clear
> MsgBox "Error renaming the sheets"
> Exit Sub
> End If
> On Error GoTo 0
> wCtr = wCtr + 1
> Next myCell
>
> End Sub
>
> You'll type in the names and then run the macro. If the list changes, you'll
> have to rerun the macro.
>
>
>
> If you're new to macros:
>
> Debra Dalgleish has some notes how to implement macros here:
> http://www.contextures.com/xlvba01.html
>
> David McRitchie has an intro to macros:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Ron de Bruin's intro to macros:
> http://www.rondebruin.nl/code.htm
>
> (General, Regular and Standard modules all describe the same thing.)
>
> Carl wrote:
>>
>> I am making a program in excel for some time cards then workbook starts with
>> sheet one to change the date next sheet has the list of names with all the
>> info that needs to be put on the timesheet that lists all the names in
>> alphabetical order and the sheet that follow are the timecards i can get it
>> to put all the info itto the sheet but what i need to do is get the sheet
>> name tab to change to what the name from the list is so the tab names changes
>> as the list changes can someone help please.
>>
>>
>
Thanks, Dave, for trying to answer the guy's questions and giving him
some links to macro creation.

Bill
From: StickThatInYourPipeAndSmokeIt on 28 Feb 2010 16:45
On Sun, 28 Feb 2010 12:55:04 -0800, Bill <billns(a)nsverizon.net> wrote:

>On 2/28/2010 10:13 AM, Dave Peterson wrote:
>> First, I find this kind of stuff very dangerous. Too many things can contribute
>> to break the process. For instance, if you move a sheet, then it may get
>> renamed incorrectly.
>>
>> But if you want, you could run a routine like this.
>>
>> It assumes that the first sheet (leftmost) shouldn't be touched.
>> The second sheet (2nd from the left) contains the names in a1:Axx
>> And the 3rd (and the rest) should be named in that order.
>>
>> Option Explicit
>> Sub testme()
>> Dim myRng As Range
>> Dim myCell As Range
>> Dim wCtr As Long
>> Dim HowManyNames As Long
>>
>> With Sheets(2) 'second position!
>> Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
>> End With
>>
>> HowManyNames = myRng.Cells.Count
>>
>> If HowManyNames<> ActiveWorkbook.Sheets.Count - 2 Then
>> MsgBox "the number of names doesn't match the number of sheets!"
>> Exit Sub
>> End If
>>
>> wCtr = 3 'since we want to start with the 3rd sheet
>> For Each myCell In myRng.Cells
>> On Error Resume Next
>> Sheets(wCtr).Name = myCell.Value
>> If Err.Number<> 0 Then
>> Err.Clear
>> MsgBox "Error renaming the sheets"
>> Exit Sub
>> End If
>> On Error GoTo 0
>> wCtr = wCtr + 1
>> Next myCell
>>
>> End Sub
>>
>> You'll type in the names and then run the macro. If the list changes, you'll
>> have to rerun the macro.
>>
>>
>>
>> If you're new to macros:
>>
>> Debra Dalgleish has some notes how to implement macros here:
>> http://www.contextures.com/xlvba01.html
>>
>> David McRitchie has an intro to macros:
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>> Ron de Bruin's intro to macros:
>> http://www.rondebruin.nl/code.htm
>>
>> (General, Regular and Standard modules all describe the same thing.)
>>
>> Carl wrote:
>>>
>>> I am making a program in excel for some time cards then workbook starts with
>>> sheet one to change the date next sheet has the list of names with all the
>>> info that needs to be put on the timesheet that lists all the names in
>>> alphabetical order and the sheet that follow are the timecards i can get it
>>> to put all the info itto the sheet but what i need to do is get the sheet
>>> name tab to change to what the name from the list is so the tab names changes
>>> as the list changes can someone help please.
>>>
>>>
>>
>Thanks, Dave, for trying to answer the guy's questions and giving him
>some links to macro creation.
>
>Bill

You forgot to leave out the punctuation, punk. He won't be able to
read that.
From: Dave Peterson on 28 Feb 2010 17:28
You're welcome, Bill.

Maybe it'll help. But I still don't like this kind of macro <vbg>.

Bill wrote:
>
> On 2/28/2010 10:13 AM, Dave Peterson wrote:
> > First, I find this kind of stuff very dangerous. Too many things can contribute
> > to break the process. For instance, if you move a sheet, then it may get
> > renamed incorrectly.
> >
> > But if you want, you could run a routine like this.
> >
> > It assumes that the first sheet (leftmost) shouldn't be touched.
> > The second sheet (2nd from the left) contains the names in a1:Axx
> > And the 3rd (and the rest) should be named in that order.
> >
> > Option Explicit
> > Sub testme()
> > Dim myRng As Range
> > Dim myCell As Range
> > Dim wCtr As Long
> > Dim HowManyNames As Long
> >
> > With Sheets(2) 'second position!
> > Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> > End With
> >
> > HowManyNames = myRng.Cells.Count
> >
> > If HowManyNames<> ActiveWorkbook.Sheets.Count - 2 Then
> > MsgBox "the number of names doesn't match the number of sheets!"
> > Exit Sub
> > End If
> >
> > wCtr = 3 'since we want to start with the 3rd sheet
> > For Each myCell In myRng.Cells
> > On Error Resume Next
> > Sheets(wCtr).Name = myCell.Value
> > If Err.Number<> 0 Then
> > Err.Clear
> > MsgBox "Error renaming the sheets"
> > Exit Sub
> > End If
> > On Error GoTo 0
> > wCtr = wCtr + 1
> > Next myCell
> >
> > End Sub
> >
> > You'll type in the names and then run the macro. If the list changes, you'll
> > have to rerun the macro.
> >
> >
> >
> > If you're new to macros:
> >
> > Debra Dalgleish has some notes how to implement macros here:
> > http://www.contextures.com/xlvba01.html
> >
> > David McRitchie has an intro to macros:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Ron de Bruin's intro to macros:
> > http://www.rondebruin.nl/code.htm
> >
> > (General, Regular and Standard modules all describe the same thing.)
> >
> > Carl wrote:
> >>
> >> I am making a program in excel for some time cards then workbook starts with
> >> sheet one to change the date next sheet has the list of names with all the
> >> info that needs to be put on the timesheet that lists all the names in
> >> alphabetical order and the sheet that follow are the timecards i can get it
> >> to put all the info itto the sheet but what i need to do is get the sheet
> >> name tab to change to what the name from the list is so the tab names changes
> >> as the list changes can someone help please.
> >>
> >>
> >
> Thanks, Dave, for trying to answer the guy's questions and giving him
> some links to macro creation.
>
> Bill

--

Dave Peterson
From: John on 28 Feb 2010 17:34
Good Man, Dave
At least one person is trying to help.
Regards
John
"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4B8AB22D.B2147E0B(a)verizonXSPAM.net...
> First, I find this kind of stuff very dangerous. Too many things can
> contribute
> to break the process. For instance, if you move a sheet, then it may get
> renamed incorrectly.
>
> But if you want, you could run a routine like this.
>
> It assumes that the first sheet (leftmost) shouldn't be touched.
> The second sheet (2nd from the left) contains the names in a1:Axx
> And the 3rd (and the rest) should be named in that order.
>
> Option Explicit
> Sub testme()
> Dim myRng As Range
> Dim myCell As Range
> Dim wCtr As Long
> Dim HowManyNames As Long
>
> With Sheets(2) 'second position!
> Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
> HowManyNames = myRng.Cells.Count
>
> If HowManyNames <> ActiveWorkbook.Sheets.Count - 2 Then
> MsgBox "the number of names doesn't match the number of sheets!"
> Exit Sub
> End If
>
> wCtr = 3 'since we want to start with the 3rd sheet
> For Each myCell In myRng.Cells
> On Error Resume Next
> Sheets(wCtr).Name = myCell.Value
> If Err.Number <> 0 Then
> Err.Clear
> MsgBox "Error renaming the sheets"
> Exit Sub
> End If
> On Error GoTo 0
> wCtr = wCtr + 1
> Next myCell
>
> End Sub
>
> You'll type in the names and then run the macro. If the list changes, you'll
> have to rerun the macro.
>
>
>
> If you're new to macros:
>
> Debra Dalgleish has some notes how to implement macros here:
> http://www.contextures.com/xlvba01.html
>
> David McRitchie has an intro to macros:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Ron de Bruin's intro to macros:
> http://www.rondebruin.nl/code.htm
>
> (General, Regular and Standard modules all describe the same thing.)
>
> Carl wrote:
>>
>> I am making a program in excel for some time cards then workbook starts with
>> sheet one to change the date next sheet has the list of names with all the
>> info that needs to be put on the timesheet that lists all the names in
>> alphabetical order and the sheet that follow are the timecards i can get it
>> to put all the info itto the sheet but what i need to do is get the sheet
>> name tab to change to what the name from the list is so the tab names changes
>> as the list changes can someone help please.
>>
>>
>
> --
>
> Dave Peterson

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Conditional Format to highlight rows
Next: if worksheet protected it return "text1"