Code for Multiple Named Ranges for Data Validation

Lost,

This code goes through each worksheet to see if there is a sheet named "Big Combined List". If it finds one, it uses that sheet. If it does not find one, it creates that sheet for future use and hides it:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    
    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("Shop").Copy .Range("A1")  'Copy 1st dynamic range and paste to A1 of Sheet4
            Sheets("Lists").Range("Office").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

Hope that helps,
~tigeravatar

TigerAvatar,

I put your code in a test workbook, but it doesn't seem to be doing anything. From what I can see, it isn't making the Big Combined List worksheet.

Is there something I am doing wrong?

Respectfully,

Lost

Lost,

The code only runs when the workbook performs a calcuation, so there'd have to be a cell that has a formula in it, otherwise the workbook is just changing, not calculcating. To run the code manually, change the sub name and put it in a standard module, then run it. Additionally, this line of code:

        wsBigList.Visible = xlSheetHidden

Makes the worksheet hidden. You can view it by right-clicking any worksheet tab and selecting Unhide.

Hope that helps,
~tigeravatar