Wrong with macro naming sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top