Excel VBA copy contents of protected worksheet

This Excel VBA tutorial explains how to copy contents of protected worksheet and then paste contents in another worksheet. You may also want to read: Excel VBA protect worksheet with password Excel VBA hide worksheet with password (xlVeryHidden) Copy contents of protected worksheet Some authors would protect worksheets with password so that other people cannot modify and not even able to select the cells. I usually do it when I setup templates for users to fill in the data but I don't...
More

Excel VBA convert date period to date row

This Excel VBA tutorial explains how to convert date period to date row. Excel VBA convert date period into date row Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period Begin Date End Date 1/1/2018 1/3/2018 However some system store one date per row. Begin Date End Date 1/1/2018 1/1/2018 2/1/2018 2/1/2018 3/1/2018 1/3/2018 ...
More

Excel VBA filter value then copy data to new worksheet

This Excel VBA tutorial explains how to automate AutoFilter to filter value and then copy data to new worksheet or copy data to new workbook. You may also want to read: Excel VBA copy each worksheet to new workbook Access VBA auto generate mass report by group to Excel Filter value then copy data to new worksheet I find it very difficult to come up with a suitable title and key words for this post. I will try to describe what this Macro does with an example. Suppose you have a staf...
More

Access Excel VBA sort Array items

This Access Excel VBA tutorial explains how to sort Array items in VBA in ascending order / descending order. You may also want to read: Use VBA Excel Function to sort data in ascending order Access Excel VBA sort Array items In an Array, you may have stored items that you want to sort. For example, if you store a name list, you may want to sort them in alphabetical order (Apple, Banana, Cat...).  If you store a number list, you may want to sort them in ascending order (100, 200, 400, 70...
More

Excel check duplicate values using Conditional Formatting

This Excel tutorial explains how to check duplicate values in Excel spreadsheet by highlighting duplicate values using Conditional Formatting. You may also want to read: Excel assign sequence number to duplicate records Excel delete duplicated data in consecutive rows Excel check duplicate values Back in Excel 2003, it was a pain to identify duplicate values. What I used to do was to sort the values in ascending order and then added an assist column to check if the value on the  left  e...
More

Excel convert data from column to row

This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row). You may also want to read: Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet Excel convert data from column to row Different database have different structure. Some database put similar data in the same column with different rows, while some database put data in different columns. .Let's take an example. Typ...
More

Add sorting in Access Table field

This Access tutorial explains how to add sorting in Access Table field. You may also want to read: Sort Data in Access Report Add Auto number in Access Query Add sorting in Access Table field It is easy to add sorting in Access Query field in Query Design View (set in the "sort" row), but to add sorting in Access Table field is done differently. Let's say we have the following employee table, where the Employee ID is not sorted. Although you can click on the arrow next the...
More

Excel Exact Function – compare case sensitive text / case sensitive lookup

This Excel tutorial explains how to compare case sensitive text using Excel Exact Function and how to perform case sensitive lookup / vlookup. Excel Exact Function to Compare case sensitive text / case sensitive lookup (vlookup) In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, "Peter" is considered same as "peter" in Excel. Vlookup also shows matching result regardless of case sensitivity. In order to compare case sensitive text, we...
More

Access Case Sensitive Join Table (Inner Join, Left Join)

This Access tutorial explains how to perform case sensitive join table, such as case sensitive inner join and case sensitive left join. You may also want to read:Access StrComp Function to Compare text (case sensitive comparison) Access Case Sensitive Join Table In Microsoft Access, comparison of text is not case sensitive. For example, Access considers "AAA" and "aaa" are the same. To compare String in Expression, please refer to my previous post about how to use StrComp Function. The m...
More

Access StrComp Function to Compare text (case sensitive comparison)

This Access tutorial explains how to use Access StrComp Function to compare text, both case sensitive and case insensitive comparison. You may also want to read: Access Case Sensitive Join Table (Inner Join, Left Join) Access StrComp Function to Compare text (case sensitive comparison) In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, if you type formula ="A"="a" The formula returns TRUE. Vlookup also shows matching result even tho...
More