(Solved) – VBProject.VBComponents(wsTarget.CodeName).Name = code fail EVERY OTHER time. Why?

(solved)-–-vbprojectvbcomponents(wstargetcodename)name-=-code-fail-every-other-time.-why?

I have a small app containing about 20 subs which runs perfectly… Every other time.

It fails, in the sub ImportData, the first time I add a code name to a newly created sheet. on the following line:
ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "Customers"

The code below contains three subs which which can recreate the issue. Important note: I can run the sub ImportData as many times in a row without any issues but if I call the sub “Sync()” twice in a row it will fail on the second attempt but works fine on the third attempt and so on (Maybe it doesn’t like odd numbers..)

Any ideas as to why this is happening would be greatly appreciated.

FYT: I am running this code in Excel for Mac

Public LastRow As Long
Private wks As Worksheet

Sub Sync()
     Call ImportData
     Call SyncBoth
End Sub

Public Sub ImportData()
'                                                      
'      1. ImportData will allow user to select file to import data from
'      2. Copy both the Customers and Vendors data to their respective sheets
'                                                      
     Dim wsSource As Worksheet
     Dim wsTarget As Worksheet
     Dim LastRow As Long
     Dim MaxDate As Date
     Dim ShCount As Integer
     Dim SourceFile As String
          SourceFile = "https://stackoverflow.com/Users/phild/Documents/RTPro/Customer and Vendor Raw Sync.xlsm"
     Dim SourceWb As Workbook
          Set SourceWb = Workbooks.Open(SourceFile)
     Dim TargetWb As Workbook
          Set TargetWb = ThisWorkbook
     Dim sheet As Worksheet

     For ShCount = 1 To 2
          Select Case ShCount
          Case 1
               Set wsSource = SourceWb.Worksheets("Sheet1")                 'Set Worksheet to copy data from
               ThisWorkbook.Sheets("Customers").Delete                           'Delete old Customer worksheet in this worksheet

               Set sheet = ThisWorkbook.Sheets.Add                                'Create New Customer woeksheet in this woekbook
               sheet.Name = "Customers"                                              'Name new Customer worksheet
               Set wsTarget = ThisWorkbook.Worksheets("Customers")         'Set Customers ws as the target ws
               Debug.Assert ThisWorkbook.VBProject.Name <> vbNullString              '<--Force the VBE to exist. Don't pollute the Immediate window
               ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "Customers"       'Give Customers a Code name
                              'THE LINE OF CODE ABOVE RESULTS IN A Runtime error '32813"
                              ' Method 'Name' of object ' VBComponent' failed
                              ' EVERY OTHER TIME I RUN THE SUB Sync()

        Case 2
               Set wsSource = SourceWb.Worksheets("Sheet3")                'Set Worksheet to copy data from
               ThisWorkbook.Sheets("Vendors").Delete                            'Delete old Vendors worksheet in this worksheet

               Set sheet = ThisWorkbook.Sheets.Add                               'Create New Vendor worksheet in this woekbook
               sheet.Name = "Vendors"                                                'Name new Vendor worksheet
               Set wsTarget = ThisWorkbook.Worksheets("Vendors")           'Set Customers ws as the target ws
               Debug.Assert ThisWorkbook.VBProject.Name <> vbNullString          '<--Force the VBE to exist. Don't pollute the Immediate window  '
               ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "Vendors"     'Give Vendors a Code name
        End Select

        Call CleanTarget(wsTarget)

          LastRow = Find_LastRow(wsSource)
          wsSource.Range("A1:Z" & LastRow).Copy Destination:=wsTarget.Range("A1")

          Next ShCount

     SourceWb.Close
End Sub

Sub SyncBoth()
     Dim ShCount As Integer

     For ShCount = 1 To 2
          Select Case ShCount
               Case 1
                    Set wks = Customers                 'Work in sheet("Customers")
                    LastRow = Find_LastRow(wks)      'Last row of "Customers"

               Case 2
                    Set wks = Vendors                     'Work in sheet("Vendors")
                    LastRow = Find_LastRow(wks)       'Last row of "Vendors"
          End Select

          Debug.Print wks.Name

     Next ShCount

     'Normally I have about 10 subs here that are called sequentially. But this is enough the cause the errorw

End Sub```


Leave a Reply

Your email address will not be published. Required fields are marked *