Create Index in Excel

Create Index in Excel

Create Index in Excel – Create Table of Contents in Excel

To Create Index in Excel just copy the code, create a module in your Excel’s visual basic editor, then paste the code and run. This code not just creates a Table of content in Excel but also creates a Hyperlink to the respective sheets. It also creates a Hyperlink in all the sheets back to Index sheet. When you run the code for the first time this code creates a sheet named index and creates the table of content in it.

Please note: Please backup and run this code on the copy of your original file. And never run this code over and over again as it will create duplicates of hyperlinks in every sheet.

Disclaimer: The author of this code cannot be held responsible for any kind of data loss or damage. If you are running or using this code you should agree for this terms and conditions.

Create Index in Excel
Sub CreateIndex()
Dim FirstCellAdd As String
Dim PrecentSheetAdd As String
Dim CombinedAdd As String
Dim FirstSheetAdd As String
FirstCellAdd = "!A1"
FirstSheetAdd = "Index"
FirstSheetCellAdd = FirstSheetAdd & FirstCellAdd
Worksheets.Add before:=Worksheets(1)
ActiveSheet.Name = "Index"
For a = 1 To Sheets.Count
    ActiveSheet.Cells(a, 1).Value = Sheets(a).Name
    PrecentSheetAdd = Sheets(a).Name
        
    CombinedAdd = PrecentSheetAdd & FirstCellAdd
    
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        CombinedAdd, TextToDisplay:=PrecentSheetAdd
    
    ActiveCell.Offset(1, 0).Select
Next a

For a = 2 To Sheets.Count
Sheets(a).Select

If ActiveSheet.Type <> 3 Then

ActiveSheet.Range("A1").Select
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Insert
ActiveSheet.Range("A1").Select


ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        FirstSheetCellAdd, TextToDisplay:=FirstSheetAdd
Else
ActiveSheet.Next.Select
End If

Next a

End Sub



Other Excel Tutorials & Articles: 

Advanced Excel

Excel Formulas

Excel VBA Macro Lessons

Excel Macro Examples



Combine Excel Sheets into one Sheet Macro

combine excel sheets into one sheet macro

Combine Excel Sheets into one Sheet Macro – Combine Excel Sheets into one Worksheet VBA – Copy data from multiple worksheets into one worksheet VBA

Combine Excel Sheets into one Sheet Macro – Combine Excel Sheets into one Worksheet VBA. I created this code with a different intention. This is not same as the consolidate option of Excel. Actually this code copies every worksheets data, creates a new sheet called ‘condolidatedata’ and pastes every sheets data one below the other. My intention was not to summarise the data but to just copy and combine multiple sheets data into one single worksheet.

The worksheets may not necessarily have data of same kind. This code works with data of different manner as well. And of course works with data of different rows and column size.

Please like my facebook page, youtube channel  and share this with your friends.

Click here to Download the file

Excel VBA Input box tutorial – How to create number series using input box

excel vba input box tutorial, excel vba input box examples

Excel VBA Input box tutorial – How to create number series using input box

Copy the code below for creating number series down the rows using inputbox function:

Sub SeriesInRows()
Dim X, Y, A, B, N
X = InputBox(“Enter the Row Offset”, “(Optional) Empty cells between series”, 0)
A = InputBox(“Enter the starting number in the series”, “Starting Number”, 1)
B = InputBox(“Enter the ending number in the series”, “Ending Number”, 50)
For N = A To B
ActiveCell.Value = N
ActiveCell.Offset(X + 1, Y).Select
Next N
X = 0
Y = 0
End Sub

Copy the code below for creating number series across the columns using inputbox function:

Sub SeriesInColumns()
Dim X, Y, A, B, N
Y = InputBox(“Enter the Row Offset”, “(Optional) Empty cells between series”, 0)
A = InputBox(“Enter the starting number in the series”, “Starting Number”, 1)
B = InputBox(“Enter the ending number in the series”, “Ending Number”, 50)
For N = A To B
ActiveCell.Value = N
ActiveCell.Offset(X, Y + 1).Select
Next N
X = 0
Y = 0
End Sub

Click the link below to download the macro file

Click here to download the file

I am very sure that this could be a best example for excel vba input box examples.

Excel macro examples – How to insert single or multiple worksheet based on active cell data or content

excel macro examples

Excel macro examples – How to insert single or multiple worksheet based on active cell data or content – Excel VBA For Each Loop Example

About This Macro:

This macro creates multiple worksheets at once with just a single click. The worksheet names would be the names entered in a column continuously one after the other. Please note that you need to select at least one cell with data and no blank cell before running the macro. Also make sure that if you are selecting multiple cells or range then it should not contain any blank cell.

Step 1 – Launch Excel’s Visual Basic Editor

To do that just click on developer tab, then click on visual basic to launch the Visual Basic Editor (VBE)

Step 2 – Insert Module

In the Visual Basic Editor, click on insert menu and then click on module

Step 3 – Copy and paste the below code to insert single or multiple worksheet based on active cell data or content. In this example I emphasize Excel VBA For Each Loop

 

Sub InsertWsheet()
Dim rRange As Range
Set rRange = ActiveWindow.RangeSelection
For Each rRange In ActiveWindow.RangeSelection
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = rRange.Value
Next rRange
End Sub

Step 4 – Run the Macro

To run the macro follow any of the methods below:

Method 1: Place the cursor anywhere in the code you just added to the module and click on Run Sub/UserForm (F5) button or press the F5 key on your keyboard.

Method 2: Click on Run Menu and then click on Run Sub/UserForm (F5)

Method 3: Go to Excel’s Developer Tab and click on Macros, select the macro by name and click on run button (You may also press Alt + F8 keys to display the macros dialog)

Method 4: Go to Excel’s View Tab, click on macros and then click on view macros. Then select the macro name and click on run.

excel vba for each

Similar Tutorial: Excel Tutorial VBA Macros – How to create a number chart 1 to 100 using Excel macro – Excel Macro Examples Series – Excel VBA For Next Loop Example

How to create a number chart 1 to 100 using MS Excel macro

excel macro examples

Excel Tutorial VBA Macros – How to create a number chart 1 to 100 using Excel macro – Excel Macro Examples Series – Excel VBA For Next Loop Example

Step 1 – Launch Excel’s Visual Basic Editor

To do that just click on developer tab, then click on visual basic to launch the Visual Basic Editor (VBE)

Step 2 – Insert Module

In the Visual Basic Editor, click on insert menu and then click on module

Step 3 – Copy and paste the below code to create the number chart 1 to 100 is as follows. In this example I emphasize Excel VBA For Next Loop

 

Sub india2()

‘The below for next loop creates the number chart

For n = 1 To 10
For m = 1 To 10
Cells(n, m).Select
a = a + 1
ActiveCell.Value = a
Next m
Next n

‘The below excel vba code formats the number chart and make the cells appear square
Range(“A1:J10”).Select
Selection.RowHeight = 25
Selection.ColumnWidth = 4

‘The excel vba code below centers the contents to middle center

Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter

‘ applying the borders

Selection.Borders(xlEdgeLeft).Weight = xlThick
Selection.Borders(xlEdgeTop).Weight = xlThick
Selection.Borders(xlEdgeBottom).Weight = xlThick
Selection.Borders(xlEdgeRight).Weight = xlThick
Selection.Borders(xlInsideVertical).Weight = xlThick
Selection.Borders(xlInsideHorizontal).Weight = xlThick

‘ Now this part of excel vba code below adds a column before the number chart

Range(“A1”).Select
Selection.EntireColumn.Insert
Selection.EntireRow.Insert

‘ Now this part of excel vba code below adjusts the first column and row height

Selection.RowHeight = 63.75
Selection.ColumnWidth = 13.29

‘ Now this part of excel vba code below creates a title to the number chart and format it

Range(“B1:K1”).Select
Selection.Merge
ActiveCell.Value = “Number Chart 1 to 100”
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = True
Selection.Font.Size = 18

‘ Now the number chart is ready.
‘You can create a number chart in other dimensions in the same way.
‘ Just change the values

‘ Hope you enjoyed the tutorial

End Sub

Step 4 – Run the Macro

To run the macro follow any of the methods below:

Method 1: Place the cursor anywhere in the code you just added to the module and click on Run Sub/UserForm (F5) button or press the F5 key on your keyboard.

Method 2: Click on Run Menu and then click on Run Sub/UserForm (F5)

Method 3: Go to Excel’s Developer Tab and click on Macros, select the macro by name and click on run button (You may also press Alt + F8 keys to display the macros dialog)

Method 4: Go to Excel’s View Tab, click on macros and then click on view macros. Then select the macro name and click on run.

excel vba for next loop

Similar Tutorial: Excel macro examples – How to insert single or multiple worksheet based on active cell data or content – Excel VBA For Each Loop Example