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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell value consistency/macro problem.
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 -- Regards, OssieMac "Alden" wrote: 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 |
Reply |
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 |