Naming a sheet w/ data from worksheet

[Guest]

I have an 88 page workbook that I want to name each sheet with the contents that are in cell A1 on each sheet. Does anyone know an automated way to do this?

 

[Ron de Bruin]

Try this

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Range("A!").Value & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

I have an 88 page workbook that I want to name each sheet with the contents that are in cell A1 on each sheet. Does anyone know

an automated way to do this?

 

[Ron de Bruin]

Typo

Range("A!")

must be

Range("A1")

--
Regards Ron de Bruin
http://www.rondebruin.nl

I have an 88 page workbook that I want to name each sheet with the contents that are in cell A1 on each sheet. Does anyone know

an automated way to do this?

 

[Guest]

Thank you for your response. This macro appears to work, but is getting hung up on page 59 returning this error message

Run-Time Error '1004'
Application-Defined or Object-Defined Erro

Any ideas

----- Ron de Bruin wrote: ----

Try thi

Sub test(
Dim a As Intege
Dim wb As Workboo
Application.ScreenUpdating = Fals
For a = 1 To ThisWorkbook.Worksheets.Coun
ThisWorkbook.Sheets(a).Cop
Set wb = ActiveWorkboo
wb.SaveAs "C:\" & wb.Sheets(1).Range("A!").Value & ".xls
wb.Close Fals
Set wb = Nothin
Next
Application.ScreenUpdating = Tru
End Su


--
Regards Ron de Brui
http://www.rondebruin.n

I have an 88 page workbook that I want to name each sheet with the contents that are in cell A1 on each sheet. Does anyone kno

an automated way to do this

 

[Ron de Bruin]

Do you have hidden or chart sheets in the workbook?

 

[Gord Dibben]

Ron

I'm curious about this saving each sheet as a separate file.

Is this what OP asked for?

I thought he just wanted to name the sheets.

Gord

 

[Ron de Bruin]

Hi Gord

After reading again, Yes you are right.

But the OP posted this

Thank you for your response. This macro appears to work

????

For naming the OP can use this

Sub test2()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("A1").Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

 

[Gord Dibben]

I saw OP's response Ron and just wondered if he knew what was happening with
your original code.

Gord

 

[Guest]

I figured out why the code was getting hung up (there was a "/" in cell A1 on some of the sheets. I corrected that and ran the original code again and it didn't work.

I have now tried the new info and am getting "change the name manually" on about half the sheets. However, it is working on some of them.

Any thoughts?

 

[Ron de Bruin]

Hi

If a Sheet name already exist then you get the msgbox "change the name manually"
You can't have sheets with the same name

--
Regards Ron de Bruin
http://www.rondebruin.nl

I figured out why the code was getting hung up (there was a "/" in cell A1 on some of the sheets. I corrected that and ran the

original code again and it didn't work.

 

[Guest]

FYI, turns out the problem was too many characters on some of the sheets.

Thanks for your help with my problem.

 

[Guest]

Ron,
This is a nifty script, but I am having a problem with it for some reason. Hopefully you can point me in the right direction. I changed the name to make more sense for what I am doing.
Anyway, Cell A2 contains the current date using the =Today() formula that is populated in that cell by yet another macro. When I try to run "save_worksheet_by_cell" I continually receive the message to rename the sheet manually. The format of cell A2 is date in the form *06-22-04. I have tried with the cell formatted as text, and that changes the cell contents to the serial number which will not work for me. I tried General format as well, and the "rename sheet 1 manually" dialog still pops up.
Any suggestions as to what might be causing that?
Thanks a ton.


Sub save_worksheet_by_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.name = sh.Range("A2").Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & sh.name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Fleone

Are you saying that every sheet have a cell(A2) with the Today function?
You can't give every sheet the same name?


--
Regards Ron de Bruin
http://www.rondebruin.nl

Ron,
This is a nifty script, but I am having a problem with it for some reason. Hopefully you can point me in the right direction. I

changed the name to make more sense for what I am doing.

Anyway, Cell A2 contains the current date using the =Today() formula that is populated in that cell by yet another macro. When I

try to run "save_worksheet_by_cell" I continually receive the message to rename the sheet manually. The format of cell A2 is date in
the form *06-22-04. I have tried with the cell formatted as text, and that changes the cell contents to the serial number which will
not work for me. I tried General format as well, and the "rename sheet 1 manually" dialog still pops up.

 

[Ron de Bruin]

If your date seperator is a / then this is also a problem.
You can't use a / in a sheet name

Use the format funtion
sh.Name = Format(sh.Range("A2"), "mm-dd-yy")

I go to bed now (12:11 for me)

 

[Guest]

I did change the regional settings in Windows to list dates in the format mm-dd-yyyy, removing the / seperator. I am just naming one sheet in this particular instance, so having duplicate names will not be a problem at all.
I will try the format suggestion, I think I read something similar to that, but wasn't sure how to implement.
Thanks again!

 

[Ron de Bruin]

am just naming one sheet in this particular instance

Why do you loop through all you work sheets then

For Each sh In ThisWorkbook.Worksheets

--
Regards Ron de Bruin
http://www.rondebruin.nl

I did change the regional settings in Windows to list dates in the format mm-dd-yyyy, removing the / seperator. I am just naming

one sheet in this particular instance, so having duplicate names will not be a problem at all.

 

[Dave Peterson]

Ron's original code pointed at Thisworkbook (the workbook containing the code).

Sub save_worksheet_by_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.name = sh.Range("A2").Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & sh.name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

You can change this line:
For Each sh In ThisWorkbook.Worksheets
to
For Each sh In Activeworkbook.Worksheets

and you won't have to select/activate anything in your code. It'll work against
whatever workbook is active.