Cell value consistency/macro problem.

[Alden]

I am using the macro below to make a new sheet from a template, and then
rename said sheet(s) from a "selected" list (read highlighted). This worked
great, until I decided that I wanted the selected cells to have data from a
reference. Now the macro only makes a duplicate of my template and renames it
with a (2) after it.

Those who use the workbook tend to change the order of the selected cells,
which breaks a summary sheet. To avoid this, the selectable cell's data is
pulled from the summary sheet's same cells in order to try to keep the needed
consistency.

Are there any solutions to my problem? (Also while I am thinking about it,
is there any way to use this macro with numbers and dates?)

Thanks,

-- Alden

Sub TabsFromList()
'David McRitchie based on previous code in sheets.htm
Application.ScreenUpdating = False
Dim cell As Range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
'Sheets.Add after:=Sheets(Sheets.Count)

Worksheets("Template").Copy after:=Worksheets(Worksheets.Count)

If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
Application.ScreenUpdating = True
End Sub

 

[OssieMac]

Hi Alden,

I tested your code and basically it works OK. However, could you post the
formula that you are using for the cell together with the returned result
because I only used a simple formula of =B1+C1 which both contained text.
Also not sure this will help but you could try using cell.value in lieu of
cell.text.

Next question on using numvbers and dates. The following sample code might
help.

Sub Format_Date()

Dim newname As String
Dim myDate As Date

myDate = Date 'Assign Current date to myDate

'Create string from date and concatenate with text
'Use any valid number/date format between double quotes
newname = "MySheet " & Format(myDate, "dd mmm yy")

MsgBox newname


End Sub