PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

WORKBOOKS AND SHEETS IN VBA

INTRODUCTION

In this article we’ll see some additional functions available for WORKBOOKS AND SHEETS in VBA. Let us understand the terms first.
WORKBOOK:
An Excel’s complete independent file is known as a workbook. The file which is saved in the computer is workbook.
A workbook can have at least one sheet and maximum a big number of sheets in a single workbook.( Subject to the availability of memory)
SHEET:
Sheet is a standalone working documents having cells in it, where we work. There can be many sheets in a workbook. The sheets can have connected.Every Sheet can have different kind of data within itself.
Let us see some of the basic functions which we can exploit using VBA to make our work easier.
 
 

ACTIVATING A WORKBOOK IN VBA

Activating a workbook means making it active and making it ready to work.
It is possible that many workbooks are opened at the same time and we need the VBA to work on the different workbook.
Now if, a situation occurs, when we need to change the focus on the other workbook, we can use this

 

 

WORKBOOKS(INDEX).ACTIVATE

OR

WORKBOOKS(“WORKBOOK NAME”).ACTIVATE

*INDEX IS GIVEN AS PER THE OPENING EVENT OF THE WORKBOOK. I.E. THE FIRST OPENED WORKBOOK WILL HAVE INDEX 1 AFTER THAT 2,3,…. AND SO ON.

ITS ALWAYS BETTER TO GO WITH THE NAME.

SAVING THE WORKBOOK

After the job is completed, we can automate it to save itself.

The workbooks can be saved through a routine in VBA.

First of all, let us save all the workbooks, which are already saved and are not new. It’ll simply save the workbooks.

 

‘gyankosh.net

‘saving a workbook

Public Sub SaveWorkbooks()
Dim ABC As Workbook
For Each ABC In Workbooks
If ABC.Path <> “” Then ABC.Save

MsgBox(ABC.path)

Next ABC
End Sub

 

PATH PROPERTY- The path property of the workbook contains its saved path.

The above code declares a workbook as ABC.

Workbooks object contains all the opened workbooks.

So we start a loop and check the workbooks one by one, if the path of any workbook is not empty, then just save it using the method, ABC.SAVE

Just for information we put another line of message box.

 

SAVING THE NEW WORKBOOK

In the previous workbooks, we saved the workbooks which were not new, or the workbooks which were already saved with a name and the current saving process was for the updates we made in the workbooks.
Now let us see how to save a new workbook in Excel using VBA.

‘gyankosh.net

‘saving a workbook

Public Sub SaveWorkbooks()
Dim ABC As Workbook
For Each ABC In Workbooks
If ABC.Path <> “” Then
ABC.Save
Else
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False

ActiveWorkbook.SaveAs Filename:=fName

End If

Next ABC
End Sub

The above mentioned program is the advanced version of the previous one.

In this program , we start with by declaring a WORKBOOK variable.

The loop is started for the ABC variable till the all the workbooks are visited.The loop starts and checks all the opened workbooks one by one.

First of all the loop checks if the FILE IS ALREADY SAVED by checking its PATH property, if it is not going to be saved for the first time, it’ll be saved.

If the file is to be saved for the first time, it’ll go to a DO LOOP and get the file name by opening a SAVE AS dialog box.

After putting the name in the save as dialog box, the name is passed and the workbook is saved as the given filename.

The DO loop is closed and FOR loop also closed.

 

SAVING AND CLOSING ALL THE WORKBOOKS

After the job is done, we have to close the workbooks. So, we can use VBA to make a routine which will save all the opened workbooks and close them after saving.
It is a very safe practice to save your work before closing the workbook.
Let us try to build one.

 


‘gyankosh.net

‘closing all workbooks after saving them


Sub closeallworkbooks()
    Dim ABC As Workbook
       For Each ABC In Workbooks
         If ABC.Name <> ThisWorkbook.Name Then
         ABC.Close savechanges:=True
         End If
        Next ABC
     ThisWorkbook.Close savechanges:=True
End Sub

We delare a variable ABC as a workbook.

The For loop starts to check every workbook.

If Workbook is not the same as the one which contains the code , then the workbook is closed with save changes as true that means, that the workbook will save all data before the closing.

After closing all the workbooks,

we’ll close the workbook containing the code by THISWORKBOOK.Close method.

We close the code containing workbook in the last because otherwise , all the workbooks may  or may not be closed.

ACTIVATING A WORKSHEET IN VBA

While working in a workbook, we use many different sheets, but whenever using VBA, we always need to activate the sheet and get the worksheet in focus. So there is need to have a function with the use of which , we can use other sheet whenever we need.
Here is the format of activating a Sheets.

SHEETS(INDEX).ACTIVATE

OR

SHEETS(“SHEET NAME”).ACTIVATE

*INDEX IS GIVEN AS PER THE OPENING EVENT OF THE SHEET. I.E. THE FIRST OPENED SHEET WILL HAVE INDEX 1 AFTER THAT 2,3,…. AND SO ON.

ITS ALWAYS BETTER TO GO WITH THE NAME.

INSERTING THE SHEETS

We use many sheets to segregate the data of different types in the Workbook. We can use many sheets in the workbook as per the availability of the memory.

Let us try a routine to insert the sheets at a specific position in the Workbook.

 

ADDING A SHEET

'gyankosh.net
'Adding a new sheet

Sub addnewSheet()

Dim newsheet As Worksheet 'Declaring a worksheet variable

Set newsheet = Sheets.Add(Type:=xlWorksheet) 'Adding a new worksheet

Sheets(Sheets.Count).Name = "gyankosh" 'Naming the worksheet

Sheets("gyankosh").Activate 'Bringing the focus to the Sheet

End Sub
We start by declaring a variable of WORKSHEET.

The new worksheet is set by adding a worksheet to the SHEET object with the method SHEETS.ADD
The type has been specified as xlWorkSheet.
After successfully adding a sheet,  we try to name the sheet.
So we use the method in SHEETS object.
Sheets(index of the Sheets given by Sheets.count which will give total number of sheets).Name =”gyankosh or any name you like”. This is the way to name a sheet in VBA.
After naming, remember that the sheet will be added but it won’t be focussed, i.e. it won’t be open for work. Si if we want to activate it too, use the method
Sheets(“name of the sheet”.Activate

ADDING A SHEET AT A SPECIFIC LOCATION USING VBA

This section deals with the procedure of adding a new Sheet after a specific Sheet in a workbook.

In the last section, we just added a worksheet into the current Workbook without specifying its location. So it’ll be added in a sequence whatever index is to be assigned. Now let us try to learn how we can add a sheet to a specified number. Suppose there are three sheets in the workbook. We’ll add a sheet at number 2.

'gyankosh.net
'Adding a new sheet at position 2.

Sub addnewSheetataposition()

Dim newsheet As Worksheet 'Declaring a worksheet variable

Set newsheet = Sheets.Add(Type:=xlWorksheet, after:=Sheets(1)) 'Adding a new worksheet after (Sheet(1))

Sheets(2).Name = "kyc" 'Naming the worksheet Sheets(2) is the new added Sheet

Sheets("kyc").Activate 'Bringing the focus to the Sheet

End Sub
We start by declaring a variable of WORKSHEET.
The new worksheet is set by adding a worksheet to the SHEET object with the method SHEETS.ADD. Here we add one more property after:=position to specify at which position the Sheet should be inserted.
The type has been specified as xlWorkSheet.
After successfully adding a sheet,  we try to name the sheet.
So we use the method in SHEETS object.
Sheets(index of the Sheets given by Sheets.count which will give total number of sheets).Name =”kyc” or any name you like. This is the way to name a sheet in VBA.
After naming, remember that the sheet will be added but it won’t be focused, i.e. it won’t be open for work. Si if we want to activate it too, use the method
Sheets(“kyc”).Activate

MOVING THE SHEETS

After inserting the Sheets, its now the turn to learn how to move the sheet at a specific positions. Like inserting the sheets, it is easy too.
 

 

MOVING A SHEET -AFTER A SPECIFIC SHEET

'gyankosh.net
'Move a sheet at a specific position.
Sub movesheets()

Worksheets("kyc").Move after:=Worksheets(1) 'Moving a sheet at positin 2.

End Sub

We start the code with declaring the sub procedure.
There is already a sheet named KYC.
We use the method
Worksheets(“kyc”) which refers to kyc sheet.  and use the MOVE method to move this sheet.
Using a space and now telling the location by using after:=Worksheets(1) which means that worksheet is to be moved after the Worksheets with the index 1.
Kindly try the same with the property BEFORE.

MOVING A SHEET-BEFORE A SPECIFIC SHEET

We just learnt about shifting the sheet after any specific sheet. Now let us try the opposite. Shifting the sheet before a specified sheet.

'gyankosh.net
'Move a sheet at a specific position.
Sub movesheetsbefore()
Worksheets("kyc").Move before:=Worksheets(1) 'Moving a sheet at positin 2.
End Sub

The only difference between moving a sheet before and after is in the property used.

For before, we use the word before. The notation goes like this

Worksheets(“Name of the sheet to be moved”).move before:=Worksheets(index)

 

ADDING A SHEET BEFORE A SPECIFIC SHEET

This section deals with the procedure of adding a new Sheet BEFORE a specific Sheet in a workbook.

In the last section, we added a sheet after a specified sheet. Now let us try to add a sheet before a specified sheet. Here is the code for doing the action.

'gyankosh.net
'Adding a new sheet at position before kyc.

 

Sub addnewSheetataposition1()

Dim newsheet As Worksheet ‘Declaring a worksheet variable

Set newsheet = Sheets.Add(Type:=xlWorksheet, before:=Sheets(“kyc”))

‘Adding a new worksheet before “kyc”

newsheet.Name = “bhalu”

‘Naming the worksheet Sheets(2) is the new added Sheet

Sheets(“bhalu”).Activate ‘Bringing the focus to the Sheet

End Sub

We start by declaring a variable of WORKSHEET.
The new worksheet is set by adding a worksheet to the SHEET object with the method SHEETS.ADD. Here we add one more property before:=position (which is given by Sheets(“name of sheet”)) to specify at which position the Sheet should be inserted.
The type has been specified as xlWorkSheet.
After successfully adding a sheet,  we try to name the sheet.
So we use the method in SHEETS object.
We  used directly the Worksheet to name it by the use of property NAME.
After naming, remember that the sheet will be added but it won’t be focused, i.e. it won’t be open for work. So if we want to activate it too, use the method
Sheets(“bhalu”).Activate

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: