#thisworkbook

New Post has been published on http://analystcave.com/excel-vba-worksheets-tutorial-vba-activesheet-vs-worksheets/

Excel VBA Worksheets: VBA Activesheet vs Worksheets, Select, Activate etc.

Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets, how to Select Worksheets, how to Activate Worksheets, Selecting vs Activating Worksheets… and everything else you need to know about the VBA Worksheet in general.

ThisWorkbook vs ActiveWorkbook

Some Excel Worksheets

Lets start with the basics. Before we start I want to stress and remind the difference between ActiveWorkbooks and ThisWorksbooks. In short:

ThisWorkbook – refers to the Workbook in which the VBA macro is running

ActiveWorkbook – refers to the Workbook which is in the topmost Excel Window

It is extra important to understand this difference and I encourage you to read my post on this topic first.

The Excel VBA Object Hierarchy

Secondly it makes sense to remind the Excel Object hierarchy.

At the top, at the root we have our Excel Application. The Excel Application represents the entire Excel process. Further down the tree we have our Workbooks. Each Application contains a collection of Workbooks. Looking into a single Workbook we will notice it contains a collection of Worksheets. Worksheets on the other hand as you know can define Ranges (not the same a single cells). Using a Range we can access its cells Values or Formulas.

Accessing VBA Worksheets

Now that we have that behind us lets explore the different ways in which we can access Worksheets in VBA:

ActiveWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ActiveWorkbook:

Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Sheets 'Get entire collection of Worksheets Set ws = Sheets(1) 'Get first Worksheet in ActiveWorkbook Set ws = Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Worksheets 'Get entire collection of Worksheets Set ws = Worksheets(1) 'Get first Worksheet in ActiveWorkbook Set ws = Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook

ThisWorkbook VBA Worksheets

The Sheets and Worksheets collections

Sheets within the ThisWorkbook:

Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Sheets 'Get entire collection of Worksheets Set ws = ThisWorkbook.Sheets(1) 'Get first Worksheet in ThisWorkbook Set ws = ThisWorkbook.Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Similarly we can use Worksheets instead of Sheets.

Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Worksheets 'Get entire collection of Worksheets Set ws = ThisWorkbook.Worksheets(1) 'Get first Worksheet in ActiveWorkbook Set ws = ThisWorkbook.Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook

Worksheet VBA Name vs Excel Name

caption id=”attachment_11373″ align=”alignright” width=”213″] Worksheet VBA Name vs Excel Name[/caption]When speaking about Worksheets in ThisWorkbook, the VBA Name of a Worksheet is not the same as the Excel Name of a Worksheet. Let us understand this different. On the right we have a screen from an example Workbook. The VBAName string is the VBA Name of our Worksheet, on the other hand the Excel Name string is our Excel Name. You can use both names to refer to the same Worksheet but in different ways.

Using the VBA Name of a Worksheet

Worksheet VBA Name

We can refer to a VBA Worksheet by its VBA Name directly – just by typing it. This is very convenient and a good practice. That is because the VBA Name can’t be changed by a user by mistake from the level of Excel (not the VBE). Hence whatever name the user give from the Workbook level to your Worksheet – its VBA Name stays the same.

Using the Excel Name of a Worksheet

Referring to an Excel Worksheet from VBA is not a recommended practice. Below I am referring to the Worksheet I named Excel Name in my example above.

Dim ws as Worksheet Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

Not what you notice is that compared to acquiring the Worksheet by its VBA Name, when using the defaults Worksheets or Sheets object you land with the said Worsheet, but from the ActiveWorkbook. Usually this doesn’t do much of a difference. But I am highlighting that this is simply another place to make a common mistake. See both versions below:

Dim ws as Worksheet '---Sheet by Excel Name in ActiveWorkbook--- Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook Set ws = ActiveWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook '---Sheet by Excel Name in ThisWorkbook--- Set ws = ThisWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook

The VBA ActiveSheet

As with Active vs ThisWorkbook you need to first understand the difference between Selecting a Worksheet and Activating it.

Selected vs Activated Worksheet, the differences:

Selected Worksheet – one or more Worksheets that have been selected within an Excel Window. Each Workbook has its own set of Selected Worksheets

ActiveWorksheet – the current Worksheet you are viewing and working in. The only top-most Worksheet in all Application Workbooks

From the definition above you see that there can be more than 1 Selected Worksheet, while only 1 ActiveSheet. In fact both are totally different things. Lets explore this in some examples:

Example 1: Explaining ActiveSheet

Let us say we have 2 Workbooks open. One is Book1.xlsm and there other is Book2.xlsm.

Lets open VBE in Book1.xlsm. We will add the following piece of code:

Sub TestActiveSheet() MsgBox ActiveSheet.Name End Sub

Now lets activate Book2.xlsm. Rename any Worksheet e.g. to MyActiveWorksheet. Next go to the Developer ribbon select Macros and run Books1.xlsm!TestActiveSheet. What happened? You probably got something like this: Although you ran a macro from Book1.xlsm, VBA considers always the top-most worksheet as the ActiveSheet.

Example 2: Explaining Selected vs ActiveSheet

Let us consider the same example. Open Book1.xlsm and add the following code to the VBE:

Sub SelectedVSActive() Dim res As String, ws As Worksheet res = "Selected sheets:" & vbNewLine For Each ws In ActiveWindow.SelectedSheets res = res & ws.Name & vbNewLine Next ws MsgBox res & vbNewLine & "ActiveSheet:" & vbNewLine & ActiveSheet.Name End Sub

Now do the following:

Activate workbook Book2.xlsm

Select a number of Worksheets (at least 2)

Run Book1.xlsm!SelectedVSActive macro

What happens? You should get something like this: You see that:

Only the top-most Worksheet is considered as the ActiveSheet

The ActiveSheet is ALWAYS considered as a Selected Worksheet within the ActiveWindow (this last part is important)

What happens if you change ActiveWindow to e.g. Windows(2)? You will see a different set of Selected Worksheets. Why? Because all Workbooks have Selected Worksheets. Hope you see the difference.

Activating VBA Worksheets

Activating Worksheet is easy in VBA. You do it using the Activate property of Worksheets. How to activate any Worksheet using VBA?

Dim ws as Worksheet '...Set ws to some Worksheet ws.Activate

Activate Worksheet examples

A few examples below:

Sheets(1).Activate 'Activate first Worksheet in ActiveWorkbook Sheet1.Activate 'Activate Sheet1 in ThisWorkbook Worksheets("MyNamedWorksheet").Activate 'Activate Excel named Worksheet in ActiveWorkbook

Selecting VBA Worksheets

Selecting Worksheets is a little more difficult as we may want to select a single Worksheet or more.

Selecting a single VBA Worksheet

Selecting a single VBA Worksheet is simple. Just use the Select property of a Worksheet.

Dim ws as Worksheet '...Set ws to some Worksheet ws.Select

Selecting a single Worksheet examples

A few examples below:

Sheets(1).Select 'Select first Worksheet in ActiveWorkbook Sheet1.Select 'Select Sheet1 in ThisWorkbook Worksheets("MyNamedWorksheet").Select 'Select Excel named Worksheet in ActiveWorkbook

Selecting multiple VBA Worksheets

Selecting more than 1 Worksheet is a little more complex as we need to leverage the VBA Array function:

Sheets(Array(1, 2)).Select 'Select first and second Worksheet in ActiveWorkbook Sheets(Array("Named1", "Named2")).Select 'Select 2 named Worksheets in ActiveWorkbook

This is not the most elegant way of doing it. Fortunately the Select function has an argument called Replace. Setting it to false will mean that the previously selected is not to be deselected. Thus we can extend our selections like this:

Sheets(1).Select Call Sheets(2).Select(False) Call Sheets(3).Select(False)

This will select the first three Worksheets in our ActiveWorkbook.

Deleting VBA Worksheets

To delete a VBA Worksheet we need to turn off the Application.DisplayAlerts

Application.DisplayAlerts = False Sheets(1).Delete Application.DisplayAlerts = True

You can delete multiple Worksheets in a similar fashion as how you can do a multiple Worksheet Select:

Application.DisplayAlerts = False Sheets(Array(1, 2)).Delete Application.DisplayAlerts = True

Copying VBA Worksheets

You can copy Worksheets within a Workbook or from other open Workbooks.

'Copy Sheet1 and paste before Sheet2 Worksheets("Sheet1").Copy Before:=Sheets("Sheet2") 'Copy Sheet1 and paste before Sheet2 in Workbook Book1.xlsm Worksheets("Sheet1").Copy Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Moving VBA Worksheets

You can move Worksheets within a Workbook or from other open Workbooks.

'Move Sheet1 and paste before Sheet2 Worksheets("Sheet1").Move Before:=Sheets("Sheet2") 'Move Sheet1 and paste before Sheet2 in Workbook Book1.xlsm Worksheets("Sheet1").Move Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")

Other VBA Worksheets properties

Below are other VBA Worksheet properties worth mentioning:

Worksheet property Description Example Calculate Calculates all dirty formulas on the current Worksheet

Dim ws as Worksheet '...Set ws ws.Calculate

Name Get or set the Excel Name of the Worksheet

Dim ws as Worksheet '...Set ws Debug.Print ws.Name 'Print Excel Name of Worksheet ws.Name = "New name" 'Set new name for Worksheet

Visible Display, Hide or make Worksheet VeryHidden. Possible values:

xlSheetVisible

xlSheetVeryHidden

xlSheetHidden

Dim ws as Worksheet '...Set ws ws.Visible = xlSheetHidden 'Make ws Worksheet Hidden

Related Posts

Word VBA Tutorial

Making a VBA automated survey in Word

Excel Substring and VBA Substring – Left, R...

Excel VBA Calculate distance between two addresses...

VBA Enum – using enumerations in VBA

New Post has been published on http://analystcave.com/merge-excel-files/

Merge Excel files - How to merge multiple Excel files

Having your data spread across multiple Excel files? Want to merge Excel files into a single Excel Workbook? Today we going to explore just that – how to merge multiple Excel Workbook spreadsheets into a single Workbook. The obvious tool for this task is Visual Basic for Applications so let us jump right in.

Want to merge Worksheets or CSV files instead?:

Merge Excel Worksheets or tables

Merge CSV or text files

Merge Excel files

Let us assume we have a couple of files listed in our directory (in my example these are File1 and File2.xlsx). What we want to do is create a new Excel Workbook and copy Worksheets to this new Workbook. I wanted however to account for 2 typical scenarios:

Copy ALL worksheets

Copy only a single worksheet – with a specific name

The code below supports both these scenarios.

Merge Excel files code

Use the MergeExcelFiles Sub procedure below to merge any number of Workbooks:

Sub MergeExcelFiles(fileNames() As String, Optional worksheetName As String = vbNullString, Optional mergedFileName As String = "merged.xlsx") Dim fileName As Variant, wb As Workbook, ws As Worksheet, destWb As Workbook, excelApp As Application Set excelApp = New Application Set destWb = excelApp.Workbooks.Add For Each fileName In fileNames Set wb = excelApp.Workbooks.Open(fileName, ReadOnly = True) For Each ws In wb.Sheets If worksheetName <> vbNullString Then If ws.Name = worksheetName Then ws.Copy After:=destWb.Sheets(destWb.Sheets.Count) Else ws.Copy After:=destWb.Sheets(destWb.Sheets.Count) End If Next ws wb.Close SaveChanges:=False Next fileName destWb.SaveAs ThisWorkbook.Path & "\" & mergedFileName destWb.Close SaveChanges:=False excelApp.Quit Set destWb = Nothing: Set excelApp = Nothing MsgBox "Merge completed!" End Sub

How to use the procedure above? Below I create a simple Test procedure that lists the Excel files within the Workbook directory and merges the Workbooks.

Sub TestMerge() Dim fileNames(0 To 1) As String fileNames(0) = ThisWorkbook.Path & "\File1.xlsx" fileNames(1) = ThisWorkbook.Path & "\File2.xlsx" '... 'Merge all worksheets in listed files MergeExcelFiles fileNames 'Merge only worksheets named "SomeWs" in listed files and save the merged file as "test.xlsx" MergeExcelFiles fileNames, "SomeWs", "test.xlsx" End Sub

Pretty simple right?

Merge Excel files within a directory

The scenario above works pretty well for situations where we want to list explicitly files we want to merge into a single Excel Workbook. How about when we have tons of files? Or to make it more simple, if we want to merge all files within a SINGLE directory. As this is also a typical scenario I have modified the above Test procedure to accommodate just that:

Sub TestMergeDirectory() Dim fileNames() As String, currIndex As Long, fileName As String, directory As String directory = ThisWorkbook.Path & "\SomeDir\" ReDim fileNames(0 To 0) As String fileName = Dir(directory) fileNames(0) = directory & fileName Do Until fileName = vbNullString currIndex = currIndex + 1 ReDim Preserve fileNames(0 To currIndex) As String fileName = Dir fileNames(currIndex) = directory & fileName Loop ReDim Preserve fileNames(0 To currIndex - 1) As String MergeExcelFiles fileNames End Sub

What happens is that we use the VBA Dir function to loop through all files within a selected directory. As we go along we modify our VBA Array redefining its size and adding additional items.

Merge Excel files in a whole directory structure

One last scenario that came to mind is when we have a whole directory structure (directories within other directories) containing files with want to merge. As the VBA Dir function works only for a single directory and does not traverse any directories within we would need to amend additionally the code above. If needed I recommend reading my post on How to traverse directories using the VBA Dir function.

Related Posts

The VBA Time Saver Kit – code snippets, VBA ...

Excel Cascading drop-down (no VBA!)

Using the Excel OnTime event in VBA

Merging worksheets / tables in Excel

VBA End – Exit or Reset VBA macro