So, I not sure why I can't ever figure this out... I have a new script I'm trying to auto-fill rows to match the data in column A. Can someone explain what needs to be replaced and why?
I've tried replacing this:
Selection.AutoFill Destination:=Range("F2:F33")
With this:
Range(Selection, Selection.End(xlDown)).Selec
But no luck.
Here is the full script. There's three places where auto-fill needs to be replaced.
Sub Macro4()'
' Macro4 Macro
'
'
Sheets("AAP Data").Select
Cells.Select
Selection.Copy
Sheets("Template").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A:A,B:B,C:C,E:E,G:G").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Template").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("2:2").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Clear
Range("C1").Select
ActiveCell.FormulaR1C1 = "Qty Purchased"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Qty Returned"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Eligible"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Hotkey"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Purchases!C[-2]:C[-1],2,FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Returns!C[-3]:C[-2],2,FALSE)"
Range("C2:D2").Select
Selection.AutoFill Destination:=Range("C2:D33")
Range("C2:D33").Select
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G7").Select
Cells.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("E2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]-RC[-3]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E33")
Range("E2:E33").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Hotkey!C[-5],1,FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F33")
Range("F2:F33").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("F1").Select
Selection.AutoFilter
Selection.AutoFilter
End Sub
Thank you everyone for your help.