Automatically Copy Range To Another Sheet Up On Change In Value In Adjacent Column

[william516]

Below is a code that I now have working, It takes the sheet "INITIATING DEVICES" and searches for column "G" if column "G" has a value of "Yes" then it will copy that row, only columns "A, B, C" to worksheet "MESSAGE CHANGES". The only problem is that it must be run via a Macro command or a virtual button and is not very good when you need it right away. I was wondering how I could make the code run automatically so that the second the "Yes" is selected from the dropdown list the user would be moved to the "MESSAGE CHANGES" page so that they could input the information. It would need to seek the next available row and it can not overwrite the data above it. The idea is that the inspector can record all the message changes needed for the site and have a record of it. The values need to be copied only nothing more. I learned the hard way that paste and pastespecial do two different things lol.

Thanks for any help.


Sub test()
Dim r As Range, filt As Range
Set ws = Sheets("INITIATING DEVICES") {I added this just to try and see if I could force the sheet because some of the forums are saying that could be the problem}
'Worksheets("INITIATING DEVICES").Activate
Set r = Range(Range("A6"), Range("G13324")) 'changae this if necessary =$A$7:$G$13224 {this is the range of the selected items columns A to G all the way down}

r.AutoFilter field:=Range("G7").Column, Criteria1:="Yes"
Set filt = r.SpecialCells(xlCellTypeVisible)
r.Columns("A:C").Copy
With Worksheets("MESSAGE CHANGES")

.Range("A7").PasteSpecial xlPasteValues
End With
ActiveSheet.AutoFilterMode = False
Application.CutCopyMode = False
End Sub
============================

"INITIATING DEVICES" is the page where the data will be found "MESSAGE CHANGES" is the page where the data will be copied if "Yes" is selected in column "G

[Excel Fox]

Did you try the Worksheet_Change event in VBA?

Paste this in the INITIATING DEVICES Sheet module


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
If Target.Column = 7 Then
For Each rng In Target
If UCase(rng.Value) = "YES" Then
Me.Cells(rng.Row, 1).Resize(1, 3).Copy Worksheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next rng
End If

End Sub

[bakerman]

Or without Copy.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 And UCase(Target.Value) = "YES" Then
Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value
End If

End Sub

[william516]

Thanks for the quick response, I did not get a chance to really look at anything until now. I copied the above code (the first selection) to the "INITIATING DEVICES" code. I'm assuming that means simply right click and view code and then paste it into that spot. That or open it through the VBA editor. I saved the file and then went to the "INITIATING DEVICES" page and clicked the dropdown selection box on column "G" and nothing happened.

I'm not sure if I'm doing something wrong or what. I will play around with the code you supplied and see if I can figure out what I'm missing. Can you think of anything that would cause the code not to work or that I'm missing?

Thanks
Bill

[bakerman]

I've put it in a simple file so you can see what happens.

[william516]

Ok I see how this is working, where could I add change or modify this so that once "Yes" is selected that the inspector or person using it would be transferred to the "MESSAGE CHANGES" page? The idea is that when the yes is selected that they can simply be transferred to the sheet so they can type in the new description of the device.

[bakerman]

Application.Goto Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp).Offset(, 3)
Add this line at the bottom of the code and you will be directed to the first empty cell to the right of the last copied data.

[william516]

Wow thanks that worked perfectly and I was able to modify it to work with a few other pages as well, thank you so much for the help. If its not asking to much could you break down the code



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 And UCase(Target.Value) = "YES" Then
Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value
End If

End Sub


I understand most of it but just wondering where your getting the values for what columns to display, so I can modify it to show a few more columns or change it so that it only shows a and c. Thanks again for the help.

[Excel Fox]

So 'Target' is the range that just had a value change in the sheet. This can be one or many cells depending on what changes or how it changes. Checking whether it is column 7, is just to ensure that the code only runs if the change of value happened in column G. If that is true, then we check whether the value in cell is 'Yes'. The UCASE is just to work around Excel's default text comparison mode, which is Binary mode. So basically, Yes is not equal to YES. To counter that, yes force the text to be compared as an UPPER CASE text.

Now's the real deal.



Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value Target.Row will give the row number of the cell that just got changed. And therefore, Cells(Target.Row,1) means some cell in Column A and in the corresponding row of the target cell. Let's say it's Range A2. Resize simply a method for resizing the range from an originating Range. So Resize(X,y) means that the existing range will be resized X rows and Y columns. So in the above case, the range A2 will now be A2:C2, ie, resized 0 rows, and 3 columns.

You know what

Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)
does. Adding a (1) to any cell reference would simply mean that we are referring to the same cell. However, (2) means we are referring to a range that is +1 offset than the same cell. So if you for example write Range("A1")(1).value, it is the same as Range("A1").Value. However, Range("A1")(2).value is actually equalent to Range("A2").Value, similarly Range("A1")(4) means Range("A4")

So now,

Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp) gives the last empty cell of that column, and the (2) makes it the range below that last empty cell. Now we resize this also to 3 columns, and we just write, for example,
Range("A2:C2").value = OtherSheet.Range("A5:C5").value

[bakerman]

Thank you Sam for clarifying the code.
One slight typo 'similarly Range("A1")(4) means Range("A4")'

One question on my behalf though, it's a neat trick to use the adding of numbers between brackets and i've figured out it does the same as offset without the offset (Range("A100").End(xlUp)(3, 2).Select gives me B3) but i've never seen it used but here so where is this coming from ? Is there somewhere i can read up about it or was this just trial and error ?

[Excel Fox]

bakerman, nice to hear that you like the approach. It has to be the trial and error. I can't remember reading about this anywhere. Though I tend to use this, and advocate this, I don't really vouch for it's sanctity. Yes, it does get the job done, and looks more crisp, but for the purist developer, it may be a bit confusing at times, unless of course, you know your stuff %d