This tutorial explains the difference between ActiveWorkbook and ThisWorkbook in VBA.
When multiple workbooks are open in Excel, the difference between ActiveWorkbook and This Workbook becomes more important.
It refers to the workbook that is currently active. It is used when we want to perform an action on the workbook we are interacting with. Let's see the example below :
ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = "Hello"
This code stores "Hello" in cell 'A1' of Sheet1 in the ActiveWorkbook.
If we switch to another workbook then the ActiveWorkbook also changes to reflect the new workbook.
It refers to the workbook that contains the currently running VBA code and is not affected by which workbook is currently active.
Suppose multiple workbooks are open and the code is in excel file named "sales". It doesn't matter which workbook is active. ThisWorkbook only refers to "sales". Let's see the example below :
Sub FormatThisWorkbook()
ThisWorkbook.Sheets("Sheet1").Range("A1").Font.Bold = True
End Sub
This code will format the cell A1 in "sales" workbook irrespective of which workbook is active.
There are many situations where we need to work with both ActiveWorkbook and ThisWorkbook. In the example below, we want to copy data from the active workbook and paste it into the workbook that contains the code.
Sub CopyDataBetweenWorkbooks()
Dim data As variant
data = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
ThisWorkbook.Sheets("Sheet1").Range("B1").Value = data
End Sub
Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.
Share Share Tweet