Write in a worksheet and workbook from VBA (object hierarchy)

To write in a cell from VBA, you have to know in which worksheet the cell is located. Is it the sheet1 or sheet2, ....?

To write in a cell use following command line.

Worksheets(1).Range("A1").Value = "Hello"

Where Worksheet(1) refers to the first worksheet.

Here an example on how to write in the sheet you wish in different manners.

write in sheet

You can see that there are many ways to write text in a sheet.

Worksheets(1).Range("A1").Value = "Hello"

Worksheets(1).Range("A2").Value = "Lets"

Worksheets("FirstSheet").Range("A3").Value = "write"

Worksheets(1).Range("A4").Value = "some"

Sheet1.Range("A5").Value = "text"

TIP:

It is important to understand the principle of hierarchy in Excel or any MS Office application.

In the following line, we write in the sheet1, in cell A6 the value BYE.

Application.Workbooks("write_in_sheets").Worksheets(1).Range("A6").Value = "BYE"

BYE write

Application is the top hierachical level.

Workbook(name of the file) will specify which file you are working in.

The next part Worksheet(1),Range() are explained before.

 

Add a workbook

This will create a new workbook.

Workbooks.Add

 

Add a worksheet

This will add a worksheet in you workbook.

Worksheets.Add

 

Count the worksheets

This will return the number of worksheet in you workbook.

MsgBox Worksheets.Count   will display the number of worksheets.

a= Worksheets.Count    will give to the value a the number of worksheets.

Workbooks.Count will count the number of workbooks

the Count property of the Workbooks collection counts the number of active workbooks.

 

Please Tweet, Like or Share us if you enjoyed.