In need of help with a code

[noahnoah]

I have upload a sample file of what some of the code does but the code is not tuned. There is the main sheet where all the information is update and the command buttons are there to separate the information to there respective sheets. The update command button works for ok but it needs a partial code to auto fill column a until the last unused row. Also every time there is an update on the main sheet, rows 633:635 has a code to separate some of the data to there perspective sheets but the update changes the ranges for that section and is there a way to auto adjust the range.

[SamT]

Sheet "All Students is main sheet. Command buttons at bottom of main sheet.

[noahnoah]

Yes, that is true the main sheet is called All Students.

[SamT]

What is wrong with this bit of code?


Private Sub Worksheet_Change(ByVal Target As Range)
Call UdateFromAllStudents
End Sub

Sub UpdateFromAllStudents()

Please use the VBA Menu item "Debug" + "Compile VBA Project" to fix as many errors as you can before uploading your file.

Thank you.

[Paul_Hossler]

1. Fixed a typo and added Option Explicit where missing

2. The worksheet event has to be in the worksheet code module, and you had it in a standard module. I added some suggestions to it and put it in the correct module (attachment). Now when you change something on ALL STUDENTS, the sub will be called

3. There are some speed improvements that can be made later, but it's important to get it running before we try to optimize

4. Your sample file has 2 extension dots in it (just FYI)




Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rOriginalCell As Range
If Intersect(Target, Me.Cells(1, 1).CurrentRegion) Is Nothing Then Exit Sub
Set rOriginalCell = Target.Cells(1, 1)
Application.ScreenUpdating = False
Application.EnableEvents = False
Call UpdateFromAllStudents
Application.EnableEvents = True
Application.ScreenUpdating = True

rOriginalCell.Select

End Sub