Hello

A file has one line of header information and than only one line of data.
When it hits the first autofill in the attached macro it gives a Run-Time
error 1004 in Autofill method of range class failed.

Can someone suggest a fix for this so it doesn't bring back this message or
looks for it and bypasses the Autofill?

It's aborting on this first Autofill. If I have the header record plus two
records it won't have that message and everything is fine.
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))

I don't know a lot about Macros...just enough to try and get by.



Sub SAPTime1()
'
' SAPTime1 Macro
' Macro recorded 10/19/2008 by Bud Zeiger czj63c
'
' Keyboard Shortcut: Ctrl+Shift+S
'
'Let's check to see if we have any data or the right data
Sheets("SAPTasks").Select
Range("a1").Select
If ActiveCell.FormulaR1C1 < "Personnel Number" Then
MsgBox "Please close workbook, re-open, and paste ZZTaskDB_Disp SAP
info into SAPTasks worksheet"
Exit Sub
End If

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

'Start of selecting TimeCardDataSav deleting and than re-creating
' This is a hidden file
'Sheets("TimeCardDataSav").Select
'Application.CutCopyMode = False
'Selection.Delete Shift:=xlUp
'Application.DisplayAlerts = False
'ActiveWindow.SelectedSheets.Delete
'Application.DisplayAlerts = True
'Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "TimeCardDataSav"
'End of selecting TimeCardData deleting and than re-creating

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

Dim br As Long

'Select SAPTasks and count the number of active rows
Sheets("SAPTasks").Select
Cells.Select
br = Cells(Rows.Count, "b").End(xlUp).Row
'Select and enter br in an empty cell

'Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("E2") _
' , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
' False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
' DataOption2:=xlSortNormal

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

'BEGIN - Ensure that column A is formated to numeric
Columns("A:A").Select
Selection.NumberFormat = "0"

'Select and enter 1 in an empty cell
Range("R1").Select
ActiveCell.FormulaR1C1 = "1"

'Copy the cell
Selection.Copy

'Select from first cell to last used cell in column A
'Identifying last used cell is like selecting the
'last cell in column A (65536 or something.)
'Holding the Ctrl key and pressing up arrow to
'find last used cell in the column.
Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Select

'Paste Special multiply
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
'END - Ensure that column A is formatted to numeric

'MsgBox br

'Select TimeCardDataSav as we are going to build the UPLOAD file
Sheets("TimeCardDataSav").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
' Range("S1").Select
'ActiveCell.FormulaR1C1 = br

Cells(1, "a") = "CATS Document Number"
Cells(1, "b") = "Person"
Cells(1, "c") = "Workdate"
Cells(1, "d") = "Time From"
Cells(1, "e") = "Time To"
Cells(1, "f") = "Receiver WBS Element"
Cells(1, "g") = "Absence-Attendance Type"
Cells(1, "h") = "Hours"
Cells(1, "i") = "Text (40chars)"
Cells(1, "j") = "User Field1 (15 Chars)"
Cells(1, "k") = "User Field 2 (15chars)"
Cells(1, "l") = "User Field 3 (15chars)"
Cells(1, "m") = "External Project Task"
'Cells(1, "n") = "Complete (X=complete)"
Cells(1, "n") = "Remaining Work (in Hours)"
Cells(1, "o") = "Invoice Role"
Cells(1, "p") = "Capability"
Cells(2, "a") = " " 'ID
Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel
Cells(2, "f") = "=SAPTasks!J2" 'SAP WBSe
Cells(2, "g") = "2000"
Cells(2, "i") = "=SAPTasks!e2" 'Task name
Cells(2, "k") = "=SAPTasks!o2" 'Resource name
Cells(2, "l") = "=SAPTasks!p2" 'EDS NET ID
Cells(2, "m") = "=SAPTasks!c2" 'External Project Task
' The next matching formula concatenates the number of records in
saptasks for knowing when to stop on the fill down
'Cells(2, "k") = "=IF($b$2:$b$" & br &
"="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b $2:$b" & br &
",Personnel!$A$1:$A$1000,0))))"
' Thirdly Auto fill down for the number rows we obtained from the
SAP-Simulation
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))
Cells(2, "b").AutoFill 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, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k"))
Cells(2, "i").AutoFill Destination:=Range(Cells(2, "i"), Cells(br, "i"))
Cells(2, "l").AutoFill Destination:=Range(Cells(2, "l"), Cells(br, "l"))
Cells(2, "m").AutoFill Destination:=Range(Cells(2, "m"), Cells(br, "m"))


'Wrapping text Begin
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("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("N1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ActiveWindow.SmallScroll ToRight:=2
Range("C2").Select

Columns("A:A").ColumnWidth = 3.5
'Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 8.5
Columns("D:D").ColumnWidth = 2.5
Columns("E:E").ColumnWidth = 2.5
Columns("F:F").ColumnWidth = 24
Columns("g:g").ColumnWidth = 6
Columns("h:h").ColumnWidth = 6
Columns("i:i").ColumnWidth = 45
Columns("k:k").ColumnWidth = 22
Columns("m:m").ColumnWidth = 19

Sheets("TimeCardDataSav").Select
Cells.Select
Selection.Copy
Sheets("TimeCardData").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").ColumnWidth = 3.5
'Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 8.5
Columns("D:D").ColumnWidth = 2.5
Columns("E:E").ColumnWidth = 2.5
Columns("F:F").ColumnWidth = 24
Columns("g:g").ColumnWidth = 6
Columns("h:h").ColumnWidth = 6
Columns("i:i").ColumnWidth = 45
Columns("k:k").ColumnWidth = 22
Columns("m:m").ColumnWidth = 19

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

'Hiding the saved file
'Sheets("TimeCardDataSav").Select
'ActiveWindow.SelectedSheets.Visible = False

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

End Sub

Hi Bud,

The range for autofill must be greater than the initial range otherwise
nothing to autofill so try the following and it should bypass the autofill
under these conditions.

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"))
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, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br,
"k"))
Cells(2, "i").AutoFill Destination:=Range(Cells(2, "i"), Cells(br,
"i"))
Cells(2, "l").AutoFill Destination:=Range(Cells(2, "l"), Cells(br,
"l"))
Cells(2, "m").AutoFill Destination:=Range(Cells(2, "m"), Cells(br,
"m"))
End If

--
Regards,

OssieMac


"Bud" wrote:

Hello

A file has one line of header information and than only one line of data.
When it hits the first autofill in the attached macro it gives a Run-Time
error 1004 in Autofill method of range class failed.

Can someone suggest a fix for this so it doesn't bring back this message or
looks for it and bypasses the Autofill?

It's aborting on this first Autofill. If I have the header record plus two
records it won't have that message and everything is fine.
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))

I don't know a lot about Macros...just enough to try and get by.



Sub SAPTime1()
'
' SAPTime1 Macro
' Macro recorded 10/19/2008 by Bud Zeiger czj63c
'
' Keyboard Shortcut: Ctrl+Shift+S
'
'Let's check to see if we have any data or the right data
Sheets("SAPTasks").Select
Range("a1").Select
If ActiveCell.FormulaR1C1 < "Personnel Number" Then
MsgBox "Please close workbook, re-open, and paste ZZTaskDB_Disp SAP
info into SAPTasks worksheet"
Exit Sub
End If

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

'Start of selecting TimeCardDataSav deleting and than re-creating
' This is a hidden file
'Sheets("TimeCardDataSav").Select
'Application.CutCopyMode = False
'Selection.Delete Shift:=xlUp
'Application.DisplayAlerts = False
'ActiveWindow.SelectedSheets.Delete
'Application.DisplayAlerts = True
'Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "TimeCardDataSav"
'End of selecting TimeCardData deleting and than re-creating

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

Dim br As Long

'Select SAPTasks and count the number of active rows
Sheets("SAPTasks").Select
Cells.Select
br = Cells(Rows.Count, "b").End(xlUp).Row
'Select and enter br in an empty cell

'Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("E2") _
' , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
' False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
' DataOption2:=xlSortNormal

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

'BEGIN - Ensure that column A is formated to numeric
Columns("A:A").Select
Selection.NumberFormat = "0"

'Select and enter 1 in an empty cell
Range("R1").Select
ActiveCell.FormulaR1C1 = "1"

'Copy the cell
Selection.Copy

'Select from first cell to last used cell in column A
'Identifying last used cell is like selecting the
'last cell in column A (65536 or something.)
'Holding the Ctrl key and pressing up arrow to
'find last used cell in the column.
Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Select

'Paste Special multiply
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
'END - Ensure that column A is formatted to numeric

'MsgBox br

'Select TimeCardDataSav as we are going to build the UPLOAD file
Sheets("TimeCardDataSav").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
' Range("S1").Select
'ActiveCell.FormulaR1C1 = br

Cells(1, "a") = "CATS Document Number"
Cells(1, "b") = "Person"
Cells(1, "c") = "Workdate"
Cells(1, "d") = "Time From"
Cells(1, "e") = "Time To"
Cells(1, "f") = "Receiver WBS Element"
Cells(1, "g") = "Absence-Attendance Type"
Cells(1, "h") = "Hours"
Cells(1, "i") = "Text (40chars)"
Cells(1, "j") = "User Field1 (15 Chars)"
Cells(1, "k") = "User Field 2 (15chars)"
Cells(1, "l") = "User Field 3 (15chars)"
Cells(1, "m") = "External Project Task"
'Cells(1, "n") = "Complete (X=complete)"
Cells(1, "n") = "Remaining Work (in Hours)"
Cells(1, "o") = "Invoice Role"
Cells(1, "p") = "Capability"
Cells(2, "a") = " " 'ID
Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel
Cells(2, "f") = "=SAPTasks!J2" 'SAP WBSe
Cells(2, "g") = "2000"
Cells(2, "i") = "=SAPTasks!e2" 'Task name
Cells(2, "k") = "=SAPTasks!o2" 'Resource name
Cells(2, "l") = "=SAPTasks!p2" 'EDS NET ID
Cells(2, "m") = "=SAPTasks!c2" 'External Project Task
' The next matching formula concatenates the number of records in
saptasks for knowing when to stop on the fill down
'Cells(2, "k") = "=IF($b$2:$b$" & br &
"="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b $2:$b" & br &
",Personnel!$A$1:$A$1000,0))))"
' Thirdly Auto fill down for the number rows we obtained from the
SAP-Simulation
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))
Cells(2, "b").AutoFill 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, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k"))
Cells(2, "i").AutoFill Destination:=Range(Cells(2, "i"), Cells(br, "i"))
Cells(2, "l").AutoFill Destination:=Range(Cells(2, "l"), Cells(br, "l"))
Cells(2, "m").AutoFill Destination:=Range(Cells(2, "m"), Cells(br, "m"))


'Wrapping text Begin
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("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("N1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ActiveWindow.SmallScroll ToRight:=2
Range("C2").Select

Columns("A:A").ColumnWidth = 3.5
'Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 8.5
Columns("D:D").ColumnWidth = 2.5
Columns("E:E").ColumnWidth = 2.5
Columns("F:F").ColumnWidth = 24
Columns("g:g").ColumnWidth = 6
Columns("h:h").ColumnWidth = 6
Columns("i:i").ColumnWidth = 45
Columns("k:k").ColumnWidth = 22
Columns("m:m").ColumnWidth = 19

Sheets("TimeCardDataSav").Select
Cells.Select
Selection.Copy
Sheets("TimeCardData").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").ColumnWidth = 3.5
'Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 8.5
Columns("D:D").ColumnWidth = 2.5
Columns("E:E").ColumnWidth = 2.5
Columns("F:F").ColumnWidth = 24
Columns("g:g").ColumnWidth = 6
Columns("h:h").ColumnWidth = 6
Columns("i:i").ColumnWidth = 45
Columns("k:k").ColumnWidth = 22
Columns("m:m").ColumnWidth = 19

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

'Hiding the saved file
'Sheets("TimeCardDataSav").Select
'ActiveWindow.SelectedSheets.Visible = False

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

End Sub

Maybe you could just check to see what br is before you do the work.

if br 2 then
'do the autofill



Bud wrote:

Hello

A file has one line of header information and than only one line of data.
When it hits the first autofill in the attached macro it gives a Run-Time
error 1004 in Autofill method of range class failed.

Can someone suggest a fix for this so it doesn't bring back this message or
looks for it and bypasses the Autofill?

It's aborting on this first Autofill. If I have the header record plus two
records it won't have that message and everything is fine.
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))

I don't know a lot about Macros...just enough to try and get by.

Sub SAPTime1()
'
' SAPTime1 Macro
' Macro recorded 10/19/2008 by Bud Zeiger czj63c
'
' Keyboard Shortcut: Ctrl+Shift+S
'
'Let's check to see if we have any data or the right data
Sheets("SAPTasks").Select
Range("a1").Select
If ActiveCell.FormulaR1C1 < "Personnel Number" Then
MsgBox "Please close workbook, re-open, and paste ZZTaskDB_Disp SAP
info into SAPTasks worksheet"
Exit Sub
End If

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

'Start of selecting TimeCardDataSav deleting and than re-creating
' This is a hidden file
'Sheets("TimeCardDataSav").Select
'Application.CutCopyMode = False
'Selection.Delete Shift:=xlUp
'Application.DisplayAlerts = False
'ActiveWindow.SelectedSheets.Delete
'Application.DisplayAlerts = True
'Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "TimeCardDataSav"
'End of selecting TimeCardData deleting and than re-creating

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

Dim br As Long

'Select SAPTasks and count the number of active rows
Sheets("SAPTasks").Select
Cells.Select
br = Cells(Rows.Count, "b").End(xlUp).Row
'Select and enter br in an empty cell

'Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("E2") _
' , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
' False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
' DataOption2:=xlSortNormal

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

'BEGIN - Ensure that column A is formated to numeric
Columns("A:A").Select
Selection.NumberFormat = "0"

'Select and enter 1 in an empty cell
Range("R1").Select
ActiveCell.FormulaR1C1 = "1"

'Copy the cell
Selection.Copy

'Select from first cell to last used cell in column A
'Identifying last used cell is like selecting the
'last cell in column A (65536 or something.)
'Holding the Ctrl key and pressing up arrow to
'find last used cell in the column.
Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Select

'Paste Special multiply
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
'END - Ensure that column A is formatted to numeric

'MsgBox br

'Select TimeCardDataSav as we are going to build the UPLOAD file
Sheets("TimeCardDataSav").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
' Range("S1").Select
'ActiveCell.FormulaR1C1 = br

Cells(1, "a") = "CATS Document Number"
Cells(1, "b") = "Person"
Cells(1, "c") = "Workdate"
Cells(1, "d") = "Time From"
Cells(1, "e") = "Time To"
Cells(1, "f") = "Receiver WBS Element"
Cells(1, "g") = "Absence-Attendance Type"
Cells(1, "h") = "Hours"
Cells(1, "i") = "Text (40chars)"
Cells(1, "j") = "User Field1 (15 Chars)"
Cells(1, "k") = "User Field 2 (15chars)"
Cells(1, "l") = "User Field 3 (15chars)"
Cells(1, "m") = "External Project Task"
'Cells(1, "n") = "Complete (X=complete)"
Cells(1, "n") = "Remaining Work (in Hours)"
Cells(1, "o") = "Invoice Role"
Cells(1, "p") = "Capability"
Cells(2, "a") = " " 'ID
Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel
Cells(2, "f") = "=SAPTasks!J2" 'SAP WBSe
Cells(2, "g") = "2000"
Cells(2, "i") = "=SAPTasks!e2" 'Task name
Cells(2, "k") = "=SAPTasks!o2" 'Resource name
Cells(2, "l") = "=SAPTasks!p2" 'EDS NET ID
Cells(2, "m") = "=SAPTasks!c2" 'External Project Task
' The next matching formula concatenates the number of records in
saptasks for knowing when to stop on the fill down
'Cells(2, "k") = "=IF($b$2:$b$" & br &
"="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b $2:$b" & br &
",Personnel!$A$1:$A$1000,0))))"
' Thirdly Auto fill down for the number rows we obtained from the
SAP-Simulation
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))
Cells(2, "b").AutoFill 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, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k"))
Cells(2, "i").AutoFill Destination:=Range(Cells(2, "i"), Cells(br, "i"))
Cells(2, "l").AutoFill Destination:=Range(Cells(2, "l"), Cells(br, "l"))
Cells(2, "m").AutoFill Destination:=Range(Cells(2, "m"), Cells(br, "m"))


'Wrapping text Begin
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("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("N1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ActiveWindow.SmallScroll ToRight:=2
Range("C2").Select

Columns("A:A").ColumnWidth = 3.5
'Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 8.5
Columns("D:D").ColumnWidth = 2.5
Columns("E:E").ColumnWidth = 2.5
Columns("F:F").ColumnWidth = 24
Columns("g:g").ColumnWidth = 6
Columns("h:h").ColumnWidth = 6
Columns("i:i").ColumnWidth = 45
Columns("k:k").ColumnWidth = 22
Columns("m:m").ColumnWidth = 19

Sheets("TimeCardDataSav").Select
Cells.Select
Selection.Copy
Sheets("TimeCardData").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").ColumnWidth = 3.5
'Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 8.5
Columns("D:D").ColumnWidth = 2.5
Columns("E:E").ColumnWidth = 2.5
Columns("F:F").ColumnWidth = 24
Columns("g:g").ColumnWidth = 6
Columns("h:h").ColumnWidth = 6
Columns("i:i").ColumnWidth = 45
Columns("k:k").ColumnWidth = 22
Columns("m:m").ColumnWidth = 19

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

'Hiding the saved file
'Sheets("TimeCardDataSav").Select
'ActiveWindow.SelectedSheets.Visible = False

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

End Sub

--

Dave Peterson