Data transfer from one sheet to another multiple cells

[demetrius323]

Good day,
I am trying to create an auto-generated log on a new sheet that is based off of data entered from a previous sheet. What I am wanting is when a single cell on the master sheet is modified (in this case a choice is made from a drop down list), a copy of certain cells in that row would be transferred to the new sheet. Now here's the kicker, I also want it to create a new entry every time that the cell is changed with the newest being at the top of the list. An example is Sheet1 has multiple rows with each column containing certain data

A B C D E
1 Name ID# Location Time out Time In, etc.
2 Smith 1234 Chapel 9:30 10:45
3 Jones 9543 Hospital 7:15 11:30

Column C contains the drop down list and auto-generates the time in Column D. What I want is when the location is changed in Column C I want it to copy all the required data from the row, but not the entire row, onto Sheet2. Does anyone have any ideas on how to accomplish this? Please help.

 

[WarPigl3t]

Yes I have an idea. First of all I'd like to talk to you about the vba area. You might already know about the vba area that you enter through the Developer Tab. This is the most frequently used vba area. But there is another vba area. Right click on the worksheet name. A dialog menu appears. Click on "View Code". This is the vba area you will want to put your code in. The first thing you should do in this area is select "Change" or "On Chage" from the right drop down list. It will insert a worksheet on change event handler. So anytime something is changed on this worksheet, it will run whatever code you put in the event handler. Now we can begin coding. First you'll need a loop to look through all the rows in your master sheet and a second loop within the first loop to look at all the rows in your output sheet. What the loop is looking for is exact matches between the cells of both sheets. If it does not find one for a specific row, then obviously something in that row was changed in the master sheet and we need to insert a new row into the output sheet. So at this point we need to insert a new row at the top of the output sheet with the changed row. But before I continue, you stated that you didn't want it to output the entire row. You said only all the required data, but you didn't specify what the required data is. So just to get the ball rolling, I'll write some code that outputs the entire row for now. This code doesn't include the event handler that would automatically insert once you click "On Change" from the drop down menu in the vba area.

masterSht = "Master"
outputSht = "Output"
LRmaster = Sheets(masterSht).Range("C" & Rows.Count).End(xlUp).Row
LRoutput = Sheets(outputSht).Range("C" & Rows.Count).End(xlUp).Row
If LRoutput < 2 Then
    LRoutput = 2
End If
i = 2  'i = 1 if you don't have headers in masterSht
Do Until i > LRmaster
    Amaster = Sheets(masterSht).Range("A" & i).Value
    Bmaster = Sheets(masterSht).Range("B" & i).Value
    Cmaster = Sheets(masterSht).Range("C" & i).Value
    Dmaster = Sheets(masterSht).Range("D" & i).Value
    Emaster = Sheets(masterSht).Range("E" & i).Value
    myFound = FALSE
    ii = 2  'ii = 1 if you don't have headers in outputSht
    Do Until ii > LRoutput
        Aoutput = Sheets(outputSht).Range("A" & i).Value
        Boutput = Sheets(outputSht).Range("B" & i).Value
        Coutput = Sheets(outputSht).Range("C" & i).Value
        Doutput = Sheets(outputSht).Range("D" & i).Value
        Eoutput = Sheets(outputSht).Range("E" & i).Value
        If Amaster = Aoutput And _
            Bmaster = Boutput And _
            Cmaster = Coutput And _
            Dmaster = Doutput And _
            Emaster = Eoutput Then
                myFound = TRUE
        End If
        ii = ii + 1
    Loop
    If myFound = FALSE Then
        Sheets(outputSht).Rows(2).Insert
        Sheets(outputSht).Range("A2").Value = Amaster
        Sheets(outputSht).Range("B2").Value = Bmaster
        Sheets(outputSht).Range("C2").Value = Cmaster
        Sheets(outputSht).Range("D2").Value = Dmaster
        Sheets(outputSht).Range("E2").Value = Emaster
    End If
    i = i + 1
Loop

 

[WarPigl3t]

The code I wrote will output the data if you change anything from any cell. I know that's not what you wanted. You only want to output if the value in column C is changed. I don't think there is a way to do this with a drop down list. I can't think of a way. You could however use a combo box instead. It' pretty much the same thing as a drop down list. The good thing about a combo box is that you can create a line of code that says if the value of the combobox changes, then run the code. You'll have to google how to do that. I don't rememeber. It's something really easy though. Then once you have googled it and found the working if statement required to only run the code if the combobox value changes, you simply write a line of code that inserts a row just like I wrote above. Then insert the new values just like in my if statement. So you don't even need loops like I did in the above code if you use a combobox.

 

[demetrius323]

I am having difficulty. Not sure if it was entered correctly. I am not familiar with VBA. I keep getting errors.