Get value of B7:b and E7:E (text value only) displayed on J7:j on ONE worksheet

[William516]

Ok I have the first part working with one major problem. When I run the code below it will display a text value for every page in the workbook. I only need it to display a value in "J" on the "initiating devices" page. The other pages have different column headings. Also it only will work when the macro is run manually, I would like to know if it could be run as the user enters the status of a devices, this will update values on another page in real time. Here is the code I have been using so far with limited success.

Sub PrepareJ()
    Dim lng As Long
    lng = Cells(Rows.Count, "B").End(xlUp).Row
    Range("J7:J" & lng).Value = Evaluate("=B7:B" & lng & "&E7:E" & lng)
    
End Sub

 

[sijpie]

I don't see how your code will access any sheets other than the one on which it is run, ie If you run the macro while Sheet1 is active, then it will only put the values of B&E in J for this one sheet.

Anyway on your second question, you can put a macro in the Sheet Module that checks for any changes to a certain column or range, and then updates something.

So if you only want to update column J on Sheet "initiating devices" when a user makes a change in column A of the same sheet, then do the following:

open the macro editor (Alt-F11)
in the top left panel find the Sheet "initiating devices" and double click it (You can also in Excel right click on the Tab of the sheet and select 'View Code')

You now have opened the Module for this sheet. This is not a general module, and you should only put code here that has to do with changes to this sheet. These macros will not be visible in the list of macros.

Paste the following code here
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns(1)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">' the user has changed something in Column A of this sheet</SPAN><br>            <SPAN style="color:#00007F">Dim</SPAN> lng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>            lng = Cells(Rows.Count, "B").End(xlUp).Row<br>            Range("J7:J" & lng).Value = Evaluate("=B7:B" & lng & "&E7:E" & lng)<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Anytime you now make a change to a cell in Column A the macro will run.