Run-Time Error"9" Subscript Out of range

Hello Excel World.

I have a problem. I don't know if you can help me out here.

I have a macros name "Update Sheets" on one sheet #1 "PMI"

When I click the Macros to copy and paste each (country, date and value) into each work sheet database.

I get an error message that states, "Run-Time Error"9" Subscript Out of range".

How can I fix this?

Please view documents below.

Thanks in advance

Corrie

Hello

A proposal to consider.

Regards.

Public Sub UpdateDataInSheets()
Dim loData As ListObject, lo As ListObject
Dim Cell As Range, r As Range
Dim n, arr(1)
Dim dt As Double
Dim nm As String
    Set loData = Range("PMI_17").ListObject
    If loData.DataBodyRange Is Nothing = False Then
        For Each Cell In loData.ListColumns(1).DataBodyRange
            dt = Cell.Offset(, 1).Value2
            nm = VBA.Replace(Cell.Value2, " ", "")
            On Error Resume Next
            Set lo = Range(nm).ListObject
            On Error GoTo 0
            If Not lo Is Nothing Then
                n = Application.Match(dt, lo.ListColumns(1).DataBodyRange, 0)
                If IsError(n) Then
                    arr(0) = Cell.Offset(, 1).Value2
                    arr(1) = Cell.Offset(, 2).Value2
                    With lo
                        'If .InsertRowRange Is Nothing Then
                            Set r = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
                        'Else
                            'Set r = .InsertRowRange.Cells(1)
                        'End If
                    End With
                    r.Resize(, 2).Value2 = arr
                End If
            End If
        Next Cell
    End If
End Sub

I really don't know what to say.

Jean-Eric. I really appreciate your continual support, always.

You demonstrates a character of a true friend. One that shows your personality with out complaints, dependable, reliable and supportive.

It seems like procrastination is your worst enemy. and helping others are your best friend.

Thank you again Jean-Eric. for your commitment and passion for excel.

Hello Everyone.

If you are open, I would like your support once more.

I have a similar problem on my first plead above on macro error message. Where Jean-Eric have help me so wonderfully.

I have tried all the codes that I have received in the pass, none of them have worked.

Please view document on the link below, if you are opened in helping me out. The file is large, was not able to paste same on this website.

Thanks in advance.

Good morning,

Hope all is well. I don't know if you all can help me. I am stuck and I don't know what to do?

I have one more document that is challenging to fix. It was working before. I don't know what is the reason of the problem in Macros.

How do you want me to proceed?

Thanks in advance

... sp=sharing

Hello,

1. Copy the procedure named TableNames in a new module in the copy of your file.

2 . Run the procedure.

3. See the name manager (Ctrl + F3)

4. Copy the procedure named UpdateDataInSheets in a module 1 (delete previous version).

5. Run the procedure.

Don't forget to check the results !...

Regards.

Option Explicit
'Rename all tables with smart names
Public Sub TableNames()
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim txt As String
    Set wb = ThisWorkbook
        For Each ws In wb.Worksheets
            Select Case ws.Name
                'sheets to ignore
                Case "B_Confin":
                Case Else:
                'sheets to be processed
                txt = Replace(ws.Name, " ", vbNullString)
                Set lo = ws.Cells(1).ListObject
                lo.DisplayName = txt
                Set lo = ws.Cells(6).ListObject
                lo.DisplayName = txt & "2"
            End Select
        Next ws
End Sub

Public Sub UpdateDataInSheets()
Dim loData As ListObject, lo As ListObject
Dim Cell As Range, r As Range
Dim n, arr(1)
Dim dt As Double
Dim nm As String
    Set loData = Range("Business_Confidence").ListObject
    If loData.DataBodyRange Is Nothing = False Then
        For Each Cell In loData.ListColumns(1).DataBodyRange
            dt = Cell.Offset(, 1).Value
            nm = VBA.Replace(Cell.Value, " ", "")
            On Error Resume Next
            Set lo = Range(nm).ListObject
            On Error GoTo 0
            If Not lo Is Nothing Then
                n = Application.Match(dt, lo.ListColumns(2).DataBodyRange, 0)
                If IsError(n) Then
                    arr(0) = Cell.Offset(, 1).Value
                    arr(1) = Cell.Offset(, 2).Value
                    With lo
                        'If .InsertRowRange Is Nothing Then
                            Set r = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
                        'Else
                            'Set r = .InsertRowRange.Cells(1)
                        'End If
                    End With
                    r.Offset(, 1).Resize(, 2).Value = arr
                End If
            End If
        Next Cell
    End If
End Sub

I've taken your suggestions.

I've renamed all my tables for better understanding.

And I've added macros, and it WORKS!!!

I'm new to Excel and I'm starting to discover that this program is a very powerful software that I will enjoy.

Thanks again.