Code for Multiple Named Ranges for Data Validation

Hello!

Dave Hawley helped another user out who wanted to concatenate two named ranges and use that new BigList as a range for data validation.

I have a named range of Shop personnel and then another for Office.

In this project, I want to be able to choose Shop + Office.

The named ranges are on the Lists sheet using this formula: =OFFSET(Lists!$A$2,0,0,COUNTIF(Lists!$A$2:$A$500,">"""),1)

How can I apply this code to my workbook? It has the BigList being created on Sheet4, but I am already using Sheets 1-21. Is there a way this code to be modified to create the BigList on a hidden Sheet1000 or something like that?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    With Sheet4 
            .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

I appreciate the help!

Respectfully,

Lost