Excel VBA: Transfer template file to Activeworkbook

Could you please clarify, which sheet of which book will be copied to which book? For now I made something generic.
Second question, what if the workbook you want to open is already opened? Should it discard any unsaved changes?
If you want sheet name HHMM then, replace format code to "YYYYMMDDHHnn"

Sub Hakan_Generic()

    Const WorkbookToCheckIfExisting As String = "check.xlsx"
    Const WorkbookToOpen As String = "open.xlsx"
    Const WorkbookToCopySheetFrom As String = "copyfrom.xlsx"
    Const WorkbookToCopySheetTo As String = "copyto.xlsx"
    Const WorkbookToDeleteSheetsFrom As String = "deletefrom.xlsx"
    Const OriginalNameOfTheSheet As String = "original"
    Const SheetNameFormat as String = "YYYYMMDDnnss"
    Const RemoveSheetsQuestion as String = "Do you want to remove the various sheets except sheet already transferred"
    Const FileDoesntExistsAlert as String = "File: File.name doesn’t exist"
    
    Dim NewNameOfTheSheet As String
    Dim iws As Worksheet

    NewNameOfTheSheet = Format(Now(), SheetNameFormat)
    
    If Dir(WorkbookToCheckIfExisting) <> "" Then
        Workbooks.Open WorkbookToOpen, ReadOnly:=True
        Workbooks(WorkbookToCopySheetFrom).Sheets(OriginalNameOfTheSheet).Name = NewNameOfTheSheet
        Workbooks(WorkbookToCopySheetFrom).Sheets(NewNameOfTheSheet).Copy After:=Workbooks(WorkbookToCopySheetTo).Sheets(1)
        
        If vbYes = MsgBox(RemoveSheetsQuestion, vbYesNo) Then
            Application.DisplayAlerts = False
            For Each iws In Workbooks(WorkbookToDeleteSheetsFrom).Worksheets
                If iws.Name <> NewNameOfTheSheet Then iws.Delete
            Next iws
            Application.DisplayAlerts = True
        End If

    Else
        MsgBox FileDoesntExistsAlert
    End If

End Sub

Select allOpen in new window

Thank you very much for your feedback.

The idea is to add the procedure in my personal.xlsb so when I open a new blank file I call the procedure and 1- sheet is inserted to active workbook.
The final aim is to use in a regular basis this 1- sheet.
I was also thinking to save .xlst (template) file however it doesn’t work when it comes to table this is why I prefer to add it to my personal.xlsb

Why not add the worksheet to your PERSONAL.xlsb, then you can copy it to the active workbook?

Thank you for the feedbacks.
I unhide personal.xsb file and transfer reference sheet which contains the table and it works.
The only problem that I have with this is that previously I managed personal.xlsb in hidden mode.
Additionally I need to copy manually reference sheet to blank workbook.
Can we complete this with a procedure located in personal.xlsb to copy 1.sheet to active workbook?

Another approach will be to save the table template as a default template in a blank workbook and then save it in a xlst file located in XLSTART to reuse across future workbooks.

Do you know if there is a way to save table already formatted as default table template. I know that I can use the option create new template but I need to start from the scratch when it comes to the format.

Let me know what do  you think.

If you will follow Roy's approach, you may put this code to your personal macro workbook that contains the template sheet.
I also changed reference to Format function.

Sub Hakan_Generic()
    Const OriginalNameOfTheSheet As String = "original"
    Const SheetNameFormat as String = "YYYYMMDDnnss"

    Dim NewNameOfTheSheet As String
    NewNameOfTheSheet = VBA.Format(Now(), SheetNameFormat)

    ThisWorkbook.Sheets(OriginalNameOfTheSheet).Copy After:=ActiveWorkbook.Sheets(1)
    ActiveWorkbook.Sheets(OriginalNameOfTheSheet).Name = NewNameOfTheSheet
End Sub

Select allOpen in new window

Simplest form.

Sub Hakan_Simple()
    ThisWorkbook.Sheets("original").Copy After:=ActiveWorkbook.Sheets(1)
    ActiveWorkbook.Sheets("original").Name = VBA.Format(Now(), "YYYYMMDDnnss")
End Sub

Select allOpen in new window

Dear Roy,
Not long ago I updated my OS and installed all Office again. In my personal macro book, some object libraries mixed up and Format function became undefined or had multiple definitions. I updated the code just to make sure that it finds the correct Format function.

For Format Function under VBA library, N is reserved for Minutes as described in VBA Language Reference. M character means Month or Minutes conditionally.

Thank you for your feedbacks.
@Roy: when I hide Personal.xlsb, sheet which contains table template is not available to copy to blank workbook.


Due to this I need a procedure to copy it to blank workbook.
@Hakan: In your simplest form proposed previously, could you please add a control. If source sheet doesn't exist in source workbook exit sub with message "Unable to proceed as source sheet doesn't exist". Additionally, even if source workbook which contains source worksheet is hidden, the procedure should work?

Thank you.

Personnal.xlsb is not a sheet. It is a complete workbook. Normally, this workbook doesn't exists. It will be created when you record any macro to your Personal Macro Workbook.
Once created, you may see it under VBA IDE. All open workbooks are always visible in VBA IDE.
You may unhide it from View tab.

Have you followed my instructions to copy the template sheet to the PERSONAL workbook when it is visible? Then add the code that I provided in standard module, save PERSONAL.xlsb and then hide it again.

Thank you very much for those proposal.
@Roy: you are right if I hide personal.xlsb and I launch the procedure it works.
I would like to know if it is possible to add in the procedure a if condition.
Once the sheet is added, MsgBox: "Do you want to keep just activesheet and remove the other one's".
If yes remove the other sheets else exit sub with the following message: "operation has been cancelled".

@Hakan: you proposal also works. Could you please also help me with MsgBox: "Do you want to keep just activesheet and remove the other one's". If yes remove the other sheets else exit sub with the following message: "operation has been cancelled".

Thank you very much for your help.

Thank you for this feedback.
For my knowledge, should thisworkbook be declared as variable and activesheet set?
Set Ws = application.activesheet
Sometimes I see that experts declare and set and sometimes no. What is the best to proceed?

In that example I would not really think it's necessary, depends what you are doing. If you are going to make frequent calls to it then declare it as a WorkSheet.

Some events may change the ActiveSheet while the code is running. I think this may cause some problems. If I will run a long procedure on the ActiveSheet, I usually assign the ActiveSheet to a variable at the start of the code, so I am sure that I am always referring the same sheet. Even when the sheet name changes while my code is running, it doesn't affect my references.

ThisWorkbook references the workbook that your code is in. It's defined when the workbook is created.

Very clear. Thank you very much for your help.

Some events may change the ActiveSheet while the code is running. I think this may cause some problems. If I will run a long procedure on the ActiveSheet, I usually assign the ActiveSheet to a variable at the start of the code, so I am sure that I am always referring the same sheet. Even when the sheet name changes while my code is running, it doesn't affect my references.

It is generally unnecessary to Activate or Select sheets in VBA code which would change the ActiveSheet. Also, changing the sheet name will not affect the ActiveSheet during the code.

LD16, pleased to help

Dear Roy,

I was explaining why we sometimes assign the sheets to a variable.
If I am referring a sheet with it's name each time, I would not access this sheet if it's name changes.
Worksheets("Sheet1") will always search for a sheet named "Sheet1".
But If I assign this sheet to a variable like Set ws=Worksheets("Sheet1") at the start of my code, I can always access to this worksheet without even knowing it's name later.