Copy workbook sheet to a network located workbook then rename page to todays date

[agentkramr]

i am trying to copy a worksheet from a workbook that connects to an oracle database and then paste that sheet in to a network located workbook then name that sheet to todpays date. i am getting an error and when i hit debug it gos to the activesheet shapes line.

i will be summoning this with a script as it will run nightly

any help is greatly appreciated.

Sub Test()
'Update Links
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
'Refresh Workbook
Workbooks("TWO WEEK COUNT 2022").RefreshAll
'Shut off Prompts
Application.DisplayAlerts = False
'Open a workbook

  'Open method requires full file path to be referenced.
  Workbooks.Open "\\serverpath\2 Week Count.xlsx"
  
  Workbooks("TWO WEEK COUNT 2022.xlsm").Activate
  
  Sheets("2018 1 Hour Counts").Copy After:=Workbooks("2 Week Count.xlsx").Sheets(Workbooks("2 Week Count.xlsx").Sheets.Count)
    ActiveSheet.Range("A1:AI80").Copy
    ActiveSheet.Range("A1:AI80").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").Format(Date"MMM DD").Value
        ActiveSheet.Name = Val
  
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

 

[agentkramr]

Anyone have any input ?

 

[agentkramr]

FYI it doesnt havce to use a selection method it can copyt the whole page, i just want it to keep the formatting and values