| |
|
|
| |
Randomizer Code |
|
| |
Link to Randomize Subroutine
|
This code and spreadsheet can be used to randomize a column or columns in Excel. It was developed to enable for a list of people to be randomized for a prize drawing.
To use this module download it and then import it into your spreadsheet via the VBA developer. To call it use:
Randomize
For a simple, working example of the code, please download the
Randomize macro enabled spreadsheet.
For a demonstration, please click here
|
|
| |
|
|
| |
Coin Collection |
|
| |
Coin Collction Workbook
|
This macro enabled spreadsheet will assist in keeping track of a coin collection. It is specifically aimed at US currency.
It must be saved as a macro-enabled file (xlsm) as it contains several macros.
The spreadsheets have now been modified to include a coin count on the $$Totals page and to enable sorting of the coin worksheets by date and mint.
|
|
| |
|
|
| |
How to Implement a Progress Display |
|
| |
|
Due to the more complex nature of this code we have decided to give it a page of it's own. It can be accessed by clicking either the picture on the left or here. |
|
| |
|
|
| |
How to Fill a ListBox on a Excel Panel |
|
| |
ListBox Example Workbook
Link to Userform Zip file
|
This sample workbook contains all the code needed to demonstrate a method for filling (and processing) a ListBox on a Userform. It also demonstrates how to do this using a list contained on a spreadsheet named "Items" with a column headed "ITEM LIST".
The advantage of using this method is that no VBA code changes are needed to change the item list.
The userform should be imported into your project via the Developer tab on Excel".
The code will work but you have to change it to meet your own requirements. |
|
| Â |
| |
|
|
| |
How to Use Filters and "Go to Special" to Delete Selected Records |
|
| |
For a demonstration of how to perform
filtering and how to delete selected rows.
Click here.
|
Sometimes you need to delete rows from an Excel spreadsheet.
You could do this by individually one row at a time which is both time consuming and error prone or you could use filters and Excel's "Go to Special" to achieve this quickly and accurately.
A video link of how to achieve this is available alongside this text. |
|
| |
|
|
| |
Merging Excel WorkSheets |
|
| |
The format of the call is:
Call MergeSheets(FromSheet, ToSheet, Column on Target sheet for starting row , Starting row on From Sheet , Copy Only Value as False)
Sample calls:
Call MergeSheets("Merge2", "Merge1", 5 , , True)
The above call will copy the values in all the rows (starting at row 1) from Merge2 to Merge1 using the number of rows in column 5 of Merge2 to set the area size.
Call MergeSheets("Input", "Output")
The above call will copy all the rows (including the formatting) from Input to Output using the number of rows in column 1 of Output to set the area size (this is the default).
The MergeSheets subroutine can be downloaded here |
Sometimes you need to be able to merge WorkSheets using VBA code rather than doing it manually.
The MergeSheets subroutine (which can be downloaded here), can be used to merge one WorkSheet into another.
The subroutine can be called with optional parameters to specify the column to be used to specify the row to be used to start merging to, the starting row of where to merge from and whether only the values are to be copied during the merge.
If these parameters are not provided then column 1 is used for determining the row to start the merge on, row 1 will be used to determined the row to start from on the from sheet and all formatting and values will be copied to the target sheet. |
| |
|
|
| |
Merging a WorkSheet Into a Table |
|
| |
The format of the call is:
Call MergeToTable(SheetFrom, SheetTo, TableName, Optional ToColumnToUse, Optional FromRow, Optional SortColumn)
Sample calls:
Call MergeToTable("Merge2", "Merge1", "MyTable", 5,, "A5")
The above call will copy all the rows (starting at row 1) from sheet Merge2 to a table named MyTable on sheet Merge1 using the number of rows in column 5 of Merge2 to set the area size.
Additional the table will be sorted using the values in column 5 of the table.
Call MergeToTable("Input", "Output", "MyTable")
The above call will copy all the rows from worksheet Input to worksheet Output using the number of rows in column 1 of Output to set the area size (this is the default).
The MergeToTable subroutine can be downloaded here |
Sometimes you need to be able to merge WorkSheets using VBA code rather than doing it manually.
The MergeToTable subroutine (which can be downloaded here), can be used to merge the contents of one worksheet into a table on another worksheet.
The subroutine can be called with optional parameters to specify the column to be used to specify the column on the from sheet to be used to define the starting row to use to merge from, the starting row of where to merge from and, the column to sort the table (if required).
If these parameters are not provided then column 1 is used for determining the row to start the merge on, row 1 will be used to determined the row to start from on the from sheet and no sorting will take place.
The number of columns in the table and on the from worksheet must be the same. |
| |
|
|
| |
Public (Bank) Holiday Schedule Generator |
|
| |
Link to Holiday Generator Workbook
Holidays listed include:
- New Year's Holidays
- Easter Holidays
- MLK Day
- Christmas Holidays
- Thanksgiving
- Independence Day
|
The workbook "Holidays.xlsm" will generate a list of United States and United Kingdom public holidays (aka Bank holidays) for seven years from a date specified. The United Kingdom will also include Scotland and Ireland specific holidays.
It will calculate the actual date as well as the "Observed On" date as sometimes these are different.
You can specify any year between 2024 and 2051 and it will generate the list for that year and seven additional years.
|
