Automatic table detection

[mariatti]

Hi mate,
I'm making a personalized expense spreadsheet, to put salaries, fixed costs and expenses on the credit card. So, each month, I create a copy of the spreadsheet within the same Excel file. The problem is that with each copy the tables change their name and the VBA code needs to keep up with this and I'm not able to do it. I managed to make a script that does exactly what I need, however, the code changes for all columns with the name VALOR.
Would I be able to add a way to detect the table name of a cell in this code and only make changes to that detected table?
If you have any optimizations for the code, I would also be grateful, I'm very new to VBA
Thanks guys

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim tableName As String

    On Error Resume Next
    tableName = Me.ListObjects(Target.ListObject.Name).Name
    On Error GoTo 0

    If tableName <> "" Then
        Set rng = Intersect(Target, Me.ListObjects(tableName).ListColumns("VALOR").DataBodyRange)

        If Not rng Is Nothing Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Value = "" Or cell.Value = 0 Or cell.Value = Empty Then 'Se o valor for apagado
                    cell.Offset(0, -1).Value = "[DATA]" ' Adiciona "[DATA]" na coluna "DATA"
                    cell.Value = "[VALOR]" ' Adiciona "[VALOR]" na coluna "VALOR"
                    cell.Offset(0, 1).Value = "[O QUE COMPROU]" ' Adiciona "[O QUE COMPROU]" na coluna "DESCRIÇÃO"
                Else
                    cell.Offset(0, -1).Value = Now ' Insere a data atual
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[mariatti]

You can hardcode the left part of the table name to check if the target cell is in the correct table as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim tableName As String

    On Error Resume Next
    tableName = Target.ListObject.Name
    On Error GoTo 0

    If Left(tableName, 8) = "GastosCC" Then
        Set rng = Intersect(Target, Me.ListObjects(tableName).ListColumns("VALOR").DataBodyRange)

        If Not rng Is Nothing Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Value = "" Or cell.Value = 0 Then 'Se o valor for apagado
                    cell.Offset(0, 1).Value = "[DATA]" ' Adiciona "[DATA]" na coluna "DATA"
                    cell.Value = "[VALOR]" ' Adiciona "[VALOR]" na coluna "VALOR"
                    cell.Offset(0, -1).Value = "[O QUE COMPROU]" ' Adiciona "[O QUE COMPROU]" na coluna "DESCRIÇÃO"
                Else
                    cell.Offset(0, 1).Value = Now ' Insere a data atual
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End If
End Sub

That's exactly what I'm referring to! you understood perfectly! This code solved my problem! thank you very much @Gergiboy!

Thank you very much to everyone who tried to help me in any way by dedicating their time!

 

[Georgiboy]

Good stuff, happy to help and I am glad you got it sorted.

 

[NoSparks]

Thanks for the explanation Georgiboy.
Definitely not the way I interpreted things, glad you picked up on it.