I don't really care about the rating system. I've been a Two for about 2
years and should be a 1. Microsoft software never works very well. thats
why there are so many questions at this site. Microsoft doesn't document
there products very well and relies on the users to help each other.

"Bud" wrote:

Joel

I replied Yes to "Did this post answer the question" to each of your replies
and when I hover over it it says that is isn't rated yet. What's the issue
with that do you know? maybe it's just a timing thing.

"Joel" wrote:

You have a lot of good code. Here are some comments that may help you solve
your problems.

1) Do you want to create the new sheets in the same workbook or a new
workbook. The original code was creating the sheets in a new workbook.

2) When you are adding sheets do you really want to add the sheets if they
are already in the workbook. One thing you can do is check if the sheet
exists. if it exists then clear the worksheet, otherwise, add the worksheet.

I will modify the code any way you want as long as I'm given the right
inputs to work with. Some of the comments in the original code referes to
deleting a worksheet and then adding the sheet. I think it is better to
check if the worksheet exists and then determining what to do base if the
sheet exists. This is code I often use to check if a sheet exists

Found = False
for each sht in sheets
if sht.name = "SWIM Time Data" then
Found = True
Exit For
end if
next sht

If Found = true then
Sheets("SWIM Time Data").Cells.Clearcontents
else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "SWIM Time Data"
End if

"Bud" wrote:

It's a name already in the workbook...Been there

Sorry, this isn't solving the problem...I'll have to think about some more
or ask for other help or something.

"Joel" wrote:

You have two sheets with the same name. This is not my problem. Delete one
of the duplicate sheets or change the name of the sheet is the macro.

"Joel" wrote:

The Sheets.Add is creating a new workbook because you didn't use the After
Property. Try these changes



Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "SWIM Time Data"


I updated your entire macro. Avoid using the select method. Also check the
variable BR. I don't know if it is the same on all sheets. Your code isn't
setting BR as you go from sheet to sheet.



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
With Sheets("SWIMInput").Range("a1")
If .FormulaR1C1 < "EDSNETID" Then
MsgBox "Please close workbook, re-open, and paste SWIM_Master_Input
MSPS info into SWIMInput worksheet"
Exit Sub
End If
End With

'Start of selecting SWIM Time Data deleting and than re-creating
With Sheets("SWIM Time Data")
Application.CutCopyMode = False
.Rows(1).Delete
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
End With
Sheets.Add after:=Sheets(Sheets.Count)
Set NewSht1 = ActiveSheet
NewSht1.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"
'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
With Sheets("SWIMInput")
br = .Cells(Rows.Count, "b").End(xlUp).Row
strDate = Format(Now, "ddmmmyyyy")

'Sort the data
Set SortRange = .Rows("2:" & br)
SortRange.Sort _
Key1:=.Range("A2"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
DataOption1:=xlSortTextAsNumbers
End With

'MsgBox br

'Select SWIMTimeDataSav as we are going to build the UPLOAD file
With Sheets("SWIMTimeDataSav")
' First Clear all contents of worksheet
.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
With .Range("B1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Range("C1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Range("D1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Range("E1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Range("f1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Range("G1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Range("i1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With

Sheets("SWIMTimeDataSav").Cells.Copy
With Sheets("SWIM Time Data")
.Cells.PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats

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