Concatenate validation lists to same sheet.

**leaning** · 10-05-2011, 11:29 AM

Hello.

I have used this code before to combine two validation lists.

Hopefully, this will be easy. Now, instead of putting the combined list on a separate sheet, I'd like it in the Z column of the "Lists" sheet (the sheet the separate lists are on.). And instead of a button, some kind of way where if I add/remove entries to either of the separate lists ("VendCity" and "CustComb"), the combined list ("MyBigList") automatically grows and shrinks so it is always accurate.

Here's the code:

Private Sub CommandButton2_Click()
Dim wsBigList As Worksheet, ws As Worksheet
    Dim wsBigListFound As Boolean: wsBigListFound = False
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name = "Big Combined List" Then
            Set wsBigList = ws
            wsBigListFound = True
            Exit For
        End If
    Next ws
    
    If wsBigListFound = False Then
        Set wsBigList = Sheets.Add(after:=Sheets(Sheets.Count))
        wsBigList.Name = "Big Combined List"
        wsBigList.Visible = xlSheetHidden
    End If
    
    With wsBigList
            .Columns(1).Clear ' Clear MyBigList ready for copy/paste
            Sheets("Lists").Range("Plant").Copy .Range("A1")  'Copy 1st dynamic range and paste to A1 of Sheet4
            Sheets("Lists").Range("Shop").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1)  'Copy 2nd dynamic range and paste to 1 below last used row of Sheet4
            .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Name = "MyBigList" 'Name the new dynamic range
    End With
End Sub

Any ideas?

Respectfully,

Lost

**leaning** · 10-05-2011, 01:43 PM

All,

Almost there, but this is pasting formulas, not values:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
With Sheets("Lists")
            .Unprotect Password:="SECRET"
            .Columns(26).Clear ' Clear MyBigList ready for copy/paste
            .Range("VendCity").Copy .Range("Z1")  'Copy 1st dynamic range and paste to Z1 of Lists sheet
            .Range("CombCust").Copy .Cells(.Rows.Count, "Z").End(xlUp)(2, 1)  'Copy 2nd dynamic range and paste to 1 below last used row of Lists sheet.
            .Range("Z1", .Cells(.Rows.Count, "Z").End(xlUp)).name = "MyBigList" 'Name the new dynamic range
            .Protect Password:="SECRET"
    End With
Application.ScreenUpdating = True
End Sub

??

Respectfully,

Lost

**leaning** · 10-05-2011, 02:16 PM

All,

Getting closer.

I got the values of the first list to copy over, but can't figure out the syntax to get the second named range to pastespecial under the first.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
With Sheets("Lists")
            .Unprotect Password:="SECRET"
            .Columns(26).Clear ' Clear MyBigList ready for copy/paste
            
            .Range("VendCity").Copy
            .Range("Z1").PasteSpecial Paste:=xlPasteValues 'Copy 1st dynamic range and paste to A1 of Sheet4
            
            .Range("CombCust").Copy
            
            .Range(.Cells(.Rows.Count, "Z").End(xlUp)(2, 1)).PasteSpecial Paste:=xlPasteValues  'Copy 2nd dynamic range and paste to 1 below last used row of sheet Lists
            .Range("Z1", .Cells(.Rows.Count, "Z").End(xlUp)).name = "MyBigList" 'Name the new dynamic range
            .Protect Password:="SECRET"
    End With
Application.ScreenUpdating = True
End Sub

??

Lost

**leaning** · 10-05-2011, 03:36 PM

All,

I put this code behind the Lists sheet. Seems to work OK.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
With Sheets("Lists")
            .Unprotect Password:="SECRET"
            .Columns(26).Clear ' Clear MyBigList ready for copy/paste

            .Range("VendCity").Copy
            .Range("Z1").PasteSpecial xlPasteValues 'Copy 1st dynamic range and paste to A1 of Sheet4
            Application.CutCopyMode = False

            .Range("CombCust").Copy
            '.Range(.Cells(.Rows.Count, "Z").End(xlUp)(2, 1)).PasteSpecial Paste:=xlPasteValues  'Copy 2nd dynamic range and paste to 1 below last used row of sheet Lists
            .Range("Z1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues


            .Range("Z1", .Cells(.Rows.Count, "Z").End(xlUp)).name = "MyBigList" 'Name the new dynamic range
            .Protect Password:="SECRET"
    End With
Application.ScreenUpdating = True
End Sub

Respectfully,

Lost