VBA Bug

[mikec1982]

I have a line of VBA code that runs correctly but is cutting off some of the results. It is for a group of students. The results compile for last names A-S, but I believe because one of my ranges is set incorrectly, I am not getting the T-Z students.

Most of the ranges are set to A2:E200, but when I attempt to increase that range to A2:E300, I get an error on this line:

Selection.AutoFill Destination:=Range("A2:E200"), Type:=xlFillDefault

Any idea how I can make the range larger so that it includes all results?

Sheets("Student").Select
    Range("A1:C130").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Student First"
    Sheets("Parent").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC[2],""*""&{""mother"",""stepmother"",""stepfather"",""father""}&""*"")),LEFT(TRIM(PrepNameTag!RC[2]),SEARCH("" "",TRIM(PrepNameTag!RC[2]))-1),IF(COUNTIF(PrepNameTag!RC[2],""*Other*""),""""))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC[1],""*""&{""mother"",""stepmother"",""father"",""stepfather""}&""*"")),MID(TRIM(PrepNameTag!RC[1]),SEARCH("" "",TRIM(PrepNameTag!RC[1]))+1,SEARCH(""("",PrepNameTag!RC[1])-1-SEARCH("" "",TRIM(PrepNameTag!RC[1]))),"""")"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC,""*""&{""mother"",""father"",""stepmother"",""stepfather""}&""*"")),PrepNameTag!RC[-2],"""")"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC[-1],""*""&{""mother"",""father"",""stepmother"",""stepfather""}&""*"")),PrepNameTag!RC[-2],"""")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC[-2],""*""&{""mother"",""father"",""stepmother"",""stepfather""}&""*"")),PrepNameTag!RC[-1],"""")"
    Range("A2:E2").Select
    Selection.AutoFill Destination:=Range("A2:E200"), Type:=xlFillDefault
    Range("A2:E100").Select
    ActiveWindow.SmallScroll Down:=-24
    Selection.AutoFill Destination:=Range("A2:E200"), Type:=xlFillDefault
    Range("A2:E200").Select
    ActiveWindow.SmallScroll Down:=-306
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Parent").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Parent").Sort.SortFields.Add Key:=Range("B2:B200") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Parent").Sort.SortFields.Add Key:=Range("A2:A200") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Parent").Sort
        .SetRange Range("A2:E200")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Sheets("Other").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC[2],""*""&{""grandmother"",""grandfather"",""brother"",""sister"",""aunt"",""uncle"",""friend""}&""*"")),LEFT(TRIM(PrepNameTag!RC[2]),SEARCH("" "",TRIM(PrepNameTag!RC[2]))-1),IF(COUNTIF(PrepNameTag!RC[2],""""),""""))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(COUNTIF(PrepNameTag!RC[1],""*""&{""grandmother"",""brother"",""grandfather"",""sister"",""aunt"",""uncle"",""friend""}&""*"")),MID(TRIM(PrepNameTag!RC[1]),SEARCH("" "",TRIM(PrepNameTag!RC[1]))+1,SEARCH(""("",PrepNameTag!RC[1])-1-SEARCH("" "",TRIM(PrepNameTag!RC[1]))),"""")"
    Range("A2:B2").Select
    Selection.AutoFill Destination:=Range("A2:B200"), Type:=xlFillDefault
    Range("A2:B200").Select
    ActiveWindow.SmallScroll Down:=-198
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Other").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Other").Sort.SortFields.Add Key:=Range("B2:B200") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Other").Sort.SortFields.Add Key:=Range("A2:A200") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Other").Sort
        .SetRange Range("A2:B200")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

 

[Micron]

maybe? Range("A2:E" & Rows.count).End(xlUp).Row

Not sure if that will work for a multi column range, but it does for one column only.

 

[MARK858]

I get an error on this line:

What does the error state when you click debug?