Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Shift down content of range of row cells in Sheet 1 when new


Salutations! Please help me out. I have a gradebook worksheet, where names and grades I assigned appear. In another tab (Sheet 2) I have the list of names that appear in the gradebook (where I use the formula =Sheet2!A3 and down). If later I need to include a new student in a certain position, in Sheet 2 I would just copy the names that I want to shift down a space and paste them in position and then write the new name in the freed space of the list. Now, is there a way (formula) that in "Gradebook" the new name appears in the right position, all the other names down shift one space down (as I did in Sheet 2), and also all the grades of those students shifted down in place with them (a range I can specify, in the image example I have only two different grades each)? I do not want to insert new rows in Gradebook, I just want the content of cells shift down accordingly. I include an image to illustrate my question. Thank you very much.




We've done work to provide two valid answers to your question. Please do us the courtesy of responding to them.
John_Ru (rep: 5722) Nov 17, '23 at 6:42 am
Add to Discussion



Hello edge37 and welcome to the forum.

You did a great job of pasting your screen shots into an Excel file to clearly explain what you want to achieve. (You understood the forum rules that only Excel files can be posted)

I created a file using your screen shots for reference and simple macros to get the job done. There are two sheets: "Grades" and "Students". To see how it works go to the "Students" sheet and add one or more names in col "C"; then click the "Add New Students" button.

There are 3 steps to the code: 1) add the new name(s) to the master list on the "Students" sheet and resort the list; 2) add the new names to the "Grades" sheet and resort the list; 3) clear the names in col "C" of the "Students" sheet.

If this solves your problem, please mark my answer as Selected.

CHeers   :-)



@Willie - looks like this user won't be responding, sadly.
John_Ru (rep: 5722) Nov 15, '23 at 1:25 pm
I've been busy and I haven't had time to try this. Will get to you as soon as I can. Thank you
edge37 Nov 18, '23 at 6:48 am
Understood, thanks Michael. We're busy too but all to often a few days delay in reply (especially from new users) means we never get a response. Hope you come back to us again.
John_Ru (rep: 5722) Nov 18, '23 at 7:28 am
Add to Discussion

Hi Michael

There's no easy way to do what you want using just a formula so I too used VBA (Excel's built-in programming language) to do that.

My code relies on "events"- when something happens in Excel, it can trigger an event.(and there are several types).

I've recreated your sheets in the attached file. If you open it (with macros enabled - check the internet if you don't know how),and add a row to Sheet1, a new row will be added and "push down" the lower rows in Sheet2. If you correct a name (say you had a spellling error), that will be mirrored in Sheet2. If you delete a row containing a name (or several), you'll get a warning message to manually delete in Sheet2. 

How does it work? Firstly, Sheet 2 contains the data in a table, where the formula in cell A2 is:


so B2 shows 1 (its row number 2 minus 1 =1) and luckily the table just fills that down the column automatically and adjust as rows are added/ deleted lower down

Behind Sheet1 are two event procedures. The first is this (where the bits in red - following the apostrophe ' - don't do anything but are comments to tell you what happens in the next lines):

Private NumRws As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'record rows before the change is made
    NumRws = CLng(ActiveSheet.UsedRange.Rows.Count)
End Sub

The bit in bold "declares" a variable used to detect if any rows were deleted (in the first section of the code below). 

The main code is triggered when things change in Sheet1 only. Here's that code

Private Sub Worksheet_Change(ByVal Target As Range)
    ' warn if used rows were deleted
    If NumRws > ActiveSheet.UsedRange.Rows.Count Then
        MsgBox "Row(s)deleted- please delete same row(s) on Sheet 2"
        Exit Sub
    End If

    ' otherwise do nothing unless a single cell in A is changed
    If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Then Exit Sub

    ' stop this code re-triggering itself
    Application.EnableEvents = False

    ' get the new (or revised) name
    NewName = Target.Value

    If Target.Value = "" Then Exit Sub
    ' see if it's a fresh name by reversing the change
    ' then checking what it was
    If Target.Value = "" Then
        ' if it was nothing, add a fresh row at the same place in Sheet2
        Sheet2.Cells(Target.Row, 2).EntireRow.Insert
        ' and add value in B of the inserted row
        Sheet2.Cells(Target.Row, 2).Value = NewName

        ' if text changed, write the new name in that row
        Sheet2.Cells(Target.Row, 2) = NewName
    End If

    ' restore the changed value
    Target.Value = NewName
    ' allow events
    Application.EnableEvents = True
End Sub

Note that it assumes that you don't add rows to Sheet2 nanually but do delete those rows deleted on Sheet1 (to keep the names/ rows aligned between sheets). 

Hopefully this works well and automatically for you. If so, please remember to mark this Answer as Selected (or do the same for Willie's Answer if you prefer his).



I will try it and I'll discuss what happened. Thank you
edge37 Nov 18, '23 at 6:48 am
Thanks in advance.
John_Ru (rep: 5722) Nov 18, '23 at 7:28 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login