?range("BreakdownList").Address
..and press Enter to see "$C$9:$C$12", which is the area where the 4
entries under BREAKDOWN reside.
Funny though, I can read it and understand what its doing. That will be a good one to keep around and refer to.
at the moment you have 4 rows in Summary with values. When will you run
the macro? If all 24 rows are filled?
If you run the macro now and later you make new entries and you will run
the macro again, you must test which sheets already exists.
Then you better try:
Sub CopyMainSwb()
Dim arrNames, n As Integer
Dim SheetExists As Boolean
Application.ScreenUpdating = False
With Sheets("Summary")
arrNames = .Range("C9:C32").SpecialCells(xlCellTypeConstants)
For n = LBound(arrNames) To UBound(arrNames)
On Error Resume Next
SheetExists = Not Sheets(arrNames(n, 1)) Is Nothing
If SheetExists = False Then
Sheets("Main Swb").Copy before:=Sheets("NOTES")
ActiveSheet.Name = arrNames(n, 1)
.Range("C9:C32").SpecialCells(xlCellTypeConstants) _
.Cells(n).Offset(, 1).Resize(1, 4) = _
"='" & arrNames(n, 1) & "'!G7"
End If
Next
End With
Application.ScreenUpdating = True
End Sub
Regards
Claus Busch
Thanks, Claus! You're absolutely correct! (I got lazy<g>, my bad)...
Sub CopySheetAndNameCopies_v2()
Dim vNames, sFormulas, n&, k&
vNames = Sheets("Summary").Range("BreakdownList")
Application.ScreenUpdating = False
Sheets("Main Swb").Visible = True
For n = LBound(vNames) To UBound(vNames)
If not bSheetExists(vNames(n, 1)) Then
Sheets("Main Swb").Copy after:=Sheets("Summary")
ActiveSheet.Name = vNames(n, 1)
sFormulas = "='" & vNames(n, 1) & "'!G7"
Sheets("Summary").Range("BreakdownList").Cells(n).Offset(,
1).Resize(1, 4) = sFormulas
End If 'Not bSheetExists
Next 'n
Sheets("Main Swb").Visible = False
Application.ScreenUpdating = True
End Sub
Sub CopySheetAndNameCopies_v3()
Dim vNames, vFormulaRefs, vaFormulas(1, 4)
Dim n&, k&
vNames = Sheets("Summary").Range("BreakdownList")
vFormulaRefs = Array("G7", "H7", "I7", "J7")
Application.ScreenUpdating = False
Sheets("Main Swb").Visible = True
For n = LBound(vNames) To UBound(vNames)
If not bSheetExists(vNames(n, 1)) Then
Sheets("Main Swb").Copy after:=Sheets("Summary")
ActiveSheet.Name = vNames(n, 1)
For k = 1 To 4
vaFormulas(1, k) = "='" & vNames(n, 1) & "'!" & vFormulaRefs(k)
Next 'k
Sheets("Summary").Range("BreakdownList").Cells(n).Offset(,
1).Resize(1, UBound(vaFormulas, 2)) = vaFormulas
End If 'Not bSheetExists
Next 'n
Sheets("Main Swb").Visible = False
Application.ScreenUpdating = True
End Sub
Function bSheetExists(WksName) As Boolean
On Error Resume Next
bSheetExists = CBool(Len(ActiveWorkbook.Sheets(WksName).Name))
End Function
You're very welcome! Thanks for the feedback!
I have a few different ideas for how to manage deleting sheets, but
I'll wait while you brainstorm awhile.<g>
Well, I came up with this pedestrain bit of code, but it only deletes the first sheet of the selected names and all of the names selected then errors out with sub script out of range. Booger!
same as add sheets:
Sub DeleteSheets()
Dim arrNames
Dim n As Integer
Application.DisplayAlerts = False
arrNames = Selection
For n = LBound(arrNames) To UBound(arrNames)
Sheets(arrNames(n, 1)).Delete
Selection.Resize(, 5).ClearContents
Next
Application.DisplayAlerts = True
End Sub
Regards
Claus Busch
Code is in a module and I get an error on this line.
Tried it in the Summary sheet vb editor first and same error.
For n = LBound(arrNames) To UBound(arrNames)
Howard
For Each c In Selection
Application.DisplayAlerts = False
Sheets(c.Value).Delete
Selection.ClearContents
Application.DisplayAlerts = True
Next
Hi Howard,
Am Mon, 8 Apr 2013 23:54:20 -0700 (PDT) schrieb Howard:
For Each c In SelectionApplication.DisplayAlerts = False
Application.DisplayAlerts = TrueNext
because you ClearContents into the For-Next-Statement.
So there is no other name left.
In Code below the last line will sort that you don't have empty rows.
Try:
Sub DeleteSheets()
Dim rngC As Range
Application.DisplayAlerts = False
For Each rngC In Selection
Sheets(rngC.Text).Delete
Next
Selection.Resize(, 5).ClearContents
Range("C8:G32").Sort key1:=[C9], order1:=xlAscending, Header:=xlYes
Application.DisplayAlerts = True
End Sub
Regards
Claus Busch
I tried the code in the sheet vb editor and it deleted the sheets but the names and formulas to the right remain intact.
Hi Howard,
Am Tue, 9 Apr 2013 02:47:53 -0700 (PDT) schrieb Howard:
I tried the code in the sheet vb editor and it deleted the sheets but the names and formulas to the right remain intact.
that is when the selected cells are not connected.
Place the code in a standard module.
Try:
Sub DeleteSheets()
Dim rngC As Range
Application.DisplayAlerts = False
For Each rngC In Selection
Sheets(rngC.Text).Delete
rngC.Resize(, 5).ClearContents
Next
Range("C8:G32").Sort key1:=[C9], order1:=xlAscending, Header:=xlYes
Application.DisplayAlerts = True
End Sub
Regards
Claus Busch