Wrong with macro naming sheets

[Guest]

Hi!

I get an error when using this macro! What is the problem? Ive marked it
below where it gives me the error!

Thanks for all the help!

Sub MonthlySentOut()

Cells.Select
Selection.Interior.ColorIndex = xlNone
Range("B1").Select
Selection.ClearContents
Range("B11").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=MID(R[6]C[1],28,7)"
Range("A2").Select
For Each Sh In ActiveWorkbook.Sheets
Sh.Activate
Sh.Name = Cells(1, 1).Value <---- where error occurs!!!!
Next Sh
sPath = "C:\Temp\"
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("a1").Value
End Sub

 

[Bob Umlas]

One guess is that =MID(B7,28,7) gets its value from B7 on one of the sheets
which results in either empty or invalid characters for a sheet name.

 

[acampbell012]

The following characters are not valid characters to use in worksheet
names:
/ ? * [ ]

http://support.microsoft.com/kb/q213900/

 

[Bob Phillips]

You can also simplify the code

Sub MonthlySentOut()

Cells.Interior.ColorIndex = xlNone
Range("B1,B11").ClearContents
Range("A1").FormulaR1C1 = "=MID(R[6]C[1],28,7)"
For Each sh In ActiveWorkbook.Sheets
sh.Name = sh.Cells(1, 1).Value
Next sh
sPath = "C:\Temp\"
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("a1").Value
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

One guess is that =MID(B7,28,7) gets its value from B7 on one of the sheets
which results in either empty or invalid characters for a sheet name.

Hi!

I get an error when using this macro! What is the problem? Ive marked it
below where it gives me the error!

Thanks for all the help!

Sub MonthlySentOut()

Cells.Select
Selection.Interior.ColorIndex = xlNone
Range("B1").Select
Selection.ClearContents
Range("B11").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=MID(R[6]C[1],28,7)"
Range("A2").Select
For Each Sh In ActiveWorkbook.Sheets
Sh.Activate
Sh.Name = Cells(1, 1).Value <---- where error occurs!!!!
Next Sh
sPath = "C:\Temp\"
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("a1").Value
End Sub