Copy and Paste a excel sheet , Value of Sheet Cell Data to be referenced in code

I am trying to copy and paste a sheet into a new workbook, the new workbook will not have any vba in it so I am creating a workbook then one sheet and the copied data is pasted into that sheet. To do this I have to reference the sheet the data is being copied from.

The sheet that the data is copied from will constantly change. Therefore I am referencing the sheet to be copied in Sheet1 Cell B1. Also the NAME of the destination sheet (New workbook and sheet) will also constantly change, these are assigned from Sheet1 Cells B2,C2 of the original sheet. All this is working fine,

See MR Excel post at the bottom, for more information.

The only part that I am stuck on is stated below and can not go any further. This is my code. I have left in the original code which work, this is commented out. I have also left in some of my attempts.

Object DOES Not support this property or method

wksh(CopySheet).UsedRange.Copy 'COPY THIS SHEET

This is the code

''Copy and Paste Sheet
        Application.SheetsInNewWorkbook = 1 'Number of Sheets in New Workbook
        Workbooks.Add 'Add sheet to new workbook
        With ThisWorkbook ' Now with this workbook

'' ########## Refering to WORKBOOK + SHEET from which the data is to be copied From to new Sheet ########        
Dim wkb As Excel.Workbook
Dim wksh As Excel.Worksheet
Dim CopySheet As Variant

    Set wkb = Excel.Workbooks("Test Copy Sheet3B.xlsm") ' USE THIS WORKBOOK
    Set wksh = wkb.Worksheets("Sheet1") 'USE THIS SHEET

    wksh.Range("B1") = CopySheet 'COPY THE SHEET NAMED IN THIS CELL E.G Sheet10
    wksh(CopySheet).UsedRange.Copy 'COPY THIS SHEET
    'wksh.Range("B1").UsedRange.Copy
    'wks.Sheets(Sheets("Sheet1").Range("B1").Value).Copy
    'ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues = CopySheet
     ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues 'PASTE IN NEW CREATED WORKBOOK SHEET
     
'' ############### Original Code ###############
''Copy and Paste Sheet
'   Application.SheetsInNewWorkbook = 1
'        Workbooks.Add
'    With ThisWorkbook
'        .Sheets("Sheet2").UsedRange.Copy 'Copy this sheet
'        ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
'        ActiveWorkbook.Sheets(1).Name = "Data Search" ' new sheet name     
'' ############### Original Code ###############

I have also posted this on Mr Excel Here There is a downloadable workbook and full code their as I have fixed most of the problems, Last few post would be best on page 2 of Mr Excel. This is the last bit I am stuck on.

Solution

Answer is below, big thanks to Luuk for pointing me in the right direction.

The fix

''Copy and Paste Sheet
        Application.SheetsInNewWorkbook = 1
        Workbooks.Add
        With ThisWorkbook
        
Dim wkb As Excel.Workbook
Dim wksh As Excel.Worksheet
Dim CopySheet As Variant

    Set wkb = Excel.Workbooks("Test Copy Sheet3B.xlsm") ' USE THIS WORKBOOK
    Set wksh = wkb.Worksheets("Sheet1") 'USE THIS SHEET
    CopySheet = wksh.Range("B1")
    .Sheets(CopySheet).UsedRange.Copy
    ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues 'PASTE IN NEW CREATED WORKBOOK SHEET

Full Code, Also posted on Mr Excel, see above post for link

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False
If Sheets(Sheets("Sheet1").Range("B1").Value).Range("A2").Value = "" Then
'ExportError.Show
MsgBox "Nothing to report"
Else
''Copy and Paste Sheet
        Application.SheetsInNewWorkbook = 1
        Workbooks.Add
        With ThisWorkbook
'' ########## Refering to WORKBOOK + SHEET from which the data is to be copied From to new Sheet ########
Dim wkb As Excel.Workbook
Dim wksh As Excel.Worksheet
Dim CopySheet As Variant

    Set wkb = Excel.Workbooks("Test Copy Sheet3B.xlsm") ' USE THIS WORKBOOK, name must match
    Set wksh = wkb.Worksheets("Sheet1") 'USE THIS SHEET name must match
    CopySheet = wksh.Range("B1")
    .Sheets(CopySheet).UsedRange.Copy
    ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues 'PASTE IN NEW CREATED WORKBOOK SHEET
     
'' Rename Tab On new Sheet
    Dim TabName As Variant
        TabName = ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
    ActiveWorkbook.Sheets(1).Name = TabName
''##################
 '' Format Header in new workbook
        ActiveWorkbook.Sheets(1).Columns("A:g").ColumnWidth = 25
        ActiveWorkbook.Sheets(1).Range("A1:g1").Font.Name = "Calibri"
        ActiveWorkbook.Sheets(1).Range("A1:g1").HorizontalAlignment = xlCenter
        ActiveWorkbook.Sheets(1).Range("A1:g1").Font.Color = vbWhite
        ActiveWorkbook.Sheets(1).Range("A1:g1").Interior.ColorIndex = 16 'Color Grey
' Create a Freeze panel on new sheet
   Dim wks As Worksheet
        For Each wks In Worksheets
            wks.Activate
                With Application.ActiveWindow
                .SplitColumn = 0
            .SplitRow = 1
        End With
        Application.ActiveWindow.FreezePanes = True
            If Not ActiveSheet.AutoFilterMode Then
                ActiveSheet.Range("A1").AutoFilter
            End If
        Next wks
'Fill all BLANK CELLS with Hyphen
    Dim r As Range, LastRow As Long
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        For Each r In ActiveWorkbook.Sheets(1).Range("A1:g" & LastRow)
        If r.Text = "" Then r.Value = "-"
    Next r
'Rename Sheet
    Dim SheetName As Variant
   '   Application.DisplayAlerts = False
        SheetName = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value
 'Save Sheet
    ActiveWorkbook.SaveAs Filename:=(SheetName) & Format(Now, " dd_mm_yyyy    HH_mm_ss") & ".xlsx", FileFormat:=51

   Application.ScreenUpdating = True
End With
End If
End Sub