Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong with macro naming sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong with macro naming sheets
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. "Pete" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong with macro naming sheets
The following characters are not valid characters to use in worksheet names: / ? * [ ] http://support.microsoft.com/kb/q213900/ Pete wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong with macro naming sheets
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) "Bob Umlas" wrote in message ... 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. "Pete" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming sheets from a cell value | Excel Discussion (Misc queries) | |||
VBA Help naming sheets | Excel Discussion (Misc queries) | |||
Macro for naming sheets? | Excel Discussion (Misc queries) | |||
What is wrong with naming a cell as c? | Excel Discussion (Misc queries) | |||
naming sheets | Excel Programming |