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

0

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.

Answer
Discuss

Discussion

Edge37

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

Answers

0

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.

Update Dec. 2/23 - see discussion below

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   :-)

Discuss

Discussion

@Willie - looks like this user won't be responding, sadly.
John_Ru (rep: 6232) 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: 6232) Nov 18, '23 at 7:28 am
Finally available to test the code.
1) When I add a new name and I click the button, the name is added but there is always an error, which I should end all the time, that says: "Run-time error ' 1004': The sort reference is not valid. Make sure that its within the data you want to sort, and the first Sort By box isn't the sarne or blank."
2) When the name is added in the Grades sheet, the grades of the other students below do not shift down along with the name, the new name just occupy.

thank you again for your support.  
edge37 Dec 2, '23 at 12:49 pm
@Willie- please note that I won't be adding to my Answer (as per my latest comment against that). Over to you (if you're still interested in helping this user). 
John_Ru (rep: 6232) Dec 2, '23 at 5:12 pm
@edge37
That run-time-error snuck past me and I'll explain.
I just downloaded the file I originally posted and tested it. When I originally tested the macros I did so individually. I then created the “parent” macro which called these “child” macros. That is what created the problem. When the “Update_Grades_Listing” macro ran the “Grades” sheet was not the active sheet – that is what generated the run-time-error. I added a line before the sorting line to activate the “Grades” sheet.   After this small change it ran without issue. I added three new names and they got added and everything moved down after sorting. The only difference between the list of names in your sample file and mine is that in my file the names are sorted alphabetically.   I have updated my original answer with the updated/repaired version.

Cheers   :-)
WillieD24 (rep: 557) Dec 2, '23 at 8:15 pm
Add to Discussion
0

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 spelling 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:

=ROW()-1

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
    Application.Undo
    ' 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

        Else
        ' 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 manually 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).

Discuss

Discussion

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: 6232) Nov 18, '23 at 7:28 am
Hello again, I finally have the time to test the codes you sent me and I did. It does exactly as I asked, unfortunately for my case it seems to work by adding a new row in Sheet 2 to apply the change. In my table, if I add a new row, some other things might not work properly. To better putting it: I need that, when a new name is written in Sheet 1, a new name is added in Sheet 2 but only shifting down a certain range of cells inside the table (that includes the names with their grades. In my table I have 25 spaces for 25 students, but not all of them would be used at the beginning (maybe only 22 students), so there will be about 3 empty spaces below, in this case (numbers 23, 24 and 25), in the table. the idea is that, when the change happens in Sheet 1, the names and grades of students below, in Sheet 2, shift one space down without inserting new rows, but occupying one of the spaces below that were empty before. Thank you very much
edge37 Dec 2, '23 at 12:33 pm
Michael. Sorry but I think I answered the original question you asked. After your long delay in replying I don't know if you added extra detail (e.g. 3 missing of 25) but I don't have the time or interest to go back and revise my Answer (especially since this sounds like a small problem encountered very occasionally). Perhaps Willie will revise his. 
John_Ru (rep: 6232) Dec 2, '23 at 5:09 pm
Add to Discussion


Answer the Question

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