In need of help with a code!

[NoelNoel]

Sub ResetDestinationSheets()
   
    Call ResetThisSheet("SPED")
    Call ResetThisSheet("ELL-LEP")
    Call ResetThisSheet("MI")
    Call ResetThisSheet("NORTH")
    Call ResetThisSheet("CCC")
    Call ResetThisSheet("ONE EOC")
    Call ResetThisSheet("TWO EOC")
    Call ResetThisSheet("THREE EOC")
    Call ResetThisSheet("FOUR EOC")
    Call ResetThisSheet("FIVE EOC")

End Sub

Sub ResetThisSheet(ByRef SheetToClear As String)
    Sheets(SheetToClear).Range("A2:U" & Rows.Count).Clear
End Sub

How can it be written so that it clears to the last used row based on column A.

 

[Hui]

Sub ResetThisSheet(ByRef SheetToClear As String)
    Sheets(SheetToClear).Range("A2:U" & Range("A" & Rows.Count).End(xlUp).Row).Clear
End Sub

This will assume that Column A has data in the last row

 

[NoelNoel]

Sub ResetThisSheet(ByRef SheetToClear As String)
    Sheets(SheetToClear).Range("A2:U" & Range("A" & Rows.Count).End(xlUp).Row).Clear
End Sub

This will assume that Column A has data in the last row

Thank you Hui.
Your addition to the code Works perfect.

I have another part of a code that is giving me small problems.
I am have been trying to code in an autofill code to the rows copied to their destination sheets
The last part of the code from ranges B633 through H635 which is the part that is giving me problems because as the main sheet updates the ranges change and when the code is activated it copies part of the data. Is their an
addition to the code to make the range adjust when their is update to the main sheet.

Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
        Call UdateFromAllStudents
    End Sub
Sub UpdateFromAllStudents()
    ' clear whatever you had previously written to the destination sheets
    Call ResetDestinationSheets

    Dim LR As Long, I As Long, LastRow As Long, myRange, xRow As Long
  
    LR = Range("A" & Rows.Count).End(xlUp).Row
  
    
  
   For I = 2 To LR
    If Range("I" & I).Value = "Y" Then Rows(I).Copy Destination:=Sheets("SPED").Range("A" & Rows.Count).End(xlUp).Offset(1)
  
    If Range("H" & I).Value = "Y" Then Rows(I).Copy Destination:=Sheets("ELL-LEP").Range("A" & Rows.Count).End(xlUp).Offset(1)
  
    If Range("J" & I).Value = "Y" Then Rows(I).Copy Destination:=Sheets("MI").Range("A" & Rows.Count).End(xlUp).Offset(1)
  
    If Range("L" & I).Value = "NAC" Then Rows(I).Copy Destination:=Sheets("NORTH").Range("A" & Rows.Count).End(xlUp).Offset(1)
  
    If Range("L" & I).Value = "CCC" Then Rows(I).Copy Destination:=Sheets("CCC").Range("A" & Rows.Count).End(xlUp).Offset(1)
      
   Next I
  
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("B633:H" & LR).Select
  
    Sheets("ALL STUDENTS").Range("$B633:$H633,$B635:$H635").Copy Sheets("SPED").Range("B50")
    Sheets("ALL STUDENTS").Range("B633:H633,B636:H636").Copy Sheets("ELL-LEP").Range("B160")
    Sheets("ALL STUDENTS").Range("B633:H636").Copy Sheets("MI").Range("B70")
    Sheets("ALL STUDENTS").Range("B633:H636").Copy Sheets("NORTH").Range("B110")
    Sheets("ALL STUDENTS").Range("B633:H636").Copy Sheets("CCC").Range("B40")

  
  
End Sub

 

[Hui]

Are there any fields with values that you can use to determine the range Rows 633:635 etc?

 

[NoelNoel]

Are there any fields with values that you can use to determine the range Rows 633:635 etc?

I apologize for not uploading the sample workbook before. You will be able to see what I am trying to do with the other sheets. On the range rows 633:635 do have value but I am not able to make it work so every time I update my main sheet the ranges we are talking about the data copied changes.

 

[Hui]

Instead of Hard Coding the Range B633:H635

you could use something like:

Dim rngFound As Range
Set rngFound = Range("B:B").Find("Campus Name")
Set rngFound = rngFound.CurrentRegion

RngFound will be set to the range of all cells directly connected to the Cell with Campus Name in it, in your case: B633:H636

You can then use that to copy or manipulate

 

[NoelNoel]

Instead of Hard Coding the Range B633:H635

you could use something like:

Dim rngFound As Range
Set rngFound = Range("B:B").Find("Campus Name")
Set rngFound = rngFound.CurrentRegion

RngFound will be set to the range of all cells directly connected to the Cell with Campus Name in it, in your case: B633:H636

You can then use that to copy or manipulate

 

[NoelNoel]

I inserted the code but give me an error message "Object variable or with block variable not set". What am I doing wrong?