Just wanted to say Thanks! and than hit the Yes button that this answered my
question

"Gary''s Student" wrote:

There is no Sheets(0). This will insert a new sheet in the "1" position and
name it "bud"

Sub NewSheet1()
Sheets.Add Befo=Sheets(1)
ActiveSheet.Name = "bud"
End Sub


--
Gary''s Student - gsnu200835


"Bud" wrote:

Hello

I am using some code someone gave me to try when I was having run-time error
'9' subscript out of range.

I changed some of the code to position the sheets exactly where I wanted them

So instead of using
Sheets.Add after:=Sheets(Sheets.Count)
Set NewSht1 = ActiveSheet
NewSht1.Name = "SWIM Time Data"

I am using this to position the sheet right after the 3rd sheet
Sheets.Add after:=Sheets(3)
Set NewSht1 = ActiveSheet
NewSht1.Name = "SWIM Time Data"

What I am having a problem with is that I delete the 1st sheet but when I
try and use Sheets(0) I get an abend

This is the code...I placed a 1 in and it adds it after what is now the 1st
sheet after I first delete SWIMInput. However, I want to add back the
SWIMINPUT right back where I deleted it from which is the first sheet in the
workbook.

How can I do this? ...I am getting a subscript out of range

'Start of selecting SWIMInput deleting and than re-creating
Sheets("SWIMInput").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False

Sheets.Add after:=Sheets(1)
Set NewSht3 = ActiveSheet
NewSht3.Name = "SWIMInput"







Sub SWIM()
'
' SWIM Macro
' Macro recorded 1/10/2009 by czj63c
' Bud Zeiger
'
' Keyboard Shortcut: Ctrl+Shift+U
'
'Let's check to see if we have any data or the right data
Sheets("SWIMInput").Select
Range("a1").Select
If ActiveCell.FormulaR1C1 < "EDSNETID" Then
MsgBox "Please close workbook, re-open, and paste SWIM_Master_Input
MSPS info into SWIMInput worksheet"
Exit Sub
End If

'Start of selecting SWIM Time Data deleting and than re-creating


Sheets("SWIM Time Data").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False

Sheets.Add after:=Sheets(3)
Set NewSht1 = ActiveSheet
NewSht1.Name = "SWIM Time Data"

'Sheets.Add
'If Err = 9 Then
' Err.Clear
' MsgBox "Please close workbook, re-open, and paste SWIM_Master_Input
MSPS info into SWIMInput worksheet"
'End If
'Sheets("Sheet1").Select
'Sheets("Sheet1").Name = "SWIM Time Data"
'End of selecting SWIM Time Data deleting and than re-creating

'Start of adding SWIMTimeDataSav so it can be used a temp area
Sheets.Add after:=Sheets(Sheets.Count)
Set NewSht2 = ActiveSheet
NewSht2.Name = "SWIMTimeDataSav"

'Sheets.Add
'Sheets("Sheet2").Select
'Sheets("Sheet2").Name = "SWIMTimeDataSav"
'End of adding temporary SWIMTimeDataSav

'This next statement turns off the screen updating while the macro is
running
Application.ScreenUpdating = False

Dim br As Long
Dim strDate As String 'Current system date

'Select SAPTasks and count the number of active rows
Sheets("SWIMInput").Select
Cells.Select
br = Cells(Rows.Count, "b").End(xlUp).Row
strDate = Format(Now, "ddmmmyyyy")

'Sort the data
Range("A2").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers



'MsgBox br

'Select SWIMTimeDataSav as we are going to build the UPLOAD file
Sheets("SWIMTimeDataSav").Select
Cells.Select
' First Clear all contents of worksheet
ActiveSheet.Cells.ClearContents
' Second Add a line that describes the data fields
' Also add the formulas for obtaining the data

Cells(1, "a") = "Employee"
Cells(1, "b") = "Date (dd-mmm-yyyy)"
Cells(1, "c") = "Start Time (hh:mm)"
Cells(1, "d") = "End Time (hh:mm)"
Cells(1, "e") = "Duration (Hrs)"
Cells(1, "f") = "Work Breakdown Structure Element(WBSE)"
Cells(1, "g") = "Line Item Text"
Cells(1, "h") = "Employee Name"
Cells(1, "i") = "Project Name"

Cells(2, "a") = "=SWIMInput!A2" 'Employee
Cells(2, "b") = strDate 'Todays date
Cells(2, "g") = "=SWIMInput!B2" 'Task Name
Cells(2, "f") = "=SWIMInput!C2" 'WBSE
Cells(2, "i") = "=SWIMInput!D2" 'Project Name
' The next matching formula concatenates the number of records in
SWIMInput for knowing when to stop on the fill down
Cells(2, "h") = "=IF($a$2:$a$" & br & "="""","""",(INDEX('SWIM Employee
Details'!$c$1:$c$1000,MATCH($a$2:$a" & br & ",'SWIM Employee
Details'!$A$1:$A$1000,0))))"
' Thirdly Auto fill down for the number rows we obtained from the
SAP-Simulation


If br 2 Then
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br,
"a"))
'Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br,
"b"))
' Changed B to copy instead so the value does not increase by one
each row filling down
Cells(2, "b").Copy Destination:=Range(Cells(2, "b"), Cells(br, "b"))
Cells(2, "f").AutoFill Destination:=Range(Cells(2, "f"), Cells(br,
"f"))
Cells(2, "g").AutoFill Destination:=Range(Cells(2, "g"), Cells(br,
"g"))
Cells(2, "i").AutoFill Destination:=Range(Cells(2, "i"), Cells(br,
"i"))
Cells(2, "h").AutoFill Destination:=Range(Cells(2, "h"), Cells(br,
"h"))
End If


'Wrapping text Begin
Range("B1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("f1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("i1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


Sheets("SWIMTimeDataSav").Select
Cells.Select
Selection.Copy
Sheets("SWIM Time Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Wrapping text Begin
Range("B1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("c1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True