Obtain cell value of named range in copy/paste macro

[shotgun1]

Hi

I'm a little stuck on copying across a sheet from workbooks and renaming it. I want the Sheet name ("Cost comparison") once copied over to be that plus the cell value of a named range ("Size") which refers to a dropdown list selected in C$4.

When I run the code it copies across but when the sheet is renamed it shows the cell reference of the named range rather than value selected in C4.

What do I have to change to get the value?

Thanks

Function SheetExists(SheetName As String) As Boolean
     ' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function
Sub request()
    Dim Bk As Variant
    Dim n As Integer
     'the folder you want to search
    myDir = "\\Pricing Model\Model Development\Test\" 'must end on \
     
     'search folder for Excel (.xls) files
    Bk = Dir(myDir & "BC*.xl*")
     
     'if blank, no Excel files found in directory--end macro here
    If Bk = "" Then
        MsgBox "No Excel files found in folder."
        Exit Sub
    End If
     
     'loop through all .xls files found in folder
    Do While Bk <> ""
        If Bk <> ThisWorkbook.Name Then
             
             'open the workbook
            Set Bk = Workbooks.Open(FileName:=myDir & Bk)
             
             'check if the sheet with the name "Analysis Format" exists
            If SheetExists("Cost comparison") Then
                 
                 '**perform your actions on the workbook here**
                Sheets("Cost comparison").Select
                Cells.Select
                Selection.copy
                ThisWorkbook.Activate
                 
                 'adds a new sheet with name "Analysis Forma" and a number for the sheet
                Sheets.Add After:=Sheets(Sheets.Count)
                If Sheets.Count > 0 Then
                    n = Sheets.Count
                    Sheets(n).Name = "Cost comparison" & Bk.Names("CanSize").Value
                End If
                Cells.PasteSpecial (xlPasteAll)
               
                 
                 'end of the process
            Else
                With Bk
                    .Close 'close opened workbook
                End With
                Exit Sub
            End If
            Application.DisplayAlerts = False
            With Bk
                .Close 'close opened workbook
            End With
            Application.DisplayAlerts = True
        End If
        Bk = Dir()
    Loop 'loop to next workbook
End Sub

 

[shotgun1]

This is how it is currently displaying.

 

[chirayu]

Try using "Comparison " & Workbooks(BK).Worksheets(1).Range("Size")

 

[shotgun1]

That didn't work but solved it by referencing to a helper cell and making that equal to the named range