Tag Archives: workbook

excel vba worksheets tutorial

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

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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
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

Excel vba object hierarchySecondly 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 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
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

vba worksheets exampleLet 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:
vba activesheet
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:
vba activesheet vs selected
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

Merge Excel files – How to merge multiple Excel files

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 3.75 out of 5)
Loading...

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.

Merge Excel files manually

Open the destination Workbook

Merge Source.xlsx with Destination.xlsx
Merge Source.xlsx with Destination.xlsx
Open the destination Excel Workbook (in our example Destination.xlsx) to which you want to copy the Worksheets of the remaining Workbooks (in our example Source.xlsx).

The further steps need to be repeated for each Excel Workbook you want to copy to the destination Workbook.

Open (each) the Workbook you want to merge and copy Worksheets

Copy each Worksheet to the destination Workbook
Copy each Worksheet to the destination Workbook
Open each Excel Workbook you want to merge with the destination Workbook.

Next right-click on each Worksheet you want to copy, click Move or Copy.... In the Move or Copy Window select the destination Workbook (Destination.xlsx in our case). Next:

  • If you want to move (cut & paste) the Worksheet – simply click the OK button to proceed
  • If you want to copy (copy & paste) the Worksheet – select the Create a Copy checkbox and click the OK button

Copy Worksheet to destination Workbook
Copy Worksheet to destination Workbook

Repeat Step 2 for each Workbook you want to copy to the destination Workbook.

Merge Excel files using VBA

merge excel filesLet 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.

Download the code

Want to download the code above and the example?

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.

vba excel range object

Excel VBA Range Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The VBA Range Object

The Excel Range Object is an object in Excel VBA that represents a cell, row, column, a selection of cells or a 3 dimensional range. The Excel Range is also a Worksheet property that returns a subset of its cells.

Contents
worksheet range
worksheet cells
range rows and columns
range cut paste
range clear
traverse ranges
range addresses
merged ranges
named ranges
specialcells

Worksheet Range

worksheet range property
The Worksheet Range property
The Range is a Worksheet property which allows you to select any subset of cells, rows, columns etc. You can use the Range property to:

  • Obtain a reference to a single Worksheet Cell:
    range select cell
    Select a single cell using the Range object
  • Obtain a range of cells using a string:
    range select range
    Select a Range of cells using a string parameter
  • Obtain a range of cells using other Range objects:
    range select range
    Select a Range using other Range objects
The Range.Select function select the range within a Worksheet.
The Range.Activate function makes the range visible

Working with Range variables

The Range is a separate object variable and can be declared as other variables:

Dim myRange as Range

Excel facilitates a Range function that allows you to obtain cells from within the ActiveSheet, current top-most worksheet.

Range("A1").Select
'...is the same as...
ActiveSheet.Range("A1").Select
The ActiveWorkbook is not same to ThisWorkbook. Same goes for the ActiveSheet. This may reference a Worksheet from within a Workbook external to the Workbook in which the macro is executed as Active references simply the currently top-most worksheet. Read more here

Range properties

The Range object contains a variety of properties with the main one being it’s Value and an the second one being its Formula.

A Range Value is the evaluated property of a cell or a range of cells. For example a cell with the formula =10+10 has an evaluated value of 20.

A Range Formula is the formula provided in the cell or range of cells. For example a cell with a formula of =10+10 will have the same Formula property.

Other Range properties include:
Work in progress

Worksheet Cells

worksheet cells arguments
The Worksheet Cells property
A Worksheet Cells property is similar to the Range property but allows you to obtain only a SINGLE CELL, based on its row and column index. Numbering starts at 1:
cells select cell
Select a single Cell using the Cells property

The Cells property is in fact a Range object not a separate data type.
Excel facilitates a Cells function that allows you to obtain a cell from within the ActiveSheet, current top-most worksheet.

Cells(2,2).Select 'Selects B2
'...is the same as...
ActiveSheet.Cells(2,2).Select 'Select B2

Cells are Ranges which means they are not a separate data type:

Dim myRange as Range
Set myRange = Cells(1,1) 'Cell A1

Range Rows and Columns

As we all know an Excel Worksheet is divided into Rows and Columns. The Excel VBA Range object allows you to select single or multiple rows as well as single or multiple columns. There are a couple of ways to obtain Worksheet rows in VBA:

Getting an entire row or column

entirerow range
Range EntireRow property
To get and entire row of a specified Range you need to use the EntireRow property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
entirecolumn range
Range EntireColumn property
To get and entire column of a specified Range you need to use the EntireColumn property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the column number. Column indexing starts at 1.

Range("B2").EntireRows(1).Hidden = True 'Gets and hides the entire row 2

Range("B2").EntireColumns(1).Hidden = True 'Gets and hides the entire column 2
The three properties EntireRow/EntireColumn, Rows/Columns and Row/Column are often misunderstood so read through to understand the differences.

Get a row/column of a specified range

range rows function
Range Rows function
If you want to get a certain row within a Range simply use the Rows property of the Worksheet. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
range columns function
Range Columns property
Similarly you can use the Columns function to obtain any single column within a Range. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex actually the first argument you provide will be the column index. Column indexing starts at 1.

Rows(1).Hidden = True 'Hides the first row in the ActiveSheet
'same as
ActiveSheet.Rows(1).Hidden = True

Columns(1).Hidden = True 'Hides the first column in the ActiveSheet
'same as
ActiveSheet.Columns(1).Hidden = True

To get a range of rows/columns you need to use the Range function like so:

Range(Rows(1), Rows(3)).Hidden = True 'Hides rows 1:3
'same as
Range("1:3").Hidden = "True
'same as 
ActiveSheet.Range("1:3").Hidden = "True

Range(Columns(1), Columns(3)).Hidden = True 'Hides columns A:C
'same as
Range("A:C").Hidden = "True
'same as 
ActiveSheet.Range("A:C").Hidden = "True

Get row/column of specified range

The above approach assumed you want to obtain only rows/columns from the ActiveSheet – the visible and top-most Worksheet. Usually however, you will want to obtain rows or columns of an existing Range. Similarly as with the Worksheet Range property, any Range facilitates the Rows and Columns property.

Dim myRange as Range
Set myRange = Range("A1:C3")

myRange.Rows.Hidden = True 'Hides rows 1:3
myRange.Columns.Hidden = True 'Hides columns A:C

Set myRange = Range("C10:F20")
myRange.Rows(2).Hidden = True 'Hides rows 11
myRange.Columns(3).Hidden = True 'Hides columns E

Getting a Ranges first row/column number

Aside from the Rows and Columns properties Ranges also facilitate a Row and Column property which provide you with the number of the Ranges first row and column.

Set myRange = Range("C10:F20")

'Get first row number
Debug.Print myRange.Row 'Result: 10
'Get first column number
Debug.Print myRange.Column 'Result: 3

Converting Column number to Excel Column

This is an often question that turns up – how to convert a column number to a string e.g. 100 to “CV”.

Function GetExcelColumn(columnNumber As Long)
    Dim div As Long, colName As String, modulo As Long
    div = columnNumber: colName = vbNullString

    Do While div > 0
        modulo = (div - 1) Mod 26
        colName = Chr(65 + modulo) & colName
        div = ((div - modulo) / 26)
    Loop

    GetExcelColumn = colName
End Function

Range Cut/Copy/Paste

Cutting and pasting rows is generally a bad practice which I heavily discourage as this is a practice that is moments can be heavily cpu-intensive and often is unaccounted for.

Copy function

Range copy function
Range copy function
The Copy function works on a single cell, subset of cell or subset of rows/columns.

'Copy values and formatting from cell A1 to cell D1
Range("A1").Copy Range("D1")

'Copy 3x3 A1:C3 matrix to D1:F3 matrix - dimension must be same
Range("A1:C3").Copy Range("D1:F3")

'Copy rows 1:3 to rows 4:6
Range("A1:A3").EntireRow.Copy Range("A4")

'Copy columns A:C to columns D:F
Range("A1:C1").EntireColumn.Copy Range("D1")

The Copy function can also be executed without an argument. It then copies the Range to the Windows Clipboard for later Pasting.

Cut function

range cut function
Range Cut function
The Cut function, similarly as the Copy function, cuts single cells, ranges of cells or rows/columns.

'Cut A1 cell and paste it to D1
Range("A1").Cut Range("D1")

'Cut 3x3 A1:C3 matrix and paste it in D1:F3 matrix - dimension must be same
Range("A1:C3").Cut Range("D1:F3")

'Cut rows 1:3 and paste to rows 4:6
Range("A1:A3").EntireRow.Cut Range("A4")

'Cut columns A:C and paste to columns D:F
Range("A1:C1").EntireColumn.Cut Range("D1")

The Cut function can be executed without arguments. It will then cut the contents of the Range and copy it to the Windows Clipboard for pasting.

Cutting cells/rows/columns does not shift any remaining cells/rows/columns but simply leaves the cut out cells empty

PasteSpecial function

range pastespecial function
Range PasteSpecial function
The Range PasteSpecial function works only when preceded with either the Copy or Cut Range functions. It pastes the Range (or other data) within the Clipboard to the Range on which it was executed.

Syntax

The PasteSpecial function has the following syntax:

PasteSpecial( Paste, Operation, SkipBlanks, Transpose)
The PasteSpecial function can only be used in tandem with the Copy function (not Cut)
Parameters

Paste
The part of the Range which is to be pasted. This parameter can have the following values:

Parameter Constant Description
xlPasteSpecialOperationAdd 2 Copied data will be added with the value in the destination cell.
xlPasteSpecialOperationDivide 5 Copied data will be divided with the value in the destination cell.
xlPasteSpecialOperationMultiply 4 Copied data will be multiplied with the value in the destination cell.
xlPasteSpecialOperationNone -4142 No calculation will be done in the paste operation.

xlPasteSpecialOperationSubtract 3 Copied data will be subtracted with the value in the destination cell.

Operation
The paste operation e.g. paste all, only formatting, only values, etc. This can have one of the following values:

Name Constant Description
xlPasteAll -4104 Everything will be pasted.
xlPasteAllExceptBorders 7 Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats 14 Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.
xlPasteColumnWidths 8 Copied column width is pasted.
xlPasteComments -4144 Comments are pasted.
xlPasteFormats -4122 Copied source format is pasted.
xlPasteFormulas -4123 Formulas are pasted.
xlPasteFormulasAndNumberFormats 11 Formulas and Number formats are pasted.
xlPasteValidation 6 Validations are pasted.
xlPasteValues -4163 Values are pasted.
xlPasteValuesAndNumberFormats 12 Values and Number formats are pasted.

SkipBlanks
If True then blanks will not be pasted.

Transpose
Transpose the Range before paste (swap rows with columns).

PasteSpecial Examples

'Cut A1 cell and paste its values to D1
Range("A1").Copy
Range("D1").PasteSpecial
 
'Copy 3x3 A1:C3 matrix and add all the values to E1:G3 matrix (dimension must be same)
Range("A1:C3").Copy 
Range("E1:G3").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

Below an example where the Excel Range A1:C3 values are copied an added to the E1:G3 Range. You can also multiply, divide and run other similar operations.

PasteSpecial example - Copy and Add
PasteSpecial example – Copy and Add

Paste

The Paste function allows you to paste data in the Clipboard to the actively selected Range. Cutting and Pasting can only be accomplished with the Paste function.

'Cut A1 cell and paste its values to D1
Range("A1").Cut
Range("D1").Select
ActiveSheet.Paste
 
'Cut 3x3 A1:C3 matrix and paste it in D1:F3 matrix - dimension must be same
Range("A1:C3").Cut 
Range("D1:F3").Select
ActiveSheet.Paste
 
'Cut rows 1:3 and paste to rows 4:6
Range("A1:A3").EntireRow.Cut 
Range("A4").Select
ActiveSheet.Paste
 
'Cut columns A:C and paste to columns D:F
Range("A1:C1").EntireColumn.Cut 
Range("D1").Select
ActiveSheet.Paste

Range Clear/Delete

The Clear function

The Clear function clears the entire content and formatting from an Excel Range. It does not, however, shift (delete) the cleared cells.

Range("A1:C3").Clear
Excel Range Clear function example
Excel Range Clear function example

The Delete function

Range Delete function
Range Delete function
The Delete function deletes a Range of cells, removing them entirely from the Worksheet, and shifts the remaining Cells in a selected shift direction.
Although the manual Delete cell function provides 4 ways of shifting cells. The VBA Delete Shift values can only be either be xlShiftToLeft or xlShiftUp.

'If Shift omitted, Excel decides - shift up in this case
Range("B2").Delete 

'Delete and Shift remaining cells left
Range("B2").Delete xlShiftToLeft  

'Delete and Shift remaining cells up
Range("B2").Delete xlShiftTop

'Delete entire row 2 and shift up
Range("B2").EntireRow.Delete

'Delete entire column B and shift left
Range("B2").EntireRow.Delete
Excel Range Delete - shifting cells
Excel Range Delete – shifting cells

Traversing Ranges

Traversing cells is really useful when you want to run an operation on each cell within an Excel Range. Fortunately this is easily achieved in VBA using the For Each or For loops.

Dim cellRange As Range
    
For Each cellRange In Range("A1:C3")
  Debug.Print cellRange.Value
Next cellRange
Although this may not be obvious, beware of iterating/traversing the Excel Range using a simple For loop. For loops are not efficient on Ranges. Use a For Each loop as shown above. This is because Ranges resemble more Collections than Arrays. Read more on For vs For Each loops here

Traversing the UsedRange

Excel Range - Worksheet UsedRange
Excel Range – Worksheet UsedRange
Every Worksheet has a UsedRange. This represents that smallest rectangle Range that contains all cells that have or had at some point values. In other words if the further out in the bottom, right-corner of the Worksheet there is a certain cell (e.g. E8) then the UsedRange will be as large as to include that cell starting at cell A1 (e.g. A1:E8). In Excel you can check the current UsedRange hitting CTRL+END. In VBA you get the UsedRange like this:

ActiveSheet.UsedRange
'same as
UsedRange

You can traverse through the UsedRange like this:

Dim cellRange As Range
    
For Each cellRange In UsedRange
  Debug.Print "Row: " & cellRange.Row & ", Column: " & cellRange.Column
Next cellRange
The UsedRange is a useful construct responsible often for bloated Excel Workbooks. Often delete unused Rows and Columns that are considered to be within the UsedRange can result in significantly reducing your file size. Read also more on the XSLB file format here

Range Addresses

The Excel Range Address property provides a string value representing the Address of the Range.

Excel Range Address property
Excel Range Address property

Syntax

Below the syntax of the Excel Range Address property:

Address( [RowAbsolute], [ColumnAbsolute], [ReferenceStyle], [External], [RelativeTo] )

Parameters

RowAbsolute
Optional. If True returns the row part of the reference address as an absolute reference. By default this is True.

$D$10:$G$100 'RowAbsolute is set to True
$D10:$G100 'RowAbsolute is set to False

ColumnAbsolute
Optional. If True returns the column part of the reference as an absolute reference. By default this is True.

$D$10:$G$100 'ColumnAbsolute is set to True
D$10:G$100 'ColumnAbsolute is set to False

ReferenceStyle
Optional. The reference style. The default value is xlA1. Possible values:

Constant Value Description
xlA1 1 Default. Use xlA1 to return an A1-style reference
xlR1C1 -4150 Use xlR1C1 to return an R1C1-style reference

External
Optional. If True then property will return an external reference address, otherwise a local reference address will be returned. By default this is False.

$A$1 'Local
[Book1.xlsb]Sheet1!$A$1 'External

RelativeTo
Provided RowAbsolute and ColumnAbsolute are set to False, and the ReferenceStyle is set to xlR1C1, then you must include a starting point for the relative reference. This must be a Range variable to be set as the reference point.

Merged Ranges

Excel Range Merge function
Excel Range Merge function
Merged cells are Ranges that consist of 2 or more adjacent cells. To Merge a collection of adjacent cells run Merge function on that Range.

The Merge has only a single parameter – Across, a boolean which if True will merge cells in each row of the specified range as separate merged cells. Otherwise the whole Range will be merged. The default value is False.

Merge examples

To merge the entire Range:

'This will turn of any alerts warning that values may be lost
Application.DisplayAlerts = False

Range("B2:C3").Merge

This will result in the following:

Excel Range Merged cells
Excel Range Merged cells

To merge just the rows set Across to True.

'This will turn of any alerts warning that values may be lost
Application.DisplayAlerts = False

Range("B2:C3").Merge True

This will result in the following:

Excel Range Merged cells across rows
Excel Range Merged cells across rows

Remember that merged Ranges can only have a single value and formula. Hence, if you merge a group of cells with more than a single value/formula only the first value/formula will be set as the value/formula for your new merged Range

Checking if Range is merged

To check if a certain Range is merged simply use the Excel Range MergeCells property:

Range("B2:C3").Merge

Debug.Print Range("B2").MergeCells 'Result: True

The MergeArea

The MergeArea is a property of an Excel Range that represent the whole merge Range associated with the current Range. Say that $B$2:$C$3 is a merged Range – each cell within that Range (e.g. B2, C3..) will have the exact same MergedArea. See example below:

Range("B2:C3").Merge
Debug.Print Range("B2").MergeArea.Address 'Result: $B$2:$C$3

Named Ranges

Named Ranges are Ranges associated with a certain Name (string). In Excel you can find all your Named Ranges by going to Formulas->Name Manager. They are very useful when working on certain values that are used frequently through out your Workbook. Imagine that you are writing a Financial Analysis and want to use a common Discount Rate across all formulas. Just the address of the cell e.g. “A2”, won’t be self-explanatory. Why not use e.g. “DiscountRate” instead? Well you can do just that.

Creating a Named Range

Named Ranges can be created either within the scope of a Workbook or Worksheet:

Dim r as Range
'Within Workbook
Set r = ActiveWorkbook.Names.Add("NewName", Range("A1"))
'Within Worksheet
Set r = ActiveSheet.Names.Add("NewName", Range("A1"))

This gives you flexibility to use similar names across multiple Worksheets or use a single global name across the entire Workbook.

Listing all Named Ranges

You can list all Named Ranges using the Name Excel data type. Names are objects that represent a single NamedRange. See an example below of listing our two newly created NamedRanges:

Call ActiveWorkbook.Names.Add("NewName", Range("A1"))
Call ActiveSheet.Names.Add("NewName", Range("A1"))

Dim n As Name
For Each n In ActiveWorkbook.Names
  Debug.Print "Name: " & n.Name & ", Address: " & _
       n.RefersToRange.Address & ", Value: "; n.RefersToRange.Value
Next n

'Result:
'Name: Sheet1!NewName, Address: $A$1, Value:  1 
'Name: NewName, Address: $A$1, Value:  1 

SpecialCells

SpecialCells are a very useful Excel Range property, that allows you to select a subset of cells/Ranges within a certain Range.

Syntax

The SpecialCells property has the following syntax:

SpecialCells( Type, [Value] )

Parameters

Type
The type of cells to be returned. Possible values:

Constant Value Description
xlCellTypeAllFormatConditions -4172 Cells of any format
xlCellTypeAllValidation -4174 Cells having validation criteria
xlCellTypeBlanks 4 Empty cells
xlCellTypeComments

-4144

Cells containing notes
xlCellTypeConstants 2 Cells containing constants
xlCellTypeFormulas -4123 Cells containing formulas
xlCellTypeLastCell 11 The last cell in the used range
xlCellTypeSameFormatConditions -4173 Cells having the same format
xlCellTypeSameValidation -4175 Cells having the same validation criteria
xlCellTypeVisible 12 All visible cells

Value
If Type is equal to xlCellTypeConstants or xlCellTypeFormulas this determines the types of cells to return e.g. with errors.

Constant Value
xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2

SpecialCells examples

Get Excel Range with Constants

This will return only cells with constant cells within the Range C1:C3:

For Each r In Range("A1:C3").SpecialCells(xlCellTypeConstants)
  Debug.Print r.Value
Next r

Search for Excel Range with Errors

For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
  Debug.Print r.Address
Next r
ActiveWorkbook vs ThisWorkbook

VBA ActiveWorkbook vs ThisWorkbook

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Often Excel / VBA Developers mix two distinguished properties in VBA when wanting to reference their Workbook – the first being the ActiveWorkbook object and the second being ThisWorkbook. In situations when running code on the same workbook you are working you needn’t really worry which reference you use as both will keep pointing at the same Workbook. The problem starts to arise when you work in an environment where there can be multiple Excel Workbooks and you may be executing code in one Workbook and wanting it to run/modify another open Workbook.

In today’s VBA Tip of the Day let’s explain the distinct difference between ActiveWorkbook vs ThisWorkbook.

ActiveWorkbook

The ActiveWorkbook is not just the visible Workbook as many would think, as you can just as well have multiple Workbooks open and placed side by side. However, only one Excel Workbook is considered Active in any point in time. Being Active is a top-most property – only the Workbook who’s window is currently selected is considered Active. See below for a precise definition:

Application.ActiveWorkbook or ActiveWorkbook in Excel / VBA:
Will return the Workbook object that represents the workbook in the active window (the window on top). The property will return Nothing if the Info window or the Clipboard window is the active window.

Using ActiveWorkbook may be risky sometimes see try running the following piece of code to see why:

Private Sub Workbook_Open()
    MsgBox Iif(Application.ActiveWorkbook Is Nothing, _
      "Current Workbook is not Active", _
      "Current Workbook is Active")
End Sub

ThisWorkbook

The ThisWorkbook property is much easier to understand as it simply references the Excel Workbook in which the VBA code is executing. For the full definition see below:

Application.ThisWorkbook or ThisWorkbook in Excel / VBA:
Will return the Workbook object that represents the workbook where the current macro code is running. Always return a Workbook object

ActiveWorkbook vs ThisWorkbook

If you are still confused about the difference between these two properties see below:

ActiveWorkbook vs ThisWorkbook
ActiveWorkbook vs ThisWorkbook

Hope that’s clear now!

Conclusions

Now that you can distinguish between both these properties it is important to keep a couple of things in mind:

  • On a daily basis use ThisWorkbook over the more erroneous ActiveWorkbook, when you expect your code to run on the same Workbook you are working on
  • Use ActiveWorkbook carefully, as it is a top-most property it might not always return a property if other Excel pop-up windows will be involved. Although you might need the ActiveWorkbook property when working with Excel AddIns (which execute code on the Active Workbook not your AddIn)
merge worksheets

Merging worksheets / tables in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...

Often we need to merge worksheets / workbooks containing lots of data. Imagine receiving periodical daily reports and wanting to quickly consolidate them to generate a weekly or monthly report… seems like a lot of work. Merging worksheets does not necessarily need to be hard… as long as you read through today’s post.

Merging worksheets with VBA

To exemplify the issue let’s consider a Workbook consisting of 2 worksheets (below named Sheet1 and Sheet2) with identical columns.

consolidate worksheets
Consolidating worksheets: Example data

What we want to do is merge these 2 worksheets into one consolidated worksheet. We may of course want to do this in multiple ways e.g. by:

  • Simply appending the worksheets to each other
  • Merging the data sets while removing duplicates

Here is the example result we want to achieve (Sheet2 appended to Sheet1 on a third separate Worksheet):

merging worksheets
Merging worksheets: Result

In today’s Tip of the Day I will exemplify how to merge worksheets / tables (any number) with an option of eliminating duplicates.

Merging worksheets with VBA

Let’s first consider a simple piece of VBA code. The procedure below can handle any number of Worksheets – including Worksheets from external Workbooks as long as you have the Workbook open and provide it within the array of Worksheets passed to the procedure.

VBA Code

Sub Merge(ws() As Worksheet, destWs As Worksheet, headerInFirstRow As Boolean, removeDuplicates As Boolean)
    'Clear destination worksheet
    destWs.UsedRange.EntireRow.Delete
    Dim pasteRange As Range, header As Range, firstFreeRow As Range, w As Variant, copyRange As Range
    'Paste header
    If headerInFirstRow Then
        Set header = ws(0).UsedRange.Cells(1).EntireRow:
        header.Copy destWs.Cells(1).EntireRow
    End If
    Set firstFreeRow = destWs.UsedRange.Rows(destWs.UsedRange.Rows.Count).Offset(1).EntireRow
    'Paste worksheets
    For Each w In ws
        Set copyRange = w.UsedRange.Rows("" & _
            IIf(headerInFirstRow, 2, 1) & ":" & w.UsedRange.Rows.Count)
        copyRange.Copy firstFreeRow.Cells(1, 1)
        Set copyRange = Nothing
        Set firstFreeRow = destWs.UsedRange.Rows(destWs.UsedRange.Rows.Count).Offset(1).EntireRow
    Next w
    'Remove duplicates
    If removeDuplicates Then
        Dim colArr As Variant, col As Long: ReDim colArr(0 To destWs.UsedRange.Columns.Count - 1)
        For col = 1 To destWs.UsedRange.Columns.Count
            colArr(col - 1) = col
        Next col
        destWs.UsedRange.removeDuplicates Columns:=(colArr), header:=IIf(headerInFirstRow, xlYes, xlNo)
    End If
    'Clean
    Set firstFreeRow = Nothing: Set w = Nothing: Set header = Nothing
End Sub

  

A now a simple example of this procedure being used assuming the example data I referenced above. Let’s assume data on is on worksheets Sheet1 and Sheet2, the destination Worksheet will be Sheet3.

Sub TestMerge()
    Dim ws(0 To 1) As Worksheet
    Set ws(0) = Sheet1
    Set ws(1) = Sheet2
    Merge ws, Sheet3, True, False
End Sub

Notice that the Merge procedure accepts the following parameters:

  • ws – an array of Worksheets
  • destWs – the destination Worksheet
  • headerInFirstRow – if true assumes that the header is in the first row of each source Worksheet
  • removeDuplicates – if true remove all duplicates after merging the Worksheets

Simple enough right? Not too long. If you want to optimize it for best performance read this.

Merging worksheets with MS Query (SQL)

Now let’s consider a second approach – using MS Query (SQL). This is my personal favorite as MS Query SQL code is short, does not require VBA and the destination Worksheet can be refresh by simply right clicking on the table and hitting refresh.

To create a MS Query feel free to use my free Excel SQL AddIn or follow the steps below:

merge worksheets excel
SQL AddIn: Merging worksheets

Follow the instructions below if you don’t have my free Excel SQL AddIn:

Open the From Microsoft Query Wizard

Data ribbon and then selecting From Other Sources and From Microsoft Query -> till the end be sure to hit View Data or Edit Query in Microsoft Query to be able to provide your own SQL query.

Proceed according to Wizard to edit SQL Query

As we want to merge several Worksheets within our current Excel Workbook we need to select Excel Files as the designated data source for our MS Query:

Select From Excel Files data source
Select From Excel Files data source

Next we need to select our Current Excel Workbook from the File Dialog:
Select your current Excel Workbook
Select your current Excel Workbook

Select the first Excel Worksheet you want to merge and click the right arrow > to drag it to the Columns in your Query section.
Select any Worksheet you want to merge to proceed
Select any Worksheet you want to merge to proceed

Next instead of Returning the Data hit the View data or edit Query in Microsoft Query radio button and proceed:
Proceed to edit the MS Query
Proceed to edit the MS Query

A new Window should pop-up. Look for the SQL button to modify the existing MS Query:
Hit SQL to edit MS Query
Hit SQL to edit MS Query

Modify the SQL Query to merge Worksheets

A new SQL Window should pop-up. You can modify any number of Worksheets using this approach. Below I am assuming I want to merge two Worksheets – Sheet1 and Sheet2. The MS Query need to therefore look like this:

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$]

Simply modify the existing MS Query in the SQL Window and hit OK.

What if you need to merge 3 Worksheets not just 1? Simply append an additional UNION ALL like this:

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$] UNION ALL SELECT * FROM [Sheet3$]

For additional Worksheets repeat this pattern

Return Data to selected Worksheet

Return Data to Excel Workbook
Return Data to Excel Workbook
Now the pleasant part – loading the Query data. Look for the Return Data button, select a cell where the MS Query is supposed to load and hit OK.

Comment on using the MS Query approach

Simple and quick – this approach is more efficient than any VBA code we may develop as it is handled by the OLEDB driver. MS Query (SQL) is also more efficient in handling large chunks of data as compared to other approaches.

Merging worksheets with PowerQuery

If you are not familiar with PowerQuery feel free to download it from Microsoft here. PowerQuery features a lot of neat data-crunching / BI features. Let’s see how can we leverage PowerQuery to do our job.

Create tables from each source Worksheet

First what we need to do is link our source tables to PowerQuery so we can create the output query.
consolidate2
Select the whole range of each source worksheet (CTRL + A)
and select the From Table option from the PowerQuery ribbon. For this example I have named Sheet1 as Table1 and similarly with Sheet2.

Merge the Worksheets

So now that we have our Tables configured in PowerQuery we can append the 2 (or more). Hit the Append button from the Combine group in the PowerQuery ribbon.

PowerQuery - Combine
PowerQuery – Combine

Next select Table1 from the first drop-down and Table2 from the second drop-down:

PowerQuery - Append
PowerQuery – Append

and hit OK

Note: When wanting to append multiple (>2) worksheets simply repeat this step. The first Table will need to be the Appended one from the remaining tables

That’s it! Similarly as with the MS Query example you can always easily refresh your query by right-clicking and hitting Refresh!

Summary

I hope you found these methods useful. On one last note I would like to share my thoughts on how these methods compare.

Personally I prefer to use MS Query (SQL) via my SQL AddIn. This sentiment may of course be partially as I used to work a lot with databases and am used to coding SQL. I know for most Excel users out there this is not the case. In such cases and if you only have a couple of Worksheets you need to merge on a regular basis I do suggest downloading the PowerQuery AddIn. It’s easy to use and also allows you to refresh your query easily. In all other scenarios feel free to use the VBA approach and simply customize your code to work best for you!

Let me know if you found this useful! And be sure to subscribe to more Tips on my Twitter or Facebook page!