Copy table headers from current sheet to newly created sheet in excel vba

[Vincent88]

I use below code to duplicate a worksheet with a dynamic table, then clear all contents, embedded items and pictures of whole sheet but I need to copy all headers' value of the table back to new sheet but failed. Need help !
CODE
Sub BlankSheet()
If ActiveWorkbook Is ThisWorkbook Then
Dim ws As Worksheet
Set ws = ActiveSheet
Dim LastColumn As Long

LastColumn = ws.Range("A1").CurrentRegion.Columns.Count

On Error Resume Next
Application.DisplayAlerts = False
ws.Range(Cells(1, 1), Cells(1, LastColumn)).Copy
ws.Copy before:=ActiveSheet
szToday = Format(Date, "d mmm yyyy")
ActiveSheet.name = szToday
Application.DisplayAlerts = True

'Clear All Contents
ActiveSheet.Cells.ClearContents
ActiveSheet.OLEObjects.Visible = True
ActiveSheet.OLEObjects.Delete
ActiveSheet.Pictures.Visible = True
ActiveSheet.Pictures.Delete
ActiveSheet.Range(Cells(1, 1)).Paste

End If
End Sub

 

[Vincent88]

Hi Vincent88,

it may have been to obvous for me but I´d like to ask if you considered using a template sheet set up as you would need it and use that for the copy and rename instead of copying an existing sheet, deleting items and rebuilding from scratch.

Ciao,
Holger

Hi HaHoBe,
Appreciate your suggestion. My file is using daily and we need to create a worksheet for that day.
I am not sure if a template will be easier in my case. Welcome your professional advice.
Thank you in advance.

 

[HaHoBe]

Hi Vincent88,

does the code work like you want it to or is there still any problem from your side? Like I stated I couldn´t find any errors when using your codes on my test workbook. Please clarify.

Ciao,
Holger

 

[Vincent88]

Hi Vincent88,

does the code work like you want it to or is there still any problem from your side? Like I stated I couldn´t find any errors when using your codes on my test workbook. Please clarify.

Ciao,
Holger

Hi HaHoBe,
Your code works fine and it is what I want. The problem should be on my code. I just want the code to check if the sheet name of the day is already existed then no need to create new worksheet and a message box will pop up and no new sheet to create.