MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy cell contents to same cell on different worksheet if contains

  Asked By: Keira    Date: Mar 14    Category: MS Office    Views: 1570

I am trying to finish up my rollbook.
I have a worksheet for attendance, another for conduct, another for
each subject.
I am using the same cell structure on each worksheet so that it can be
uniform, i,e. Monday Sept 1 in A1, Tues. in A2 etc.
I am trying to make the cell for conduct etc. automatically update to
"a" if the attendance shows "a" (for absent) for that day. I know I
can make each cell = the same cell on the attendance worksheet if it
has an "a", by using a formula, however it is very difficult to make
sure that the formula is in each cell (as there are columns in the
conduct sheet not used for conduct but for other things).
Is there a way to do this in VBA, that if the attendance worksheet
contains an "a", then that same cell (a1 etc) in another worksheet
will automatically be "a"?
(This would make it easier for me to do the averages for conduct,
because I can differentiate between absent, and present but 0 points).
Thank you so much for your help.



2 Answers Found

Answer #1    Answered By: Dep Tran     Answered On: Mar 14

Yes, relatively easy in VBA. For example, if you put this in Sheet1's code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If UCase(Target.Value) = "A" Then
Dim where As String: where = Target.AddressLocal(False, False)
Worksheets("sheet2").Range(where).Value = "A"
Worksheets("sheet3").Range(where).Value = "A"
End If
End If
End Sub

Then it will act on A's in column B and put them in the same place in sheets
2 and 3.

Answer #2    Answered By: Cesara Fernandez     Answered On: Mar 14

It worked perfectly, thank you so much!