J
Jason.Alden.Benoit
Hi,
I have a macro that will generate sheets from a template and name
them from selected names in cells. I then would like from the new
sheets to be able to automatically have the data systematically pulled
over to a summary sheet. Basically I have a "tally" template that will
generate with a person's name.(I also have the sheet's/person's name
generate in a cell on the form.) I then have the data tally up in a
summary on that sheet. On a single summary sheet I want to be able to
be able to look at all summarized data from all other sheets, with a
heading of the sheet and therefore person's name above the data.
I haven't come up with a way to edit the heading's on the summary
sheet with the sheets in the work books or the person's name. If I
could do that I can handle the rest. There may be a few ways to do
this, I just need one. : ) I had thought about when generating the
template sheets "grabbing" the name of the sheet as it is generated
and sequentially editing the cells for the headers on the summary
sheet, but not sure if this is possible. It would be going horizontal
and skipping three cells. The template generation code is below,
modified slightly by me:
////
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
///
.. I am not very experienced with macros but I can hack them VERY
little; mainly change values, not sure what I am doing as far as
writing them. Thanks for any ideas, and if you have anything I could
help clarify please let me know! Thanks!
- Alden
I have a macro that will generate sheets from a template and name
them from selected names in cells. I then would like from the new
sheets to be able to automatically have the data systematically pulled
over to a summary sheet. Basically I have a "tally" template that will
generate with a person's name.(I also have the sheet's/person's name
generate in a cell on the form.) I then have the data tally up in a
summary on that sheet. On a single summary sheet I want to be able to
be able to look at all summarized data from all other sheets, with a
heading of the sheet and therefore person's name above the data.
I haven't come up with a way to edit the heading's on the summary
sheet with the sheets in the work books or the person's name. If I
could do that I can handle the rest. There may be a few ways to do
this, I just need one. : ) I had thought about when generating the
template sheets "grabbing" the name of the sheet as it is generated
and sequentially editing the cells for the headers on the summary
sheet, but not sure if this is possible. It would be going horizontal
and skipping three cells. The template generation code is below,
modified slightly by me:
////
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
///
.. I am not very experienced with macros but I can hack them VERY
little; mainly change values, not sure what I am doing as far as
writing them. Thanks for any ideas, and if you have anything I could
help clarify please let me know! Thanks!
- Alden