saving a sheet named as a cell vaule

  • Thread starter Thread starter pbs
  • Start date Start date
P

pbs

I am trying to set up a macro that will save a sheet based on the vaule in a
cell of the same workbook.

Anyone got any ideas

Regards

Peter
 
Not sure what you mean by "Save a Sheet". You save workbooks, and the sheets
get saved in the process ...
This doesn't check for errors (such as invalid Sheet Name character in the
cell), but it will name the ActiveSheet based on the value in A1:

Sub NameSheet()

ActiveWorkbook.ActiveSheet.Name = Range("A1").Value

End Sub
 
The following VB code will rename the active sheet in the workbook t
the contents of the selected cell in the active sheet:


Sub a_Name_Sheet()
'
' Names the active sheet in the workbook with the contents of th
active cell

'Declare variables
Dim check1, check2, check3, check4, check5, textLength, myTest A
Integer
Dim cellContents, Msg, Style, Title

' Get the contents of the active cell...
cellContents = ActiveCell.Value

' Check for certain characters that can't be used in sheet name
' (if it finds the character, it returns the position of where it is
' starting from the first character moving left to right; if i
doesn't
' appear in the cell, it returns 0)
check1 = InStr(1, cellContents, ":", vbTextCompare)
check2 = InStr(1, cellContents, "\", vbTextCompare)
check3 = InStr(1, cellContents, "/", vbTextCompare)
check4 = InStr(1, cellContents, "?", vbTextCompare)
check5 = InStr(1, cellContents, "*", vbTextCompare)

' Check for length of data (sheet names can't exceed 31 characters)
textLength = Len(cellContents)

' Determine if cell data can be used as sheet name
myTest = check1 + check2 + check3 + check4 + check5
If myTest > 0 Or textLength > 31 Or IsEmpty(cellContents) Then
' Alert user there's a problem
' Define user dialog parameters
Msg = "The data in the selected cell can't be used because of
" & Chr(13) & _
"one of the following issues:" & Chr(13) & Chr(13) & _
" - It is longer than 31 characters." & Chr(13) & _
" - It contains one or more of these characters: : \
? *" & Chr(13) & _
" - It contains no data."
Style = vbOKOnly + vbExclamation + vbDefaultButton1
Title = "Data Error"
' Display user dialog
Response = MsgBox(Msg, Style, Title)
Else
ActiveSheet.Name = cellContents
End If
End Su
 
Back
Top