Setting named ranges in a new workbook created from vba is making them local scope rather than global scope

Question

first time poster on here (and a novice coder!), so I hope I can explain myself well, as I've been struggling to find an answer to this when searching on the internet.

My company has a very simple timesheet system at the moment that uses Excel, with everyone sending a predefined workbook at the end of the week with a list of tasks that they have done, split by half days (so it could say Friday AM - Admin, Friday PM - analysis, for example). In order to make life simple for the person who has to use all this data for project costs, I have made a named range in the timesheets people use called DataRange that can then be called in VBA.

I've made a workbook that allows her to click a button and specify a directory for where all the timesheets she wants to import are, and after selecting all these, it loops through each one, finds the DataRange and pastes it into a new workbook, one after another, putting Timesheet_Name in the A column, and the date in the B column.

My next step is to allow for new tables to be created using this data. Within the loop a new named range is created that covers all the data pasted in from the timesheet workbook, and gives it the name of whatever is in the A column (so if the first timesheet in the loop was pasted in, the name would be Timesheet_JohnSmith, and the range would cover all of the data that came from the timesheet workbook.)

This all works great, however, I am having a problem in that when these new named ranges are created, they are set to a scope of the worksheet they're in, rather than the workbook as a whole. This means that if you want to use them in other worksheets (which is my future intention in order to create these into new tables), you have to refer to them as (for example, if they were on sheet 1 in the workbook) Sheet1!Timesheet_JohnSmith rather than just Timesheet_JohnSmith.

My code is below, it is the line that says: SummarySheet.Names.Add Name:=setUserName, RefersTo:=DestRange where the new named ranges are set. What I want to know is why it is setting it to the scope of just the worksheet it is in and if there is a simple way to change it to the scope of the whole workbook. Thank you!

Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim getUserFileName() As String
Dim setUserName As String
Dim NFile As Long
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range

' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(x1WBATWorksheet).Worksheets(1)
SummarySheet.SaveAs ("SummaryTest")
Workbooks("SummaryTest.xlsx").Activate
ActiveWorkbook.Sheets.Add

' Modify this folder path to point to the files you want to use.
FolderPath = Worksheets("Summary").Range("FilePath")

' Set the current directory to the the folder path.
ChDrive FolderPath
ChDir FolderPath

' Open the file dialog box and filter on Excel files, allowing multiple files
' to be selected.
SelectedFiles = Application.GetOpenFilename( _
    filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1

' Loop through the list of returned file names
For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
    ' Set FileName to be the current workbook file name to open.
    FileName = SelectedFiles(NFile)

    ' Open the current workbook.
    Set WorkBk = Workbooks.Open(FileName)

    ' Get the file name to be used for column A, removing the path and file extension
    getUserFileName = Split(FileName, "\")
    setUserName = getUserFileName(UBound(getUserFileName))
    setUserName = Replace(setUserName, ".xlsx", "")

    ' Set the cell in column A to be the file name.
    SummarySheet.Range("A" & NRow).Value = setUserName
    SummarySheet.Range("B" & NRow).Value = Date

    ' Set the source range to be A9 through C9.
    ' Modify this range for your workbooks. It can span multiple rows.
    Set SourceRange = WorkBk.Worksheets(1).Range("DataRange")

    ' Set the destination range to start at column B and be the same size as the source range.
    Set DestRange = SummarySheet.Range("C" & NRow)
    Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
       SourceRange.Columns.Count)

    ' Copy over the values from the source to the destination.
    DestRange.Value = SourceRange.Value

    'Create the name range in the new workbook to be used for future calculations
    SummarySheet.Activate
    SummarySheet.Names.Add Name:=setUserName, RefersTo:=DestRange

    ' Increase NRow so that we know where to copy data next.
    NRow = NRow + DestRange.Rows.Count

    ' Close the source workbook without saving changes.
    WorkBk.Close savechanges:=False
Next NFile

' Call AutoFit on the destination sheet so that all data is readable.
SummarySheet.Columns.AutoFit


End Sub

Answer 1

It's doing what you told it to - which was to add the Name to the sheet, not the workbook. You can just use:

DestRange.Name = setUserName