Range object goes through "worksheet 1" instead of "worksheet 2"

Question

I'm having a problem of iterating through a range of items in a for loop:

How the procedure is supposed to work-

I start off on worksheet 1("Tracking Spreadsheet"), and depending on a change in worksheet 1 ( selecting "yes" in a cell) it would transfer you to worksheet 2("Deferred Submittals") and then iterate through a range of cells ( A1:A20 for example) from worksheet 2. The for loop would keep going until it reached a cell that was empty, and stop and then proceed to write into that cell.

How its working now-

I start off on worksheet 1, and depending on a change in worksheet 1 ( selecting "yes" in a cell) it would transfer you to worksheet 2 and then iterate through a range of cells ( A1:A20 for example) from worksheet 2. However, instead of iterating through worksheet 2's A1:A20, it would iterate through worksheet 1's A1:A20. After it found an empty cell in worksheet 1's A1:A20 range, it would then fill in the corresponding cell in Worksheet 2 ( If A5 is empty on worksheet 1, it would then fill in A5 on worksheet 2).

I was hoping to get help on having the range iterate through the active sheet at the time ( worksheet 2) instead of iterating through worksheet 1.

My code so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  ' Deferred Submittal, Column C = 3
  Dim cellTextDS As String
  Dim deferredArray As Range, deferredCell As Range, deferredRowEmpty As Long
  deferredRowEmpty = 1

  ' Deferred Submittal, Column C
  If Target.Column = 3 Then
    Row = Target.Row
    cellTextDS = ActiveSheet.Range("C" & Row).Text
    If cellTextDS = "Yes" Then
      Sheets(3).Activate

      Set deferredRange = Workbooks("BPS Tracking Sheet v6.xlsm").Worksheets("Deferred Submittals").Range("A1:A20") 
      For Each deferredCell In deferredRange
        Sheets(3).Activate
        MsgBox "inside deferredCell is " & deferredCell
        MsgBox " active sheet currently is " & name
        If IsEmpty(Range("A" & deferredRowEmpty).Value) = True Then Exit For

        MsgBox " deferredRowEmpty is " & deferredRowEmpty
        deferredRowEmpty = deferredRowEmpty + 1
      Next deferredCell

      MsgBox "Moving to 'Deferred Submittals' tab in order to input more information. row is " & deferredRowEmpty
      ActiveSheet.Range("A" & deferredRowEmpty).Value = "empty"

    End If
  End If
End Sub

Any help would be really appreciated!

p.s. the code is in the worksheet_change section of worksheet 1 if that makes any difference.

p.s.s. I've tried using this stackoverflow method for setting workbooks and worksheets, to no avail

p.s.s.s. posted the rest of my code for this portion. There is more code in this section, but it is literally just copy and paste of this section but for different columns. I've also edited what worksheet 1 and 2 are, but they are the different worksheet tabs in this workbook, specifically worksheet 1 = "Tracking Spreadsheet" and worksheet 2 = "Deferred Submittals"

please post the rest of your code , what is `Target.Column = 3` ? I suspect it belongs to `Worksheet_Change(ByVal Target As Range)` event. Also, what is the name of `WorkSheet 1` and `WorkSheet 2` ? — Shai Rado, Nov 22 '16 at 18:04
Change events can fire when the `Target` isn't the active sheet. Use `Target.Parent` or `Me` instead. — Comintern, Nov 22 '16 at 18:19
I added in more info Shai Rado, thanks for letting me know. Comintern, I will look into this then. So far .Activate is what I've been trying but it is not working — qqkju, Nov 22 '16 at 18:29

score 0 · Accepted Answer · answered Nov 22 '16 at 22:45

OK - I think this is the kind of thing you're looking for. When using the worksheet_change event there are a few things that should be done.

Disabling events means the macro can run without triggering further erroneous change calls and therefore having some error management is required.

I have left a commented line which may be of use so that any changes on multiple cells at one time won't trigger further changes and will just exit the sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub

On Error GoTo ExitSub
    Application.EnableEvents = False
    Dim wsSource As Worksheet: Set wsSource = Me
    Dim wsTarget As Worksheet: Set wsTarget = ThisWorkbook.Worksheets("Deferred Submittals")

    Dim NextEmpty As Range
    If wsSource.Cells(Target.Row, 3) = "Yes" Then
        Set NextEmpty = wsTarget.Range("A1:A20").Find("", LookIn:=xlValues)
        If Not NextEmpty Is Nothing Then
            Debug.Print NextEmpty.Address ' Function check
            NextEmpty.Value = Target.Row ' Or whatever you want the value to be
        End If
    End If

ExitSub:
    Application.EnableEvents = True
End Sub

Range object goes through "worksheet 1" instead of "worksheet 2"

1 Answers1