Copy data to worksheet from drop down list

[scrungie]

I have a grade book with 17 worksheets 1 for each subject = each of the sheets are identical with the students names in col B and the data is entered manually in the corresponding cols along side the names.

What I would like is a vba solution to enter the data in a worksheet called 'datainput' = which would be identical in layout as the above subject sheets= and by the use of a drop down list with the names of the 17 subject sheets = copy the data in the 'datatinput' worksheet into the next available blank col in the worksheet selected from the drop down list=and with a command button erase the data in the 'datainput' worksheet ready to enter new data.

I hope the above is understandable,

Thanks in anticipation of a favourable reply

 

[mumps]

Hi scrungie. This can very likely be done with a Worksheet_Change event. It would be much easier to help if I could see a copy of your file. You could upload your file to a free website such as www.box.com. When you upload a file to the site, it will give you a link to the file. You can post the link on this forum and then the file can be downloaded for viewing.

 

[scrungie]

https://www.box.com/s/qllt2iv0rp5949rjltxa

Thanks for the interest

 

[mumps]

Hi scrungie. Place this code into the code module for worksheet "DataInput" (not into a regular module). The code assumes that cell D7 in your "DataInput" sheet is never blank.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim c As Range
    For Each c In Sheets(Target.Value).Range("D7:X7")
        If c = "" Then
            Sheets("DataInput").Range("D7:D36").Copy
            Sheets(Target.Value).Cells(Target.Row, c.Column).PasteSpecial xlPasteValues
            Sheets("DataInput").Range("D7:D36").ClearContents
            Application.CutCopyMode = False
            Exit For
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[scrungie]

mumps

I must be doing something wrong=is there the possibility that you send it to WWW.box.com so that I can see where I am going awry

Thanks

 

[mumps]

Hi scrungie. Click here to download the file. Let me know how it works out.

 

[mumps]

Hi again. I just realized that you have 4 semesters. The code I have written will work only for the first semester. I have to go out now but when I get back, I will revise the code so that it will work for all four semesters and I will post the revised version for you.

 

[scrungie]

Thanks

It works but the input data should be from D5:D36 to include date otherwise working good

 

[mumps]

Hi scrungie. Click here to download the updated file. You will notice that I have added a calendar for you to make the date entry easier. The calendar will appear when you click the date cell. Please let me know how it works out.

 

[scrungie]

Mumps

Working good as of yet, thanks. I hope that I don't have to harass you about any future ideas but the resolution you provided might make me change my mind. Again thanks alot