VBA Methods - Workbook Add with Header

Excel.workbooks.add is to create new  Workbook with template.

Syntax : expression.Add (Template) , Returns : New Workbooks with template

Refer previous post for more details Here.

Below Example is to Create New Workbook with Header:

VBA Vode:

Option Explicit
Sub Examples_WorkBookAdd_WithHeader()
    Dim MstWB As Workbook
    Dim SumWs As Worksheet
    Dim StrHdr As String
    Dim j As Integer
    
    Set MstWB = Workbooks.Add(1)
    Set SumWs = MstWB.Sheets(1)
    SumWs.Name = "Summary"
    StrHdr = "No.,Date,Code,Stock Name,Open,Close,Qty"
    For j = LBound(Split(StrHdr, ",")) To UBound(Split(StrHdr, ","))
        SumWs.Cells(1, j + 1) = Split(StrHdr, ",")(j)
    Next j
    
    'You May Include any code project Here
    
    With SumWs
        .Rows(1).Font.Bold = True
        .Cells.EntireColumn.AutoFit
    End With
    
    Set MstWB = Nothing
    Set SumWs = Nothing
    StrHdr = ""

End Sub

Note: The Example we use Template XlWBATemplate which is equivalent to 1. 

Read more about Excel.workbooks.add, excelmacros, macro excel,
excel programming, excel vba at below links.

Microsoft Reference-Excel.workbooks.add
Other Reference-Excel.workbooks.add

Leave your comments if you have any request.
Practice makes perfect.
Thank You.

To Protect and Unprotect Sheet with VBA Code

To protect worksheet from editing. 

• Go to Review Tab and click at Protect Sheet.
  
  
  
• Under Protect Sheet Key in Password and Tick allow user to do and click OK.
  
  
  
• Key in password to reconfirm and click OK.
  
  
  
  
• Done.

VBA code:

Option Explicit
Sub ProtectSheet()
    
    If ActiveSheet.ProtectContents = False Then
        With ActiveSheet
            .Protect Password:="abc123", AllowInsertingRows:=True, _
             AllowDeletingRows:=True, Contents:=True, _
             AllowFiltering:=True, Scenarios:=True
            .EnableSelection = xlUnlockedCells
        End With
    End If
    
End Sub

To Unprotect worksheet. 

• Go to Review Tab and click at Unprotect Sheet.
  
  
  
• Key in password and click OK.
  
  
  
• Done.

VBA code:

Option Explicit
Sub UnprotectSheet()
    
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect Password:="abc123"
    End If
    
End Sub

Microsoft Reference - Protect Worksheet

Practice makes perfect. Thank You.

To Use Hlookup with formula and VBA code

Hlookup formula in Microsoft Excel is to find something in table or range by columns. In this example we refer to table monthly score in Sheet1.

Table in Sheet1: Base on monthly we need to find score in

• Range("B2:M3)
  
• Row index = 2

Formula = HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = HLOOKUP(B2,Sheet1!$B$2:$M$3,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$M$3 is table_array but $ sign to fix the table when drag down.
• 2 is row_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub HLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.HLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet3").Range("$B$2:$M$3"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Hlookup function

Done.

To Use Vlookup with formula and VBA code

Vlookup formula in Microsoft Excel is to find something in table or range by rows. In this example we refer to table monthly score in Sheet1.

Table in Sheet1 and range = Range("B2:C13)  and base on month we need to find score in column index = 2. 

Formula = VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

In Sheet2 we have another table without score, now we have to lookup this value from Sheet1 table by using formula.

Fill this formula into Range("C2") = VLOOKUP(B2,Sheet1!$B$2:$C$13,2,FALSE).

• B2 is lookup_value
• Sheet1!$B$2:$C$13 is table_array but $ sign to fix the table when drag down.
• 2 is column_index_num
• FALSE is range_lookup (FALSE - Exact Match, TRUE - Approximate match)

VBA code:

Option Explicit
Sub VLookUPExample()

    Dim i As Integer
    With ActiveSheet
        i = 2
        Do
            .Range("C" & i) = Application.VLookup(.Range("B" & i), ActiveWorkbook.Sheets("Sheet1").Range("$B$2:$C$13"), 2, False)
        i = i + 1
        Loop While .Range("A" & i) <> ""
    End With
    
End Sub

Note:

• Make sure Sheet2 is selected or activated.
• If your table is located in another workbook then change ActiveWorkbook to Workbooks("MyBookName.xls") but ensure this workbook is open.

Microsoft Reference - Vlookup function

Done.

To Go Specific Cells or Range

Below code to is To Go Specific Cells and can be use to scroll any direction.

Option Explicit
Sub ToGoSpecficCells()

    Application.Goto ActiveSheet.Range("A1"), True
    
End Sub

Note: Change cells location in bracket A1-> any cell and Go cells or range will be at top left corner of excel sheet.

How to save excel file using VBA

 To save excel file we must know file extension because in excel too many format for example *.xls, *.xlsx, *.xlsm and etc. For XlFileFormat enumeration we can refer Here. 

Complete syntax we can refer here

Workbook.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

The most importance input is FileName and FileFormat the rest is optional. For FileName we must provide full path and file name. For example .xlsx (51),.xlam (55) and etc.

Below completed code to create Workbook and Save instantly:

Option Explicit
Sub CreateWorkBookAndSaveInSpecificFolder()
    
    'Variables Declaration
    Dim MstWB As Workbook
    Dim StrFullPath As String, StrFldrPath As String
    
    'Assign Variable according to folder path (Ex. folder name is "My Analysis" at Desktop
    StrFldrPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "My Analysis"
    
    'Assign Variable according to full path
    StrFullPath = StrFldrPath & "\" & "mytemplate.xlsx"
    
    'To check folder exist or not and create if none
    If Dir(StrFldrPath, vbDirectory) <> "" Then
        GoTo Line1
    Else
        MkDir StrFldrPath
    End If
Line1:
    
    'Create New Workbook with normal template
    Set MstWB = Workbooks.Add(1)
    
    'Create Workbook Content for ex. Header
    With MstWB.ActiveSheet
        .Range("A1") = "No."
        .Range("B1") = "Name"
        .Range("C1") = "Address"
        .Range("D1") = "Phone No."
    End With
    
    'Stop prompt user to replace file or not but it will replace instantly
    Application.DisplayAlerts = False
    
    'Save workbook according to file extension
    MstWB.SaveAs Filename:=StrFullPath, FileFormat:=51, CreateBackup:=False
    
    'Reset Stop prompt user back to default
    Application.DisplayAlerts = True
    
    'Close Workbook without saving
    MstWB.Close False

End Sub

Thank You

Microsoft Reference (Save As)
Microsoft Reference (File Format)
Microsoft Reference (Display Alert)

How to create new workbook with customize template Excel Vba

To start we must create our own template and save this template some where, in this example we save this template under Desktop and file name as mytemplate.xlxs.

The Path should be C:\Users\UserName\Desktop\mytemplate.xlxs, but we can't use UserName directly because different user the name given will different. We must replace UserName as below:

 VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "mytemplate.xlsx"

Refer my posting about Folder and Sub Folder Here.

Below completed code to create Workbook with customize Template:

Option Explicit
Sub CreateWorkBookWithCustomTemplate()
    
    'Variables Declaration
    Dim MstWB As Workbook
    Dim StrPath As String
    
    'Assign Variable according to template path
    StrPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "mytemplate.xlsx"
    
    If Dir(StrPath, vbDirectory) <> "" Then
    
        'Create New Workbook with Custom template
        Set MstWB = Workbooks.Add(StrPath)
    Else
        'To prompt message if no template found
        MsgBox "No Template found. Please create template at Desktop."
    End If

End Sub

Thank You

How to create new workbook with specific template Excel Vba

To create Workbook with Excel VBA, first thing we must specify which template we need to create such as Workbook with blank sheet, Chart, Macro, International Macro or Dialog. Each template represent by number or special command as below:

Workbook with Blank worksheet

Workbooks.Add(1)
    OR
Workbooks.Add(xlWBATWorksheet)

Workbook with Chart Worksheet

Workbooks.Add(2)
    OR
Workbooks.Add(xlWBATChart)

Workbook with Macro Worksheet

Workbooks.Add(3)
    OR
Workbooks.Add(xlWBATExcel4MacroSheet)

Workbook with International Macro Worksheet

Workbooks.Add(4)
    OR
Workbooks.Add(xlWBATExcel4IntlMacroSheet)

Base on my testing if we use Workbooks.Add(7) it will create Workbook with Dialog Worksheet. If we use number 5 or 6 the result is the same as Workbooks.Add(1) which Blank worksheet. If the number is greater than 7 "Run Time Error 1004 , Method Add of Object Workbooks Failed".

Below completed code to create Workbook with Blank Worksheet:

Option Explicit
Sub CreateWorkbook()
    
    'Variables Declaration
    Dim MstWB As Workbook
    
    'Change the number according to template
    Set MstWB = Workbooks.Add(1)
    
End Sub

Thank You

Microsoft Reference (Workbooks)
Microsoft Reference (Workbooks.Add)
Microsoft Reference (xlwbatemplate)