Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell value consistency/macro problem.
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a new line, without destroying the consistency of the formu | Excel Worksheet Functions | |||
Cell value consistency for all sheets/Macro problem. | Excel Programming | |||
Problem with cell references in my macro | Excel Programming | |||
Improve consistency in how Excel and Word 2003 close. | Excel Discussion (Misc queries) | |||
how can I change the size of each cell consistency? | Excel Programming |