Private Sub Worksheet_Change(ByVal Target As Range) not working on MAC

New Contributor

Hello all, 

 

The below code is not working on MAC Big Sur with the lastet Excel 365 with subscription 

Can someone telll me why is not and suggest any workaround?

Thank you

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    If Target.Address(0, 0) <> "D4" Then Exit Sub
    Application.ScreenUpdating = False

    Dim bottomC As Long
    bottomC = Sheets("DATA").Range("C" & Rows.Count).End(xlUp).Row
    Dim x As Long
    For x = bottomC To 2 Step -1
        If Sheets("DATA").Cells(x, 1) = Target Then
            Sheets("DATA").Cells(x, 3).Copy Cells(Target.Row + 4, Target.Row) ' VAT
           Sheets("DATA").Cells(x, 4).Copy Cells(Target.Row + 5, Target.Row) ' DOY
            Sheets("DATA").Cells(x, 5).Copy Cells(Target.Row + 2, Target.Row)  ' ADDRESS
            Sheets("DATA").Cells(x, 6).Copy Cells(Target.Row + 3, Target.Row)  ' 3 EQUALS WHAT TO COPY
           Sheets("DATA").Cells(x, 10).Copy Cells(Target.Row + 6, Target.Row)
           Sheets("DATA").Cells(x, 9).Copy Cells(Target.Row + 7, Target.Row)
           Sheets("DATA").Cells(x, 14).Copy Cells(Target.Row + 1, Target.Row)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

3 Replies

@makis1023 On a MacBook Air myself, though with Monterey 12.3.1, MS365 subscription:

 

Pasted the code into the code area in an empty worksheet. Entered 15 rows of some random data on a sheet called "DATA" in columns D through O. Then I entered something in D4 on the first sheet. Nothing happens. Then I clear cell D4 and after a few seconds cells D5:D11 contain the values from columns 14,5,6,3,4,10 and 9 (in that order) from the second row in the DATA range. If that is not what you  expect the code to do, you need to clarify your intentions.

 

 

Yes you are right. It works, what I am trying to do is that when start typing to bring results , something like bing etc. Can this be done? I can do it on windows with FILTER function but this doesnt work on MAC.

Thank you !

@makis1023 Not sure what you mean. If you can use FILTER on the PC version you should also be able to use it on the Mac, since you are an MS365 subscriber. Am not a VBA person myself but could, nevertheless, test your code. It became harder to understand what it was doing and what kind of workaround you were after. If you want to continue with VBA, I can't help you.