Saturday, October 21, 2017

Dropdown with values from different sheet and showing the first element of the list in the dropdown cell using vba

Sub autofill1()
'
' Macro1 Macro
'

'
'For C column autifilling

Sheets("Service Catalogue").Select
 Dim inputRange As Range

    Range("C6").Select
   
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Services"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
       
    End With
    Dim namedrange As Range
    Set namedrange = Range("Services")
    ActiveCell.Value = (namedrange.cells(1, 1).Value)
   
   
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
   
     Dim dd As DropDown

   
  'For D column autofilling
 
    Sheets("Service Catalogue").Select

    Range("D6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(C6)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
  


   
 Set inputRange = Evaluate(Range("D6").Validation.Formula1)
  Range("D6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
   'For E column autofilling
  
    Sheets("Service Catalogue").Select

    Range("E6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(D6)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
    Set inputRange = Evaluate(Range("E6").Validation.Formula1)
  Range("E6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
   'For F column autofilling
  
   Sheets("Service Catalogue").Select

    Range("F6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(E6)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
  
    Set inputRange = Evaluate(Range("F6").Validation.Formula1)
  Range("F6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
       
         'For G column autofilling
  
   Sheets("Service Catalogue").Select

    Range("G6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Selection"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
  
    Set inputRange = Evaluate(Range("G6").Validation.Formula1)
  Range("G6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
       
       
          
         'For H column autofilling
  
   Sheets("Service Catalogue").Select

    Range("H6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(G6)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
  
    Set inputRange = Evaluate(Range("H6").Validation.Formula1)
  Range("H6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
       
          'For I column autofilling
  
   Sheets("Service Catalogue").Select

    Range("I6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Infra"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
  
    Set inputRange = Evaluate(Range("I6").Validation.Formula1)
  Range("I6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
       
         'For I column autofilling
  
   Sheets("Service Catalogue").Select

    Range("J6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=SelectionTS"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
  
  
    Set inputRange = Evaluate(Range("J6").Validation.Formula1)
  Range("J6").Select
  ActiveCell.Value = inputRange(1)
  Selection.AutoFill Destination:=ActiveCell.Range("A1:A18"), Type:= _
        xlFillDefault
       
       
       
End Sub

No comments:

Post a Comment