Board index » Excel Programming

All times are UTC



 changing 50 sheet names 
Author Message
James Schmid
#1 / 8
 changing 50 sheet names

Does anyone have an idea on shortening this code, it takes a while to rename
all these sheets, even if only 1 name is changed.
I wonder if it could change only the sheets that have the name changed?

any help would be appreciated.

Sub RunInvTabs()
    Sheet1.Name = Sheet102.Range("a1").Value
    Sheet2.Name = Sheet102.Range("a2").Value
    Sheet3.Name = Sheet102.Range("a3").Value
    Sheet4.Name = Sheet102.Range("a4").Value
    Sheet5.Name = Sheet102.Range("a5").Value
    Sheet6.Name = Sheet102.Range("a6").Value
    Sheet7.Name = Sheet102.Range("a7").Value
    Sheet8.Name = Sheet102.Range("a8").Value
    Sheet9.Name = Sheet102.Range("a9").Value
    Sheet10.Name = Sheet102.Range("a10").Value
    Sheet11.Name = Sheet102.Range("a11").Value
    Sheet12.Name = Sheet102.Range("a12").Value
    Sheet13.Name = Sheet102.Range("a13").Value
    Sheet14.Name = Sheet102.Range("a14").Value
    Sheet15.Name = Sheet102.Range("a15").Value
    Sheet16.Name = Sheet102.Range("a16").Value
    Sheet17.Name = Sheet102.Range("a17").Value
    Sheet18.Name = Sheet102.Range("a18").Value
    Sheet19.Name = Sheet102.Range("a19").Value
    Sheet20.Name = Sheet102.Range("a20").Value
    Sheet21.Name = Sheet102.Range("a21").Value
    Sheet22.Name = Sheet102.Range("a22").Value
    Sheet23.Name = Sheet102.Range("a23").Value
    Sheet24.Name = Sheet102.Range("a24").Value
    Sheet25.Name = Sheet102.Range("a25").Value
    Sheet26.Name = Sheet102.Range("a26").Value
    Sheet27.Name = Sheet102.Range("a27").Value
    Sheet28.Name = Sheet102.Range("a28").Value
    Sheet29.Name = Sheet102.Range("a29").Value
    Sheet30.Name = Sheet102.Range("a30").Value
    Sheet31.Name = Sheet102.Range("a31").Value
    Sheet32.Name = Sheet102.Range("a32").Value
    Sheet33.Name = Sheet102.Range("a33").Value
    Sheet34.Name = Sheet102.Range("a34").Value
    Sheet35.Name = Sheet102.Range("a35").Value
    Sheet36.Name = Sheet102.Range("a36").Value
    Sheet37.Name = Sheet102.Range("a37").Value
    Sheet38.Name = Sheet102.Range("a38").Value
    Sheet39.Name = Sheet102.Range("a39").Value
    Sheet40.Name = Sheet102.Range("a40").Value
    Sheet41.Name = Sheet102.Range("a41").Value
    Sheet42.Name = Sheet102.Range("a42").Value
    Sheet43.Name = Sheet102.Range("a43").Value
    Sheet44.Name = Sheet102.Range("a44").Value
    Sheet45.Name = Sheet102.Range("a45").Value
    Sheet46.Name = Sheet102.Range("a46").Value
    Sheet47.Name = Sheet102.Range("a47").Value
    Sheet48.Name = Sheet102.Range("a48").Value
    Sheet49.Name = Sheet102.Range("a49").Value
    Sheet50.Name = Sheet102.Range("a50").Value
End Sub

--

Thank you,

James P. Schmidt



Thu, 08 Jul 2004 12:37:04 GMT  
WizardOfAu
#2 / 8
 changing 50 sheet names
Dear James,

You could try the following:

Sub RunInvTabs()
Application.ScreenUpdating = False
For i = 1 To 50
    Sheets("Sheet" & i).Name = Sheets("Sheet102").Range("A" & i).Value
Next i
Application.ScreenUpdating = True
End Sub

Regards,

Robbie


Quote:
> Does anyone have an idea on shortening this code, it takes a while to
rename
> all these sheets, even if only 1 name is changed.
> I wonder if it could change only the sheets that have the name changed?

> any help would be appreciated.

> Sub RunInvTabs()
>     Sheet1.Name = Sheet102.Range("a1").Value
>     Sheet2.Name = Sheet102.Range("a2").Value
>     Sheet3.Name = Sheet102.Range("a3").Value
>     Sheet4.Name = Sheet102.Range("a4").Value
>     Sheet5.Name = Sheet102.Range("a5").Value
>     Sheet6.Name = Sheet102.Range("a6").Value
>     Sheet7.Name = Sheet102.Range("a7").Value
>     Sheet8.Name = Sheet102.Range("a8").Value
>     Sheet9.Name = Sheet102.Range("a9").Value
>     Sheet10.Name = Sheet102.Range("a10").Value
>     Sheet11.Name = Sheet102.Range("a11").Value
>     Sheet12.Name = Sheet102.Range("a12").Value
>     Sheet13.Name = Sheet102.Range("a13").Value
>     Sheet14.Name = Sheet102.Range("a14").Value
>     Sheet15.Name = Sheet102.Range("a15").Value
>     Sheet16.Name = Sheet102.Range("a16").Value
>     Sheet17.Name = Sheet102.Range("a17").Value
>     Sheet18.Name = Sheet102.Range("a18").Value
>     Sheet19.Name = Sheet102.Range("a19").Value
>     Sheet20.Name = Sheet102.Range("a20").Value
>     Sheet21.Name = Sheet102.Range("a21").Value
>     Sheet22.Name = Sheet102.Range("a22").Value
>     Sheet23.Name = Sheet102.Range("a23").Value
>     Sheet24.Name = Sheet102.Range("a24").Value
>     Sheet25.Name = Sheet102.Range("a25").Value
>     Sheet26.Name = Sheet102.Range("a26").Value
>     Sheet27.Name = Sheet102.Range("a27").Value
>     Sheet28.Name = Sheet102.Range("a28").Value
>     Sheet29.Name = Sheet102.Range("a29").Value
>     Sheet30.Name = Sheet102.Range("a30").Value
>     Sheet31.Name = Sheet102.Range("a31").Value
>     Sheet32.Name = Sheet102.Range("a32").Value
>     Sheet33.Name = Sheet102.Range("a33").Value
>     Sheet34.Name = Sheet102.Range("a34").Value
>     Sheet35.Name = Sheet102.Range("a35").Value
>     Sheet36.Name = Sheet102.Range("a36").Value
>     Sheet37.Name = Sheet102.Range("a37").Value
>     Sheet38.Name = Sheet102.Range("a38").Value
>     Sheet39.Name = Sheet102.Range("a39").Value
>     Sheet40.Name = Sheet102.Range("a40").Value
>     Sheet41.Name = Sheet102.Range("a41").Value
>     Sheet42.Name = Sheet102.Range("a42").Value
>     Sheet43.Name = Sheet102.Range("a43").Value
>     Sheet44.Name = Sheet102.Range("a44").Value
>     Sheet45.Name = Sheet102.Range("a45").Value
>     Sheet46.Name = Sheet102.Range("a46").Value
>     Sheet47.Name = Sheet102.Range("a47").Value
>     Sheet48.Name = Sheet102.Range("a48").Value
>     Sheet49.Name = Sheet102.Range("a49").Value
>     Sheet50.Name = Sheet102.Range("a50").Value
> End Sub

> --

> Thank you,

> James P. Schmidt



Thu, 08 Jul 2004 13:03:32 GMT  
James Schmid
#3 / 8
 changing 50 sheet names
It returned Subscript Out Of Range Error 9

What Could I Have Done Wrong?

Thanks

James


Quote:
> Dear James,

> You could try the following:

> Sub RunInvTabs()
> Application.ScreenUpdating = False
> For i = 1 To 50
>     Sheets("Sheet" & i).Name = Sheets("Sheet102").Range("A" & i).Value
> Next i
> Application.ScreenUpdating = True
> End Sub

> Regards,

> Robbie



> > Does anyone have an idea on shortening this code, it takes a while to
> rename
> > all these sheets, even if only 1 name is changed.
> > I wonder if it could change only the sheets that have the name changed?

> > any help would be appreciated.

> > Sub RunInvTabs()
> >     Sheet1.Name = Sheet102.Range("a1").Value
> >     Sheet2.Name = Sheet102.Range("a2").Value
> >     Sheet3.Name = Sheet102.Range("a3").Value
> >     Sheet4.Name = Sheet102.Range("a4").Value
> >     Sheet5.Name = Sheet102.Range("a5").Value
> >     Sheet6.Name = Sheet102.Range("a6").Value
> >     Sheet7.Name = Sheet102.Range("a7").Value
> >     Sheet8.Name = Sheet102.Range("a8").Value
> >     Sheet9.Name = Sheet102.Range("a9").Value
> >     Sheet10.Name = Sheet102.Range("a10").Value
> >     Sheet11.Name = Sheet102.Range("a11").Value
> >     Sheet12.Name = Sheet102.Range("a12").Value
> >     Sheet13.Name = Sheet102.Range("a13").Value
> >     Sheet14.Name = Sheet102.Range("a14").Value
> >     Sheet15.Name = Sheet102.Range("a15").Value
> >     Sheet16.Name = Sheet102.Range("a16").Value
> >     Sheet17.Name = Sheet102.Range("a17").Value
> >     Sheet18.Name = Sheet102.Range("a18").Value
> >     Sheet19.Name = Sheet102.Range("a19").Value
> >     Sheet20.Name = Sheet102.Range("a20").Value
> >     Sheet21.Name = Sheet102.Range("a21").Value
> >     Sheet22.Name = Sheet102.Range("a22").Value
> >     Sheet23.Name = Sheet102.Range("a23").Value
> >     Sheet24.Name = Sheet102.Range("a24").Value
> >     Sheet25.Name = Sheet102.Range("a25").Value
> >     Sheet26.Name = Sheet102.Range("a26").Value
> >     Sheet27.Name = Sheet102.Range("a27").Value
> >     Sheet28.Name = Sheet102.Range("a28").Value
> >     Sheet29.Name = Sheet102.Range("a29").Value
> >     Sheet30.Name = Sheet102.Range("a30").Value
> >     Sheet31.Name = Sheet102.Range("a31").Value
> >     Sheet32.Name = Sheet102.Range("a32").Value
> >     Sheet33.Name = Sheet102.Range("a33").Value
> >     Sheet34.Name = Sheet102.Range("a34").Value
> >     Sheet35.Name = Sheet102.Range("a35").Value
> >     Sheet36.Name = Sheet102.Range("a36").Value
> >     Sheet37.Name = Sheet102.Range("a37").Value
> >     Sheet38.Name = Sheet102.Range("a38").Value
> >     Sheet39.Name = Sheet102.Range("a39").Value
> >     Sheet40.Name = Sheet102.Range("a40").Value
> >     Sheet41.Name = Sheet102.Range("a41").Value
> >     Sheet42.Name = Sheet102.Range("a42").Value
> >     Sheet43.Name = Sheet102.Range("a43").Value
> >     Sheet44.Name = Sheet102.Range("a44").Value
> >     Sheet45.Name = Sheet102.Range("a45").Value
> >     Sheet46.Name = Sheet102.Range("a46").Value
> >     Sheet47.Name = Sheet102.Range("a47").Value
> >     Sheet48.Name = Sheet102.Range("a48").Value
> >     Sheet49.Name = Sheet102.Range("a49").Value
> >     Sheet50.Name = Sheet102.Range("a50").Value
> > End Sub

> > --

> > Thank you,

> > James P. Schmidt



Thu, 08 Jul 2004 14:14:35 GMT  
WizardOfAu
#4 / 8
 changing 50 sheet names
Dear James,

The error 9 message is being caused because there are no sheets named
"Sheet1" etc in your workbook. I should have realised you were referring to
sheet number 1 in the worksheets collection.

Sub RunInvTabs()
Application.ScreenUpdating = False
For i = 1 To 50
     Sheets(i).Name = Sheets(102).Range("A" & i).Value
Next i
Application.ScreenUpdating = True
End Sub

Regards,

Robbie


Quote:
> It returned Subscript Out Of Range Error 9

> What Could I Have Done Wrong?

> Thanks

> James



> > Dear James,

> > You could try the following:

> > Sub RunInvTabs()
> > Application.ScreenUpdating = False
> > For i = 1 To 50
> >     Sheets("Sheet" & i).Name = Sheets("Sheet102").Range("A" & i).Value
> > Next i
> > Application.ScreenUpdating = True
> > End Sub

> > Regards,

> > Robbie



> > > Does anyone have an idea on shortening this code, it takes a while to
> > rename
> > > all these sheets, even if only 1 name is changed.
> > > I wonder if it could change only the sheets that have the name
changed?

> > > any help would be appreciated.

> > > Sub RunInvTabs()
> > >     Sheet1.Name = Sheet102.Range("a1").Value
> > >     Sheet2.Name = Sheet102.Range("a2").Value
> > >     Sheet3.Name = Sheet102.Range("a3").Value
> > >     Sheet4.Name = Sheet102.Range("a4").Value
> > >     Sheet5.Name = Sheet102.Range("a5").Value
> > >     Sheet6.Name = Sheet102.Range("a6").Value
> > >     Sheet7.Name = Sheet102.Range("a7").Value
> > >     Sheet8.Name = Sheet102.Range("a8").Value
> > >     Sheet9.Name = Sheet102.Range("a9").Value
> > >     Sheet10.Name = Sheet102.Range("a10").Value
> > >     Sheet11.Name = Sheet102.Range("a11").Value
> > >     Sheet12.Name = Sheet102.Range("a12").Value
> > >     Sheet13.Name = Sheet102.Range("a13").Value
> > >     Sheet14.Name = Sheet102.Range("a14").Value
> > >     Sheet15.Name = Sheet102.Range("a15").Value
> > >     Sheet16.Name = Sheet102.Range("a16").Value
> > >     Sheet17.Name = Sheet102.Range("a17").Value
> > >     Sheet18.Name = Sheet102.Range("a18").Value
> > >     Sheet19.Name = Sheet102.Range("a19").Value
> > >     Sheet20.Name = Sheet102.Range("a20").Value
> > >     Sheet21.Name = Sheet102.Range("a21").Value
> > >     Sheet22.Name = Sheet102.Range("a22").Value
> > >     Sheet23.Name = Sheet102.Range("a23").Value
> > >     Sheet24.Name = Sheet102.Range("a24").Value
> > >     Sheet25.Name = Sheet102.Range("a25").Value
> > >     Sheet26.Name = Sheet102.Range("a26").Value
> > >     Sheet27.Name = Sheet102.Range("a27").Value
> > >     Sheet28.Name = Sheet102.Range("a28").Value
> > >     Sheet29.Name = Sheet102.Range("a29").Value
> > >     Sheet30.Name = Sheet102.Range("a30").Value
> > >     Sheet31.Name = Sheet102.Range("a31").Value
> > >     Sheet32.Name = Sheet102.Range("a32").Value
> > >     Sheet33.Name = Sheet102.Range("a33").Value
> > >     Sheet34.Name = Sheet102.Range("a34").Value
> > >     Sheet35.Name = Sheet102.Range("a35").Value
> > >     Sheet36.Name = Sheet102.Range("a36").Value
> > >     Sheet37.Name = Sheet102.Range("a37").Value
> > >     Sheet38.Name = Sheet102.Range("a38").Value
> > >     Sheet39.Name = Sheet102.Range("a39").Value
> > >     Sheet40.Name = Sheet102.Range("a40").Value
> > >     Sheet41.Name = Sheet102.Range("a41").Value
> > >     Sheet42.Name = Sheet102.Range("a42").Value
> > >     Sheet43.Name = Sheet102.Range("a43").Value
> > >     Sheet44.Name = Sheet102.Range("a44").Value
> > >     Sheet45.Name = Sheet102.Range("a45").Value
> > >     Sheet46.Name = Sheet102.Range("a46").Value
> > >     Sheet47.Name = Sheet102.Range("a47").Value
> > >     Sheet48.Name = Sheet102.Range("a48").Value
> > >     Sheet49.Name = Sheet102.Range("a49").Value
> > >     Sheet50.Name = Sheet102.Range("a50").Value
> > > End Sub

> > > --

> > > Thank you,

> > > James P. Schmidt



Thu, 08 Jul 2004 13:35:52 GMT  
James Schmid
#5 / 8
 changing 50 sheet names
Sheets(i) returns error 9 , I tried changing sheets(i) to sheet(i) but still
fails.
I am trying to use vba   "Sheet1"   name instead of workbook "Sheet1"
so combining Sheet with (i) returns "Sheets1"  which I also do not have.

Interesting problem

If someone has a solution, Please post.


Quote:
> Dear James,

> The error 9 message is being caused because there are no sheets named
> "Sheet1" etc in your workbook. I should have realised you were referring
to
> sheet number 1 in the worksheets collection.

> Sub RunInvTabs()
> Application.ScreenUpdating = False
> For i = 1 To 50
>      Sheets(i).Name = Sheets(102).Range("A" & i).Value
> Next i
> Application.ScreenUpdating = True
> End Sub

> Regards,

> Robbie



> > It returned Subscript Out Of Range Error 9

> > What Could I Have Done Wrong?

> > Thanks

> > James



> > > Dear James,

> > > You could try the following:

> > > Sub RunInvTabs()
> > > Application.ScreenUpdating = False
> > > For i = 1 To 50
> > >     Sheets("Sheet" & i).Name = Sheets("Sheet102").Range("A" & i).Value
> > > Next i
> > > Application.ScreenUpdating = True
> > > End Sub

> > > Regards,

> > > Robbie



> > > > Does anyone have an idea on shortening this code, it takes a while
to
> > > rename
> > > > all these sheets, even if only 1 name is changed.
> > > > I wonder if it could change only the sheets that have the name
> changed?

> > > > any help would be appreciated.

> > > > Sub RunInvTabs()
> > > >     Sheet1.Name = Sheet102.Range("a1").Value
> > > >     Sheet2.Name = Sheet102.Range("a2").Value
> > > >     Sheet3.Name = Sheet102.Range("a3").Value
> > > >     Sheet4.Name = Sheet102.Range("a4").Value
> > > >     Sheet5.Name = Sheet102.Range("a5").Value
> > > >     Sheet6.Name = Sheet102.Range("a6").Value
> > > >     Sheet7.Name = Sheet102.Range("a7").Value
> > > >     Sheet8.Name = Sheet102.Range("a8").Value
> > > >     Sheet9.Name = Sheet102.Range("a9").Value
> > > >     Sheet10.Name = Sheet102.Range("a10").Value
> > > >     Sheet11.Name = Sheet102.Range("a11").Value
> > > >     Sheet12.Name = Sheet102.Range("a12").Value
> > > >     Sheet13.Name = Sheet102.Range("a13").Value
> > > >     Sheet14.Name = Sheet102.Range("a14").Value
> > > >     Sheet15.Name = Sheet102.Range("a15").Value
> > > >     Sheet16.Name = Sheet102.Range("a16").Value
> > > >     Sheet17.Name = Sheet102.Range("a17").Value
> > > >     Sheet18.Name = Sheet102.Range("a18").Value
> > > >     Sheet19.Name = Sheet102.Range("a19").Value
> > > >     Sheet20.Name = Sheet102.Range("a20").Value
> > > >     Sheet21.Name = Sheet102.Range("a21").Value
> > > >     Sheet22.Name = Sheet102.Range("a22").Value
> > > >     Sheet23.Name = Sheet102.Range("a23").Value
> > > >     Sheet24.Name = Sheet102.Range("a24").Value
> > > >     Sheet25.Name = Sheet102.Range("a25").Value
> > > >     Sheet26.Name = Sheet102.Range("a26").Value
> > > >     Sheet27.Name = Sheet102.Range("a27").Value
> > > >     Sheet28.Name = Sheet102.Range("a28").Value
> > > >     Sheet29.Name = Sheet102.Range("a29").Value
> > > >     Sheet30.Name = Sheet102.Range("a30").Value
> > > >     Sheet31.Name = Sheet102.Range("a31").Value
> > > >     Sheet32.Name = Sheet102.Range("a32").Value
> > > >     Sheet33.Name = Sheet102.Range("a33").Value
> > > >     Sheet34.Name = Sheet102.Range("a34").Value
> > > >     Sheet35.Name = Sheet102.Range("a35").Value
> > > >     Sheet36.Name = Sheet102.Range("a36").Value
> > > >     Sheet37.Name = Sheet102.Range("a37").Value
> > > >     Sheet38.Name = Sheet102.Range("a38").Value
> > > >     Sheet39.Name = Sheet102.Range("a39").Value
> > > >     Sheet40.Name = Sheet102.Range("a40").Value
> > > >     Sheet41.Name = Sheet102.Range("a41").Value
> > > >     Sheet42.Name = Sheet102.Range("a42").Value
> > > >     Sheet43.Name = Sheet102.Range("a43").Value
> > > >     Sheet44.Name = Sheet102.Range("a44").Value
> > > >     Sheet45.Name = Sheet102.Range("a45").Value
> > > >     Sheet46.Name = Sheet102.Range("a46").Value
> > > >     Sheet47.Name = Sheet102.Range("a47").Value
> > > >     Sheet48.Name = Sheet102.Range("a48").Value
> > > >     Sheet49.Name = Sheet102.Range("a49").Value
> > > >     Sheet50.Name = Sheet102.Range("a50").Value
> > > > End Sub

> > > > --

> > > > Thank you,

> > > > James P. Schmidt



Thu, 08 Jul 2004 15:04:01 GMT  
Alan Perkin
#6 / 8
 changing 50 sheet names
In a file with what appears to be at least 102 worksheets, renaming any one
of the worksheets is going to take a while, especially if a formula refers
to it and the calculation mode is set to automatic.

You might find that simply changing that at will help.  You can do that
either manually through the options, or by changing your macro to look like
this:

Sub RunInvTabs()
    ' turn off automatic calculation
    Application.Calculation = xlCalculationManual

    ' rename the worksheets
    Sheet1.Name = Sheet102.Range("a1").Value
    :
    :
    :
    Sheet50.Name = Sheet102.Range("a50").Value

    ' turn automatic calculation back on
    Application.Calculation = xlCalculationAutomatic
End Sub

HTH

Alan P.


Quote:
> Sheets(i) returns error 9 , I tried changing sheets(i) to sheet(i) but
still
> fails.
> I am trying to use vba   "Sheet1"   name instead of workbook "Sheet1"
> so combining Sheet with (i) returns "Sheets1"  which I also do not have.

> Interesting problem

> If someone has a solution, Please post.



> > Dear James,

> > The error 9 message is being caused because there are no sheets named
> > "Sheet1" etc in your workbook. I should have realised you were referring
> to
> > sheet number 1 in the worksheets collection.

> > Sub RunInvTabs()
> > Application.ScreenUpdating = False
> > For i = 1 To 50
> >      Sheets(i).Name = Sheets(102).Range("A" & i).Value
> > Next i
> > Application.ScreenUpdating = True
> > End Sub

> > Regards,

> > Robbie



> > > It returned Subscript Out Of Range Error 9

> > > What Could I Have Done Wrong?

> > > Thanks

> > > James



> > > > Dear James,

> > > > You could try the following:

> > > > Sub RunInvTabs()
> > > > Application.ScreenUpdating = False
> > > > For i = 1 To 50
> > > >     Sheets("Sheet" & i).Name = Sheets("Sheet102").Range("A" &
i).Value
> > > > Next i
> > > > Application.ScreenUpdating = True
> > > > End Sub

> > > > Regards,

> > > > Robbie



> > > > > Does anyone have an idea on shortening this code, it takes a while
> to
> > > > rename
> > > > > all these sheets, even if only 1 name is changed.
> > > > > I wonder if it could change only the sheets that have the name
> > changed?

> > > > > any help would be appreciated.

> > > > > Sub RunInvTabs()
> > > > >     Sheet1.Name = Sheet102.Range("a1").Value
> > > > >     Sheet2.Name = Sheet102.Range("a2").Value
> > > > >     Sheet3.Name = Sheet102.Range("a3").Value
> > > > >     Sheet4.Name = Sheet102.Range("a4").Value
> > > > >     Sheet5.Name = Sheet102.Range("a5").Value
> > > > >     Sheet6.Name = Sheet102.Range("a6").Value
> > > > >     Sheet7.Name = Sheet102.Range("a7").Value
> > > > >     Sheet8.Name = Sheet102.Range("a8").Value
> > > > >     Sheet9.Name = Sheet102.Range("a9").Value
> > > > >     Sheet10.Name = Sheet102.Range("a10").Value
> > > > >     Sheet11.Name = Sheet102.Range("a11").Value
> > > > >     Sheet12.Name = Sheet102.Range("a12").Value
> > > > >     Sheet13.Name = Sheet102.Range("a13").Value
> > > > >     Sheet14.Name = Sheet102.Range("a14").Value
> > > > >     Sheet15.Name = Sheet102.Range("a15").Value
> > > > >     Sheet16.Name = Sheet102.Range("a16").Value
> > > > >     Sheet17.Name = Sheet102.Range("a17").Value
> > > > >     Sheet18.Name = Sheet102.Range("a18").Value
> > > > >     Sheet19.Name = Sheet102.Range("a19").Value
> > > > >     Sheet20.Name = Sheet102.Range("a20").Value
> > > > >     Sheet21.Name = Sheet102.Range("a21").Value
> > > > >     Sheet22.Name = Sheet102.Range("a22").Value
> > > > >     Sheet23.Name = Sheet102.Range("a23").Value
> > > > >     Sheet24.Name = Sheet102.Range("a24").Value
> > > > >     Sheet25.Name = Sheet102.Range("a25").Value
> > > > >     Sheet26.Name = Sheet102.Range("a26").Value
> > > > >     Sheet27.Name = Sheet102.Range("a27").Value
> > > > >     Sheet28.Name = Sheet102.Range("a28").Value
> > > > >     Sheet29.Name = Sheet102.Range("a29").Value
> > > > >     Sheet30.Name = Sheet102.Range("a30").Value
> > > > >     Sheet31.Name = Sheet102.Range("a31").Value
> > > > >     Sheet32.Name = Sheet102.Range("a32").Value
> > > > >     Sheet33.Name = Sheet102.Range("a33").Value
> > > > >     Sheet34.Name = Sheet102.Range("a34").Value
> > > > >     Sheet35.Name = Sheet102.Range("a35").Value
> > > > >     Sheet36.Name = Sheet102.Range("a36").Value
> > > > >     Sheet37.Name = Sheet102.Range("a37").Value
> > > > >     Sheet38.Name = Sheet102.Range("a38").Value
> > > > >     Sheet39.Name = Sheet102.Range("a39").Value
> > > > >     Sheet40.Name = Sheet102.Range("a40").Value
> > > > >     Sheet41.Name = Sheet102.Range("a41").Value
> > > > >     Sheet42.Name = Sheet102.Range("a42").Value
> > > > >     Sheet43.Name = Sheet102.Range("a43").Value
> > > > >     Sheet44.Name = Sheet102.Range("a44").Value
> > > > >     Sheet45.Name = Sheet102.Range("a45").Value
> > > > >     Sheet46.Name = Sheet102.Range("a46").Value
> > > > >     Sheet47.Name = Sheet102.Range("a47").Value
> > > > >     Sheet48.Name = Sheet102.Range("a48").Value
> > > > >     Sheet49.Name = Sheet102.Range("a49").Value
> > > > >     Sheet50.Name = Sheet102.Range("a50").Value
> > > > > End Sub

> > > > > --

> > > > > Thank you,

> > > > > James P. Schmidt



Thu, 08 Jul 2004 20:54:47 GMT  
Dave Peterso
#7 / 8
 changing 50 sheet names
How about something like this:

Sub runinvtabs()

Dim wks As Worksheet
Dim sheetnum As Long

    For Each wks In ActiveWorkbook.Worksheets
        If LCase(wks.CodeName) Like "sheet#" Then
            sheetnum = Right(wks.CodeName, 1)
        ElseIf LCase(wks.CodeName) Like "sheet##" Then
            sheetnum = Right(wks.CodeName, 2)
        Else
            sheetnum = 999
        End If
        If sheetnum <= 50 Then
            wks.Name = sheet102.Range("a" & sheetnum)
        End If
    Next wks

End Sub

Quote:

> Does anyone have an idea on shortening this code, it takes a while to rename
> all these sheets, even if only 1 name is changed.
> I wonder if it could change only the sheets that have the name changed?

> any help would be appreciated.

> Sub RunInvTabs()
>     Sheet1.Name = Sheet102.Range("a1").Value
>     Sheet2.Name = Sheet102.Range("a2").Value
>     Sheet3.Name = Sheet102.Range("a3").Value
>     Sheet4.Name = Sheet102.Range("a4").Value
>     Sheet5.Name = Sheet102.Range("a5").Value
>     Sheet6.Name = Sheet102.Range("a6").Value
>     Sheet7.Name = Sheet102.Range("a7").Value
>     Sheet8.Name = Sheet102.Range("a8").Value
>     Sheet9.Name = Sheet102.Range("a9").Value
>     Sheet10.Name = Sheet102.Range("a10").Value
>     Sheet11.Name = Sheet102.Range("a11").Value
>     Sheet12.Name = Sheet102.Range("a12").Value
>     Sheet13.Name = Sheet102.Range("a13").Value
>     Sheet14.Name = Sheet102.Range("a14").Value
>     Sheet15.Name = Sheet102.Range("a15").Value
>     Sheet16.Name = Sheet102.Range("a16").Value
>     Sheet17.Name = Sheet102.Range("a17").Value
>     Sheet18.Name = Sheet102.Range("a18").Value
>     Sheet19.Name = Sheet102.Range("a19").Value
>     Sheet20.Name = Sheet102.Range("a20").Value
>     Sheet21.Name = Sheet102.Range("a21").Value
>     Sheet22.Name = Sheet102.Range("a22").Value
>     Sheet23.Name = Sheet102.Range("a23").Value
>     Sheet24.Name = Sheet102.Range("a24").Value
>     Sheet25.Name = Sheet102.Range("a25").Value
>     Sheet26.Name = Sheet102.Range("a26").Value
>     Sheet27.Name = Sheet102.Range("a27").Value
>     Sheet28.Name = Sheet102.Range("a28").Value
>     Sheet29.Name = Sheet102.Range("a29").Value
>     Sheet30.Name = Sheet102.Range("a30").Value
>     Sheet31.Name = Sheet102.Range("a31").Value
>     Sheet32.Name = Sheet102.Range("a32").Value
>     Sheet33.Name = Sheet102.Range("a33").Value
>     Sheet34.Name = Sheet102.Range("a34").Value
>     Sheet35.Name = Sheet102.Range("a35").Value
>     Sheet36.Name = Sheet102.Range("a36").Value
>     Sheet37.Name = Sheet102.Range("a37").Value
>     Sheet38.Name = Sheet102.Range("a38").Value
>     Sheet39.Name = Sheet102.Range("a39").Value
>     Sheet40.Name = Sheet102.Range("a40").Value
>     Sheet41.Name = Sheet102.Range("a41").Value
>     Sheet42.Name = Sheet102.Range("a42").Value
>     Sheet43.Name = Sheet102.Range("a43").Value
>     Sheet44.Name = Sheet102.Range("a44").Value
>     Sheet45.Name = Sheet102.Range("a45").Value
>     Sheet46.Name = Sheet102.Range("a46").Value
>     Sheet47.Name = Sheet102.Range("a47").Value
>     Sheet48.Name = Sheet102.Range("a48").Value
>     Sheet49.Name = Sheet102.Range("a49").Value
>     Sheet50.Name = Sheet102.Range("a50").Value
> End Sub

> --

> Thank you,

> James P. Schmidt

--

Dave Peterson



Thu, 08 Jul 2004 22:45:54 GMT  
James Schmid
#8 / 8
 changing 50 sheet names
DSL was down,

I cant believe something that turning on and off automatic calculation
could speed things up so much.

It works fast now.

Thank you.


Quote:
> In a file with what appears to be at least 102 worksheets, renaming any
one
> of the worksheets is going to take a while, especially if a formula refers
> to it and the calculation mode is set to automatic.

> You might find that simply changing that at will help.  You can do that
> either manually through the options, or by changing your macro to look
like
> this:

> Sub RunInvTabs()
>     ' turn off automatic calculation
>     Application.Calculation = xlCalculationManual

>     ' rename the worksheets
>     Sheet1.Name = Sheet102.Range("a1").Value
>     :
>     :
>     :
>     Sheet50.Name = Sheet102.Range("a50").Value

>     ' turn automatic calculation back on
>     Application.Calculation = xlCalculationAutomatic
> End Sub

> HTH

> Alan P.



> > Sheets(i) returns error 9 , I tried changing sheets(i) to sheet(i) but
> still
> > fails.
> > I am trying to use vba   "Sheet1"   name instead of workbook "Sheet1"
> > so combining Sheet with (i) returns "Sheets1"  which I also do not have.

> > Interesting problem

> > If someone has a solution, Please post.



> > > Dear James,

> > > The error 9 message is being caused because there are no sheets named
> > > "Sheet1" etc in your workbook. I should have realised you were
referring
> > to
> > > sheet number 1 in the worksheets collection.

> > > Sub RunInvTabs()
> > > Application.ScreenUpdating = False
> > > For i = 1 To 50
> > >      Sheets(i).Name = Sheets(102).Range("A" & i).Value
> > > Next i
> > > Application.ScreenUpdating = True
> > > End Sub

> > > Regards,

> > > Robbie



> > > > It returned Subscript Out Of Range Error 9

> > > > What Could I Have Done Wrong?

> > > > Thanks

> > > > James



> > > > > Dear James,

> > > > > You could try the following:

> > > > > Sub RunInvTabs()
> > > > > Application.ScreenUpdating = False
> > > > > For i = 1 To 50
> > > > >     Sheets("Sheet" & i).Name = Sheets("Sheet102").Range("A" &
> i).Value
> > > > > Next i
> > > > > Application.ScreenUpdating = True
> > > > > End Sub

> > > > > Regards,

> > > > > Robbie



> > > > > > Does anyone have an idea on shortening this code, it takes a
while
> > to
> > > > > rename
> > > > > > all these sheets, even if only 1 name is changed.
> > > > > > I wonder if it could change only the sheets that have the name
> > > changed?

> > > > > > any help would be appreciated.

> > > > > > Sub RunInvTabs()
> > > > > >     Sheet1.Name = Sheet102.Range("a1").Value
> > > > > >     Sheet2.Name = Sheet102.Range("a2").Value
> > > > > >     Sheet3.Name = Sheet102.Range("a3").Value
> > > > > >     Sheet4.Name = Sheet102.Range("a4").Value
> > > > > >     Sheet5.Name = Sheet102.Range("a5").Value
> > > > > >     Sheet6.Name = Sheet102.Range("a6").Value
> > > > > >     Sheet7.Name = Sheet102.Range("a7").Value
> > > > > >     Sheet8.Name = Sheet102.Range("a8").Value
> > > > > >     Sheet9.Name = Sheet102.Range("a9").Value
> > > > > >     Sheet10.Name = Sheet102.Range("a10").Value
> > > > > >     Sheet11.Name = Sheet102.Range("a11").Value
> > > > > >     Sheet12.Name = Sheet102.Range("a12").Value
> > > > > >     Sheet13.Name = Sheet102.Range("a13").Value
> > > > > >     Sheet14.Name = Sheet102.Range("a14").Value
> > > > > >     Sheet15.Name = Sheet102.Range("a15").Value
> > > > > >     Sheet16.Name = Sheet102.Range("a16").Value
> > > > > >     Sheet17.Name = Sheet102.Range("a17").Value
> > > > > >     Sheet18.Name = Sheet102.Range("a18").Value
> > > > > >     Sheet19.Name = Sheet102.Range("a19").Value
> > > > > >     Sheet20.Name = Sheet102.Range("a20").Value
> > > > > >     Sheet21.Name = Sheet102.Range("a21").Value
> > > > > >     Sheet22.Name = Sheet102.Range("a22").Value
> > > > > >     Sheet23.Name = Sheet102.Range("a23").Value
> > > > > >     Sheet24.Name = Sheet102.Range("a24").Value
> > > > > >     Sheet25.Name = Sheet102.Range("a25").Value
> > > > > >     Sheet26.Name = Sheet102.Range("a26").Value
> > > > > >     Sheet27.Name = Sheet102.Range("a27").Value
> > > > > >     Sheet28.Name = Sheet102.Range("a28").Value
> > > > > >     Sheet29.Name = Sheet102.Range("a29").Value
> > > > > >     Sheet30.Name = Sheet102.Range("a30").Value
> > > > > >     Sheet31.Name = Sheet102.Range("a31").Value
> > > > > >     Sheet32.Name = Sheet102.Range("a32").Value
> > > > > >     Sheet33.Name = Sheet102.Range("a33").Value
> > > > > >     Sheet34.Name = Sheet102.Range("a34").Value
> > > > > >     Sheet35.Name = Sheet102.Range("a35").Value
> > > > > >     Sheet36.Name = Sheet102.Range("a36").Value
> > > > > >     Sheet37.Name = Sheet102.Range("a37").Value
> > > > > >     Sheet38.Name = Sheet102.Range("a38").Value
> > > > > >     Sheet39.Name = Sheet102.Range("a39").Value
> > > > > >     Sheet40.Name = Sheet102.Range("a40").Value
> > > > > >     Sheet41.Name = Sheet102.Range("a41").Value
> > > > > >     Sheet42.Name = Sheet102.Range("a42").Value
> > > > > >     Sheet43.Name = Sheet102.Range("a43").Value
> > > > > >     Sheet44.Name = Sheet102.Range("a44").Value
> > > > > >     Sheet45.Name = Sheet102.Range("a45").Value
> > > > > >     Sheet46.Name = Sheet102.Range("a46").Value
> > > > > >     Sheet47.Name = Sheet102.Range("a47").Value
> > > > > >     Sheet48.Name = Sheet102.Range("a48").Value
> > > > > >     Sheet49.Name = Sheet102.Range("a49").Value
> > > > > >     Sheet50.Name = Sheet102.Range("a50").Value
> > > > > > End Sub

> > > > > > --

> > > > > > Thank you,

> > > > > > James P. Schmidt



Sat, 10 Jul 2004 08:55:26 GMT  
 
 Page 1 of 1
  [ 8 post ] 

 Relevant Pages 

1. change 50 worksheet change events

2. Quick way to load 50 comboboxes with 50 of the same items

3. convert 123,50 to honderd en drientwintig en 50/100

4. Convert 123,50 to honderd en drientwintig en 50/100

5. Transferring Table with 50 records to (another) Table with 1 record with 50 fields

6. convert 50 page doc to 50 seperate docs

7. How can I turn a 50 page doc into 50 seperate docs

8. Help with Ent X Error Msg -50 - EntX-Error-50.jpg (1/1)

9. Editing 50 indentical sheets across several work nbooks at once

10. Summing a single cell from 50 sheets

 
Powered by phpBB® Forum Software © phpBB Group