Macro that refreshes the workbook, copies the sheet to a network workbook and changes the name in the copied to workbook to todays date

[agentkramr]

i have a workbook that populates data from an Oracle database.
With my VBA i am trying to refresh the workbook , copy that page to a network workbook and then name the tab in the network workbook to todays date
i am using AI1 to hold my value of todays date using a custom format of (mmm Dd)

Sub CB_Copy()
'Refresh Workbook
Workbooks("My Workbook").RefreshAll
'Open a workbook

  'Open method requires full file path to be referenced.
  Workbooks.Open \\server\Shares\Folder\Sales\_Nightly Reports\2022 Nightly Reports\2022 2 Week Counts\2022 2 Week Count1.xlsx
 
  Workbooks("My Workbook.xlsm").Activate
 
  Sheets("2018 1 Hour Counts").Copy After:=Workbooks("2022 2 Week Count1.xlsx").Sheets(Workbooks("2022 2 Week Count1.xlsx").Sheets.Count)
    ActiveSheet.Range("A1:AC84").Copy
    ActiveSheet.Range("A1:AC84").PasteSpecial xlPasteValues
    'ActiveSheet.Shapes("Rectangle: Rounded Corners 1").Delete
    'ActiveSheet.Shapes("Rectangle: Rounded Corners 2").Delete
    Dim Val As String
    Val = Sheets("2018 1 Hour Counts").Range("AI1").Value
        ActiveSheet.Name = Val
    'Columns("AF:AI").Delete

    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

i get an error
"You typed an invalid name for a sheet or chart. Make sure that:

• The name that you type does not exceed 31 charaters
• The name does not contain any of the following characters: \ / ? * [ or ]
• You dod not leave the name blank

if it hit debug it goes to the line ActiveSheet.Namer = Val

any help would be greatly appreciated

 

[agentkramr]

anyone ?