Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvys Code
Hey Tom,
Its supposed to be set to A5:A53. Below is the code I am currently using: Private Sub CommandButton2_Click() Dim rng As Range, rng1 As Range Dim cell As Range, cell1 As Range Dim rngStat As Range Dim res As Variant With Worksheets("Team Data") Set rng = .Range("A5:A53") Set rngStat = .Range("B4:U4") End With With Worksheets("Template") Set rng1 = .Range("A5:A16") End With For Each cell In rng For Each cell1 In rng1 res = Application.Match(cell1, rngStat, 0) If Not IsError(res) Then cell1.Offset(0, 1).Value = rngStat(cell.Row - 3, res).Value End If Next On Error Resume Next Application.DisplayAlerts = False Worksheets(cell.Value).Delete Application.DisplayAlerts = True On Error GoTo 0 rng1.Parent.Copy after:=Worksheets(Worksheets.Count) ActiveSheet.name = cell.Value rng1.Offset(0, 1).ClearContents Next End Sub heres the problem, It works like I need. The only thing it does wrong is after it creates the last sheet based on the last persons name in range A5:A53, it creates another sheet called Template(2) and then I get a runtime error. I click Debug and then it brings up the screen with the 2nd to the last line of the code highlighted(that is ActiveSheet.name = cell.Value. This line is in yellow. What I would like is that when the code sees there are no more names in Range A5:A53, the code quits without error and does not create a page at the end called Template(2). Thank you Todd Huttenstine |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |