VBA code for adding sheet doesn't work with Workbook function

[olorin_117]

Hello

I have this code below that copies the entire sheet, renames the new one with the current date and erases the contents of certain range that works perfectly if I just run the macro. Now i would like that to work with the Workbook_NewSheet function in the workbook section of vba but all it does is. a) creates a new blank sheet with the name "Sheet2", creates another sheet with todays's date but doesn't copy anything, just a blank sheet. Am I doing something wrong?

Public Sub CopyRenameSheet1()

    ActiveSheet.Copy Before:=ActiveSheet
    Range("B9:H" & Range("B9").End(xlDown).Row).ClearContents
    Range("B5").ClearContents
    ActiveSheet.Name = Format(Date, "DD.MM.YYYY")
    
End Sub

P.S. Pretty newbie with VBA

 

[Joe4]

Welcome to the Board!

I do not know that you are going to be able to do that, unless the name of the sheet you want to copy from is static (you can code the name of the sheet into the code because it will never change).
That is because it appears that the "Workbook_NewSheet" event procedure has no recollection of what sheet you were on when you went to create the new sheet. Its only reference is to the new sheet.

Unless that sheet you are copying from is always the same, I would probably stick with what you have, and either assign the VBA code to a button or a keyboard shortcut to make it easier to run.

 

[Jaafar Tribak]

The Workbook_NewSheet event doesn't fire when copying a worksheet.

 

[olorin_117]

I want to answer my question since I kinda solved it but it has a problem that I'll explain later

I used the code below in the wokrbook vba object code in order to create a new worksheet, give it specific name and format and then copy the contents from a template sheet in the same workbook as suggested by user @Joe4.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Sh.Move Before:=ThisWorkbook.Sheets(1)
Sh.Name = Format(Date, "DD.MM.YYYY")
Worksheets("template").Range("A1:X25").Copy

ActiveSheet.Range("a1").pastespecial Paste:=xlPasteAll
ActiveSheet.Range("a1").pastespecial Paste:=xlPasteColumnWidths
ActiveSheet.Range("B9:H" & Range("B9").End(xlDown).Row).ClearContents
ActiveSheet.Range("B5").ClearContents

Sheets(Sheets.Count - 1).Range("N25:R25").Copy 'there are certain data that, every time a new sheet is created, i want from the last production sheet
ActiveSheet.Select
ActiveSheet.Range("N25:R25").pastespecial Paste:=xlPasteValues

End Sub

But what user @Jaafar Tribak told me, and by using the above code made me understand that this is working fine ONLY if you don't have any code in the worksheet vba object part, which in my case I have. By using the above code, doesn't copy the code that I have in the worksheet object, which I need in every sheet. I know this may be obvious knowledge for more experienced users but I am kinda new.