Re: Auto Rename Excel Sheets in Workbook

[Otto Moehrbach]

You can create as many sheets in an existing workbook as your computer
memory can handle.
What do you mean by "Auto name the sheet name? Describe what you want to
do. HTH Otto
"Kulin Shah" <Kuli...@discussions.microsoft.com> wrote in message
news:A2126C8F-75B1-4589...@microsoft.com...
> --------------------------------------------------------------------------------
>
> HI,
>
> 1. I would like to know if i can Auto name the sheet name.??
>
> 2. Also more if i would want to make changes to a workbook, can a seperate
> sheet be created in the same existing workbook?
>
> Plz Help.
>
>
> Kulin.
>

[Kulin Shah]

Hi,

I wanted excel to create an auto sheet when changes made to the Master Sheet.

For eg if i have made a sheet with all my formulas etc (as default) and if
another user makes a change to the same then excel should automatically copy
the same data to another sheet with the new entered data. The Default one
will still remain in its place.

If yes then, can the new sheet prepared be renamed automatically for eg. if
Cell a1 = "abcd" then sheet will automatically be renamed as "abcd"

Is this possible.

Awaiting a reply.

Thanks,

Kulin

[Otto Moehrbach]

Yes, Excel can do that, but if you want this to happen automatically, Excel
needs to know WHEN to do that. You say whenever the user makes a change to
the sheet. To me that means when the user makes any change to any ONE cell
in the entire sheet. If you want this to happen after the user makes a
change to a specific cell, then you have to tell me what cell. Tell me
exactly when you want this to happen and I'll write some VBA code for you.

HTH Otto
"Kulin Shah" <Kuli...@discussions.microsoft.com> wrote in message

news:BD26EF7D-0E35-4755...@microsoft.com...

[Ed Thomas]

Pardon the intrusion off subject. Otto have been trying to contact you
regarding an update to the invoice template you did for us a few years
ago. All e-mails bounce back. Please contact me at e...@triad.rr.com.
Thanks,

Ed

[Kulin Shah]

HI Otto,

Many thanks for understanding.

I would like Excel to create a new Sheet when Cell b3 is changed.

Also more if the user makes a further change in the new Sheet (in Cell b3)
will it again create a new Sheet? No i do not prefer this if possible.

Also auto rename of the new Sheet with Cell value in cell b1

Awaiting for your response.

Many Thanks,

Kulin.

[Kulin Shah]

Many thanks for understanding.

I would like Excel to create a new Sheet when Cell b3 is changed.

Also more if the user makes a further change in the new Sheet (in Cell b3)
will it again create a new Sheet? No i do not prefer this if possible.

Also auto rename of the new Sheet with Cell value in cell b1

Awaiting for your response.

[Otto Moehrbach]

Kulin
The following macro will do what you want.
Because you do not want a new sheet added if the user makes an entry change
in B3 of the new sheet, I needed to write the name of the original sheet
into the code in the macro. I assumed that name to be "First". Change that
name in the macro as needed. Therefore, as written, the macro will do
nothing if the name of the host sheet is not "First".
Note that if B1 is blank, the new sheet will have the default name of
"Sheetx" where "x" is the number of the next sheet to be created.
Note that this macro is a sheet macro and must be placed in the sheet module
of the "First" sheet. To access that module, right-click on the sheet tab,
select View Code, and paste this macro into that module. "X" out of the
module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestWs As Worksheet
If Me.Name <> "First" Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
Set DestWs = Sheets.Add(after:=Sheets(Sheets.Count))
On Error Resume Next
DestWs.Name = Range("B1").Value
On Error GoTo 0
End Sub

"Kulin Shah" <Kuli...@discussions.microsoft.com> wrote in message

news:28219B10-5FFB-4B3F...@microsoft.com...

[Kulin Shah]

HI Thanks fo the wonderful solution.

The formula actually helped.

But i wanted to copy the same Sheet (Template) to the new sheet if a cell is
changed. The orinal Template will remain blank as per originally made one.

I hope you can solve this as well.

Brgds. Kulin

[Otto Moehrbach]

Kulin
I don't understand what you are saying. Let's stick with the name of
the original sheet as I have it in the macro, "First".
As written, that macro will act if B3 is changed. The macro will create a
new blank sheet and name it the entry in B1 of the "First" sheet. Say that
name is Bell. You now have 2 sheets, the "First" sheet and the "Bell"
sheet. If you enter something in B3 of the Bell sheet, the macro will do
nothing.
Now refer to the "First" and "Bell" sheets and tell me what you want.

Otto
"Kulin Shah" <Kuli...@discussions.microsoft.com> wrote in message

news:115DCA3C-8D39-4E40...@microsoft.com...

[Braca]

Hello Otto.
I found this coding of yours very helpfull and I have one question for you:
Is it possible to copy that first sheet instead of just creating a new one?
Regards,
Braca

[Don Guillett]

Have you tried copy after instead of add after.

--
Don Guillett
SalesAid Software
dguil...@austin.rr.com
"Braca" <Br...@discussions.microsoft.com> wrote in message
news:1877A80E-2F11-4553...@microsoft.com...

[Don Guillett]

I should have been more clear
Sheets.Add(after:=Sheets(Sheets.Count))
Sheets("template").copy(after:=Sheets(Sheets.Count))
Merry xmas

--
Don Guillett
SalesAid Software
dguil...@austin.rr.com

"Don Guillett" <dguil...@austin.rr.com> wrote in message
news:Ot75GaDK...@TK2MSFTNGP03.phx.gbl...