Run-time error '1004' Method 'Range" of 'Object' _Global' failed

%3CLINGO-SUB%20id%3D%22lingo-sub-2261705%22%20slang%3D%22en-US%22%3ERun-time%20error%20'1004'%20Method%20'Range%22%20of%20'Object'%20_Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261705%22%20slang%3D%22en-US%22%3E%3CP%3Esuper%20new%20to%20Macros%20and%20VB.%20What%20do%20I%20need%20to%20do%20to%20make%20my%20Macro%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20DeliveryReport()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20DeliveryReport%20Macro%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Keyboard%20Shortcut%3A%20Ctrl%2Bd%3CBR%20%2F%3E'%3CBR%20%2F%3EColumns(%22A%3AA%22).Select%3CBR%20%2F%3ESelection.Insert%20Shift%3A%3DxlToRight%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3ERange(%22A1%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22Parts%20Received%20Y%2FN%22%3CBR%20%2F%3EColumns(%22G%3AG%22).Select%3CBR%20%2F%3ESelection.Insert%20Shift%3A%3DxlToRight%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22Tech%20Comments%22%3CBR%20%2F%3EColumns(%22K%3AK%22).Select%3CBR%20%2F%3ESelection.Delete%20Shift%3A%3DxlToLeft%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3EWith%20Selection%3CBR%20%2F%3E.HorizontalAlignment%20%3D%20xlGeneral%3CBR%20%2F%3E.VerticalAlignment%20%3D%20xlBottom%3CBR%20%2F%3E.WrapText%20%3D%20True%3CBR%20%2F%3E.Orientation%20%3D%200%3CBR%20%2F%3E.AddIndent%20%3D%20False%3CBR%20%2F%3E.IndentLevel%20%3D%200%3CBR%20%2F%3E.ShrinkToFit%20%3D%20False%3CBR%20%2F%3E.ReadingOrder%20%3D%20xlContext%3CBR%20%2F%3E.MergeCells%20%3D%20False%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ESelection.RowHeight%20%3D%20100%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Clear%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22F2%3AF176594%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22A1%3AM176594%22)%3CBR%20%2F%3E.Header%20%3D%20xlYes%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Clear%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%3CBR%20%2F%3E%22F2%3AF176595%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22A1%3AM176595%22)%3CBR%20%2F%3E.Header%20%3D%20xlYes%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EWith%20Selection.Font%3CBR%20%2F%3E.Name%20%3D%20%22Calibri%22%3CBR%20%2F%3E.Size%20%3D%2020%3CBR%20%2F%3E.Strikethrough%20%3D%20False%3CBR%20%2F%3E.Superscript%20%3D%20False%3CBR%20%2F%3E.Subscript%20%3D%20False%3CBR%20%2F%3E.OutlineFont%20%3D%20False%3CBR%20%2F%3E.Shadow%20%3D%20False%3CBR%20%2F%3E.Underline%20%3D%20xlUnderlineStyleNone%3CBR%20%2F%3E.ThemeColor%20%3D%20xlThemeColorLight1%3CBR%20%2F%3E.TintAndShade%20%3D%200%3CBR%20%2F%3E.ThemeFont%20%3D%20xlThemeFontMinor%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EColumns(%22G%3AI%22).Select%3CBR%20%2F%3EWith%20Selection.Font%3CBR%20%2F%3E.Name%20%3D%20%22Calibri%22%3CBR%20%2F%3E.Size%20%3D%2012%3CBR%20%2F%3E.Strikethrough%20%3D%20False%3CBR%20%2F%3E.Superscript%20%3D%20False%3CBR%20%2F%3E.Subscript%20%3D%20False%3CBR%20%2F%3E.OutlineFont%20%3D%20False%3CBR%20%2F%3E.Shadow%20%3D%20False%3CBR%20%2F%3E.Underline%20%3D%20xlUnderlineStyleNone%3CBR%20%2F%3E.ThemeColor%20%3D%20xlThemeColorLight1%3CBR%20%2F%3E.TintAndShade%20%3D%200%3CBR%20%2F%3E.ThemeFont%20%3D%20xlThemeFontMinor%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ERows(%221%3A1%22).Select%3CBR%20%2F%3EWith%20Selection.Font%3CBR%20%2F%3E.Name%20%3D%20%22Calibri%22%3CBR%20%2F%3E.Size%20%3D%2012%3CBR%20%2F%3E.Strikethrough%20%3D%20False%3CBR%20%2F%3E.Superscript%20%3D%20False%3CBR%20%2F%3E.Subscript%20%3D%20False%3CBR%20%2F%3E.OutlineFont%20%3D%20False%3CBR%20%2F%3E.Shadow%20%3D%20False%3CBR%20%2F%3E.Underline%20%3D%20xlUnderlineStyleNone%3CBR%20%2F%3E.ThemeColor%20%3D%20xlThemeColorLight1%3CBR%20%2F%3E.TintAndShade%20%3D%200%3CBR%20%2F%3E.ThemeFont%20%3D%20xlThemeFontMinor%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EColumns(%22A%3AA%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%209%3CBR%20%2F%3EColumns(%22B%3AB%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2017%3CBR%20%2F%3EColumns(%22C%3AE%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%209%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22Order%20Line%20Number%22%3CBR%20%2F%3EColumns(%22F%3AF%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2022%3CBR%20%2F%3EColumns(%22G%3AG%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2036%3CBR%20%2F%3EColumns(%22H%3AH%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2012%3CBR%20%2F%3EColumns(%22I%3AI%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2070%3CBR%20%2F%3EColumns(%22J%3AJ%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2018%3CBR%20%2F%3EColumns(%22K%3AK%22).Select%3CBR%20%2F%3ESelection.ColumnWidth%20%3D%2022%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3ESelection.AutoFilter%3CBR%20%2F%3EWith%20ActiveWindow%3CBR%20%2F%3E.SplitColumn%20%3D%200%3CBR%20%2F%3E.SplitRow%20%3D%201%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EActiveWindow.FreezePanes%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2261705%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

super new to Macros and VB. What do I need to do to make my Macro work?

 

Sub DeliveryReport()
'
' DeliveryReport Macro
'
' Keyboard Shortcut: Ctrl+d
'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Parts Received Y/N"
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "Tech Comments"
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.RowHeight = 100
Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"F2:F176594"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:M176594")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"F2:F176595"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:M176595")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With Selection.Font
.Name = "Calibri"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Columns("G:I").Select
With Selection.Font
.Name = "Calibri"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Rows("1:1").Select
With Selection.Font
.Name = "Calibri"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Columns("A:A").Select
Selection.ColumnWidth = 9
Columns("B:B").Select
Selection.ColumnWidth = 17
Columns("C:E").Select
Selection.ColumnWidth = 9
ActiveCell.FormulaR1C1 = "Order Line Number"
Columns("F:F").Select
Selection.ColumnWidth = 22
Columns("G:G").Select
Selection.ColumnWidth = 36
Columns("H:H").Select
Selection.ColumnWidth = 12
Columns("I:I").Select
Selection.ColumnWidth = 70
Columns("J:J").Select
Selection.ColumnWidth = 18
Columns("K:K").Select
Selection.ColumnWidth = 22
Cells.Select
Selection.AutoFilter
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub

0 Replies