Compile Error: Variable Not Defined

[pawcoyote]

Howdy,

I have used this code before for other sheets in my workbook. I changed the name to be the new worksheet and adjusted the code throughout my workbook to include the new worksheet.

I get the Compile Error: Variable Not Defined when ever I point to the Set wsIBMass (new worksheet). I am not sure what I am missing.

Option Explicit


'Created on 11/27/17; Copy of information from MDS Equipment Detail Sheet to the IB Mass Creation Sheet


Sub BuildIBMass()
    Dim i As Long
    Dim iCol As Long
    
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set wsIBMass = Worksheets("IB Mass Creation")
    
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    Set headerIBMass = wsMOST.Rows(rowHeaderIBMass)
    
    Application.ScreenUpdating = False
    
    'get last Client Name row
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    
    'get last Client Name row
    iCol = GetColumnNumber("Client Name", headerMDS)
    
    With wsMDS
        rowDataEndMDS = .Cells(.Rows.Count, iCol).End(xlUp).row
    End With
    
    'added 11/27/2017
    If rowDataEndMDS < 7 Then
        MsgBox "No Data on MDS to copy to MOST"
        Exit Sub
    End If
    
    Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)


    Range(wsIBMass.Rows(rowDataStartIBMass), wsMOST.Rows(rowDataEndMDS)).EntireRow.Delete
    
    'Fields to be filled in when Copy from MDS to IB Mass Creation macro is enabled
    
    For i = rowDataStartMDS To rowDataEndMDS
        With wsIBMass.Rows(i - 2)
'            LUV(headerMOST, "Config Serial Number" & vbLf & "Manufacturer serial if no Configured serial", i - 2).Value = LUV(headerMDS, "Oracle Configuration SN", i).Value
            
            .Cells(2).Value = LUV(headerMDS, "Serial Number", i).Value
            .Cells(16).Value = LUV(headerMDS, "Ricoh Equipment ID", i).Value
            .Cells(21).Value = LUV(headerMDS, "Department Name (if required)", i).Value
            .Cells(22).Value = LUV(headerMDS, "Cost Center (if required)", i).Value
            .Cells(45).Value = LUV(headerMDS, "IP Address", i).Value
            .Cells(50).Value = LUV(headerMDS, "MAC Address", i).Value
            
            
        End With
    Next i


    Application.ScreenUpdating = True


    MsgBox "The data has been copied to the IB Mass Creation worksheet.  Please verify that the data has copied over properly!"
    


End Sub

 

[MARK858]

Please add the messagebox line as shown in posts 7 and 9 and answer the question asked previously below

so what does the messagebox line return?

 

[pawcoyote]

Please add the messagebox line as shown in posts 7 and 9 and answer the question asked previously below

Sorry I didn't grasp what you were saying yesterday. My mind was fried by the end of the day.

I added and it returned a Zero.

So I am assuming that it is seeing nothing in the headers... But there is Text in all the headers... I referenced the headers in my Globals Mod provided below. This is working for all the other sheets to reference but the new one for some reason..

Option Explicit' Updated 11/27/17
' You need to update this mod everytime you add a new sheet or if you modify where the Headers and Data start and end.
'
Public Const rowHeaderMDS As Long = 5       'MDS Sheet Header Start Row
Public Const rowDataStartMDS As Long = 7    'MDS Sheet Data Start Row


Public Const rowHeaderMOST As Long = 4      'MOST Sheet Header Start Row (Fixed Starting row 3/23/17)
Public Const rowDataStartMOST As Long = 5   'MOST Sheet Data Start Row  (Fixed Starting row 3/23/17)


Public Const rowHeaderPreMDS As Long = 5    'PreMDS Sheet Header Start Row
Public Const rowDataStartPreMDS As Long = 7 'PreMDS Sheet Data Start Row


Public Const rowHeaderNames As Long = 1     'Names Sheet Header Start Row
Public Const rowDataStartNames As Long = 2  'Names Sheet Data Start Row


Public Const rowHeaderIBMass As Long = 6     'IB Mass Creation Sheet Header Start Row
Public Const rowDataStartIBMass As Long = 7  'IB Mass Creation Sheet Data Start Row


Public headerMDS As Range, headerMOST As Range, headerPreMDS As Range, headerNames As Range, headerIBMass As Range 'Updated As Range Most 11/27/17


Public rowDataEndMDS As Long, rowDataEndMOST As Long, rowDataEndPreMDS As Long, rowDataEndNames As Long, rowDataEndIBMass As Long 'Updated As Long Most 11/27/17


Public rNames As Range
Public rStatesRegions As Range
Public wsMDS As Worksheet, wsMOST As Worksheet, wsPreMDS As Worksheet, wsNames As Worksheet


Public aRequiredIfClient As Variant
Public aNoDupsAllowed As Variant


Public rMDS As Range, rMOST As Range, rPreMDS As Range, rIBMass As Range

 

[pawcoyote]

I REM out the 'Option Explicit and everything worked fine. I am at a loss as to why I am getting these errors..

 

[RoryA]

You need to remove all the variable declarations that are duplicates of your Public constant names - like rowHeaderMDS for example.

 

[pawcoyote]

You need to remove all the variable declarations that are duplicates of your Public constant names - like rowHeaderMDS for example.

Thank all for all your help... I will be working on this. I really appreciate all your help...

 

[RoryA]

It looks to me as though you only needed to add one declaration:

Sub BuildIBMass()
    Dim i As Long
    Dim iCol As Long
    Dim wsIBMass As Worksheet

 

[pawcoyote]

I think I found the issue... I had Worksheets instead of Worksheet in two of the Modules...