B
Bud
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
").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").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
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
").ColumnWidth = 2.5Columns("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
").ColumnWidth = 2.5Columns("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