Dear Friends,
 
Let’s take a brief re-cap of what all things you have studied so far in Excel Macro Tutorial.

So far you have learnt so many basic stuffs about Excel Macros under Excel Macro Tutorials. You have learnt about all the Controls available in Excel Macros and also how to use them? You have also learnt How to Record and Run a Macro in Excel. You also have learnt some basic Programming Lessons as well like If Else Statements, For Loop, While and DO While Loops etc.

 
So far we have studied all the basic stuff about the Excel Macro, which will be required to you to start developing a tool in Excel Macro.
Here I have collected a Set of questions or doubts, which are asked as soon as you start the coding your own.
 

1. How to read or Write some value in a Excel Cell ?
2. How to read or write some thing in a Excel Cell of a Specified Sheet of the same Workbook.
3. How to Select a Worksheet from the Same Workbook ?
4. How to Activate a Worksheet from the same Workbook ?

1. How to put some value in a Excel Cell ?

This is the most frequently used stuff in excel macro. Reading and writing the data in excel cells. Here are the simple syntax to do so.

Cells(<Row Index> , <Column Index>).Value = <Your Value>
OR
Range(<Column Name><Row Index>).Value = <Your Value>
 
Steps to Do:
 
1. Open a New Workbook
2. Add a Command Button to WorkSheet (How to Add Command Button in Excel)
3. Double Click on the Command Button
4. Copy and Paste the Below Code in the Command Button Click Event

Private Sub CommandButton1_Click()
    Range("C3").Value = "Range C3"
    Cells(4, 3).Value = "Row No: 4  Column No: 3"
End Sub

 
Note: In the above you can see that no where Sheet Name is not mentioned. But as you know one Excel Workbook can have many Worksheets and in each worksheets the above Cell Address will exist. Since there is no Sheet Name mentioned, Code will put the value in the Same Sheet where your code or Your Command Button is there.
On Clicking on Button, Cell C3 and C4 will be populated with the Values. Refer the below diagram.

Enter Value in Cell

Enter Value in Cell


3. How to read or write some thing in a Excel Cell of a Specified Sheet of the same Workbook.

In the above example as you saw that Command button will put the value in specified cell in the same worksheet where Command button is located.
 
Let’s take an example, you want to put this vale in Same Cell Address but in a Sheet named “MyData”. It means when you click on Command Button, it should Sheet named “My Data” and put these values in that particular cell of that Sheet.
  Follow the below Steps:
 
Under the Command Button Click Event Paste the below Code:

Private Sub CommandButton1_Click()
    Worksheets("My Data").Range("C3").Value = "Range C3"
    Worksheets("My Data").Cells(4, 3).Value = "Row No: 4  Column No: 3"
End Sub

 
Note: Above code will through error if the Sheet Name does not exist in that workbook.

3. How to Select a Worksheet from the Same Workbook ?

Below is the Syntax to select a Particular Worksheet from the Workbook.
 
Worksheets(“Sheet Name”).Select

4. How to Activate a Worksheet from the same Workbook ?

Below is the Syntax to Activate a Particular Worksheet from the Workbook.
 
Worksheets(“Sheet Name”).Activate

 
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

To Check out more Excel Tips and Tricks, visit Excel Tips and Tricks