Working with workbooks is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Contents: Referencing workbooks by name Referencing workbooks by their status Referencing workbooks by when they were opened Assign a workbook to a variable Create new workbooks Opening workbooks Counting … Read more
Working with worksheets is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Referencing worksheets from the workbook ‘Reference a worksheet in another workbook Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet in the same workbook as the VBA code ThisWorkbook.Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet … Read more
Looping through every worksheet or every workbook is a very common action. One which you may be doing on a regular basis. The code below should set you on the right path. Loop through every worksheet Sub LoopThroughWorksheets() ‘Create a variable to hold the worksheet and also the output message Dim Ws As Worksheet Dim … Read more
At some point in your VBA life it will be necessary to display or re-order an array/list alphabetically (or reverse alphabetically). Unfortunately, VBA doesn’t have a function for sorting arrays automatically, but with a little bit of coding we can create a reusable function which would achieve something similar. Setting up the example This first VBA … Read more