Learn and Create Macros Easily - Creating multiple sheets with VBA (Macro)

Welcome to the blog. Without wasting anytime we will get staraight to the point. Here we will learn and create Macros in a very easy way.

In this blog we will create multiple tabs based on the Locations. Here  is the Data which we will be using to create Tabs. In this sample data, there are 10 Cities and you are tasked to create 10 Tabs with the city names. Manually it can be done. Create 10 Tabs and rename each one. Just imagine if you have 200 cities. It would be very time consuming and tiring job.

Now let is create a code which will create 10 tabs and rename it with the cities.

The Code will look Like this

You can copy the code from below and use it in your excel

-------------------------

Sub Citi_Name()

    

    Range("A1").Select

    Selection.End(xlDown).Select

    TotCity = ActiveCell.Row - 1

    

    

    For i = 1 To TotCity

        

        TotSheet = ActiveWorkbook.Sheets.Count

        x = i + 1

        Sheets(1).Select

        CityName = Range("a" & x).Text

        Sheets.Add After:=Sheets(Sheets.Count)

        

        Sheets(TotSheet + 1).Name = CityName

        

    Next i

-------------------------

Now let us learn how to write the code with explanation

Copy the below data and paste it sheet1 in the excel

--------------

Cities

Bengaluru

Chennai

Hydrabad

Trivindram

Delhi

Kolkata

Mumbai

Pune

Mysuru

Chandigarh

-----------------

On your excel press Alt+F11

You will see this screen

Then click on insert-> module

You will see this . This is the place where our code will be written.

This is the module and we need a macro or code  to run this. Let us name this Citi_Name. Hence we will write 'Sub Citi_Name' and press enter. As soon as we press end enter the 'End Sub' appears.  Our code will be between these Sub and End Sub

To toggle between excel sheet and code we need to press Alt+F11

Now let us try to understand what the code means line by line

---------------------------

    Range("A1").Select – The cursor is placed in the ‘A1’ Cell

    Selection.End(xlDown).Select – The cursor is moved to the last cell in the range. In this case ‘A11’

    TotCity = ActiveCell.Row – 1 – ‘TotCity’ is the variable to define total city in the range.

                                                                We minus 1  since the first row is header and we will exclude it

                                                                Hence Last row is 11 minus 1 = 10. Therefore TotCity would be 10

       

 

 Below we will loop from 1 to10, the cities from Bengaluru through Chandigarh in our case 

 For i = 1 To TotCity

   

       TotSheet = ActiveWorkbook.Sheets.Count – ‘TotSheet’ is the variable to identify total number of sheets

 

        x = i + 1 –

        Sheets(1).Select

        CityName = Range("a" & x).Text – ‘CityName’  ‘ CityName’ is the variable used to pick the city name

        Sheets.Add After:=Sheets(Sheets.Count) – ‘A new sheet is added

       

        Sheets(TotSheet + 1).Name = CityName – ‘This line of code renames the last sheet to the City

       

    Next I                - ‘Will move to next row  and will loop until last city in this case ‘A11’ Chandigarh

------------------------------

Press Alt+F8 to activate the macro. This will bring up this screen. Click on Run button. It will run the macro and the final spread sheet will have all the sheets with the City Names

You can watch the video on YouTube

Try this code and create your macro. Cheers!!!