My Excel skills are being tested quite often now. I had to work on a repetitive task to close a large list of support incidents. The input list of incident numbers were in an excel sheet and for each, I had to go to the incident management software and update some fields (primarily drop-down selections) prior to closure. As any gamer would attest, it is not much fun having to switch between two sides of the keyboard when also working with a mouse. Hence the act of
Ctrl+V would be interrupted by having to use the down arrow and get to the next cell. I decided to write an Excel Macro that would go to the next cell below, select it and copy it. If you think it would be tough, think again. Here is the code.
Public Sub NextRowCopy() Dim currRow As Integer currRow = ActiveCell.Row currRow = currRow + 1 Range("C" & currRow).Select Selection.Copy End Sub
I pasted this in a new module in my workbook (
Alt+F11 » Right Click Workbook in Project Explorer to the left and Insert » Module). As you would have guessed, the incident numbers were in column C. After saving the macro, I went back to the workbook. I pressed
Alt+F8 to see the
NextRowCopy macro in the popup window. Selected it and clicked on Options, assigned it a shortcut
Ctrl+Q (again for the same reason that Q was to the same side of the keyboard as C).
Once I updated the Incident, I would do a Ctrl+Q and I would have the next incident number to be worked on.
The And Some More part
Most of the fields to be updated in the system were dropdown selections, but there was a standard text to be updated as well. I couldn’t copy it from excel since it would change my cursor location and I didn’t want to have to open a notepad and cycle three windows for this. I searched for a multiple clipboard manager and decided to use QuickTextPaste. It lets you assign shortcuts to predefined text, run programs and more.
My task was completed and while looking back at my macro, I think I could have had excel do the copy of the predefined text as well. Let us say that the existing data was from columns A through N. I could add the text I needed to paste in Column Q and have the macro check where I was currently and move to the right cell.
Public Sub NextRowCopy() Dim currRow As Integer currRow = ActiveCell.Row currCol = ActiveCell.Column If currCol = 3 Then ' We are in the incidents column. Switch to column O currCol = "O" Else ' We are in new column O, switch to column C in the next row currCol = "C" currRow = currRow + 1 End If Range(currCol & currRow).Select Selection.Copy End Sub
Each time the macro ran, my clipboard had either the incident to be searched or the text to be entered to close the incident. Total time saved: 30 minutes