If your Excel Sheets are stored on a shared location and you also want to tracked who opened that Excel and Saved it then use the following steps.

1. Create a Sheet named "Log" with the following format.

Timestamp Username

2. Copy the following code in the Workbook_Open Event

1. Private Sub Workbook_Open()
2. Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value _
3. = Format(Now(), "mm-dd-yy HH MM AMPM")
4. Worksheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value _
5. = Environ("UserName")
6. End Sub

read more

We have a data in the following format.

Column 1 Column 2 Column 3 Column 4 A D X B E Y C F Z G

There is no limit to the number of columns. Each Column has some data in the Cells below it.

The challenge is to transpose this data into this format.

A Column 1 B Column 1 C Column 1 D Column 2 E Column 2 F Column 2 G Column 2 X Column 4 Y Column 4 Z Column 4

read more

There is an excel sheet with the two columns, Employee ID and Employee Name. We also have a notepad file which has only the Employee ID of the employee.

Our task is to replace the Employee Id in the notepad with their corresponding Employee Name. The Employee Name needs to be picked from the excel file.

Our Macro will one by one go through each of the Employee Id in the excel sheet and search it in the notepad file, if Employee Id is found in the notepad it will be replaced by the Employee Name which is present right next to the Employee Id

Backup of the Notepad file

read more

This is the custom function, also known as User Defined Function that will count the number of cells in a specified range for the specified background color.

1. This function takes two arguments, first argument is the range of cells like A1:A2 and the second argument is cell that has the background color that you want to count.

2. How to use this function. Enter this in the cell where you want the count to be displayed.

=count_background_color(A2:A16,A5)

A2:A16 is the Range of Cells

A5 is the cell with the background color that you want to count.

Here is the function code.

read more