VBA Copy from depend drop downs

SlightlyClueless · Mar 3, 2022

Hi All,

I am looking for a little assistance with the VBA code in the workbook I have been working on. There is a userform with dependent dropdowns that pulls their values from "Master Sheet" in the workbook. The drop downs are functioning fine. However I have two roadblocks that I have now been able to get past. The first, The dropdowns allow the selection of "Category, Make, Model, and Add To". In the Master Sheet, "Category, Make, Model" Run from Columns A:C. Columns D:G have the equipment's, "Weight, Length, Width, Depth" information. I have not been able to have the information from columns A:F be copied based off the model selection. I have been trying have it paste in a test are for functionality with no luck. However once that would be functioning the "Add To" combo box selection in the user form would specify the range in the ECA worksheet to place that data. In the combo box selection, selecting "Keep" would place that information in range S3:Y16, "Remove" would be range S18:Y32, and "Final" would be range S35:Y47. Since numerous pieces of equipment would be added into each section when adding a piece of equipment it would place that entry in the next empty row of that range.

Any help or direction would be appreciated.

Dependent Drop Down Code

VBA Code:

Private Sub cmbAddTo_Change()

End Sub

Private Sub cmdCancel_Click()
frmUser.Hide
End Sub


Private Sub UserForm_Initialize()
  cmbCategory.RowSource = DynamicList(1, Null, 1, "Master Sheet", "Drop Down")
    
End Sub
Private Sub cmbCategory_Change()

    
    cmbMake.RowSource = DynamicList(1, cmbCategory.Value, 2, "Master Sheet", "Drop Down")
    
End Sub
Private Sub cmbMake_Change()

    
    cmbModel.RowSource = DynamicList(2, cmbMake.Value, 3, "Master Sheet", "Drop Down")
    
End Sub

Dynamic Dropdown Module Code

VBA Code:

  'Parameters - FilterColumn: Where we need to apply filter, FilterText : filter text value, DropDownColumn:Which column is for drop-down
 
  Function DynamicList(FilterColumn As Integer, FilterText As Variant, DropDownColumn As Integer, MasterSheet As String, DropDownSheet As String) As String
       
    'If FilterText is blank then exit from function
    If FilterText = "" Then Exit Function
    
    'On Error GoTo err_handler
        
    Dim shMaster As Worksheet
    Dim shDropDown As Worksheet
    
    Dim iMasterLastRow As Double
    Dim iMasterLastColumn As Double
    Dim iDropDownLastRow As Integer
    
       
    Set shMaster = ThisWorkbook.Sheets(MasterSheet)
    Set shDropDown = ThisWorkbook.Sheets(DropDownSheet)
    
    'removing existing filters, if any
    
    If shMaster.AutoFilterMode Then shMaster.AutoFilterMode = False
    
    
   'Applying filter in Master Data Sheet
   
    With shMaster
     
     iMasterLastRow = .Range("A" & .Rows.Count).End(xlUp).Row 'identify the last row number in Master Sheet
     
     iMasterLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column 'identify the last column number in Master Sheet
    
    'Applying Filter in Master Sheet
    .Range(.Cells(1, 1), .Cells(iMasterLastRow, iMasterLastColumn)).AutoFilter Field:=FilterColumn, Criteria1:=FilterText
   
    End With
   
                   
   'Removing the existing data from the drop-down column number
   
    With shDropDown
     
    .Range(.Cells(1, DropDownColumn), .Cells(Rows.Count, iMasterLastColumn)).ClearContents
      
    End With
    
    'Copying the filter drop down column basis on filter value and paste in Drop Down sheet
       
    With shMaster
      
     .Range(.Cells(1, DropDownColumn), .Cells(iMasterLastRow, DropDownColumn)).SpecialCells(xlCellTypeVisible).Copy shDropDown.Cells(1, DropDownColumn)
    
     .AutoFilterMode = False 'Removing filters
          
    End With
      
   
    With shDropDown
    
    'Removing duplicates from all the columns
     
    .Columns(DropDownColumn).RemoveDuplicates Columns:=1, Header:=xlYes
    
    'identifying last row number in drop-down sheet
     iDropDownLastRow = .Cells(Rows.Count, DropDownColumn).End(xlUp).Row
     
     'code to exclude column headers if no value in dropdown column in drop down sheet
     iDropDownLastRow = IIf(iDropDownLastRow < 2, 2, iDropDownLastRow) 'code to exclude header if only header there
        
     'Assigning the range in function name to return the value
     DynamicList = "'Drop Down'!" & .Range(.Cells(2, DropDownColumn), .Cells(iDropDownLastRow, DropDownColumn)).Address
    
    
    End With
   
    Exit Function
   
err_handler:
    
    MsgBox Err.Description
       
End Function

ECA Sheet

Rent Sheet.xlsm