Automatic Excel Macros for Novices

Working at Macy’s in an ICQA department, I used an excel file for data entry without using automatic excel macros.

What is an excel macro? This website at excel graduate clearly explains what a macro is with a basic introduction.

I processed damaged jewelry merchandise at Macy’s that had been prepared to go out for repairs. Everyday I enter the data under the same columns without automatic excel macros and I kept thinking to myself:

Is there a way to streamline the productivity of this file with automation?

So each day I decided to take some time to look up codes online a little bit at a time so as to not disrupt my production. So, the first thing I did was to find a formula for automatically entering line numbers, but I had to copy the formula throughout the column with the Fill Handle and the number of entries were nearly infinite. I kept at the search, because I had other automation ideas in mind. I wanted the log date to automatically insert with automatic excel macros when I pressed the Tab or right arrow key, so I eventually found this and customized it for my needs. I wasn’t finished with my automation ideas. I then went back to my original idea to insert the line numbers, but to automate them through a macro so that when I completed inserting data into the last cell, the line number would appear. I succeeded, but I was still not satisfied and wanted to add yet more macros. The next macro I wanted to create was one that when I completed the last cell, the cursor would activate the first cell on the next line. I accomplished this, and yet I still wasn’t satisfied. I wanted to do one more thing. I decided that instead of tapping the tab key twice to enter the date automatically and fill in the last cell, I decided that there must be a macro to skip the date cell as the date was inserted and land on the last cell. I found the code and made my modifications. Meanwhile I learned some basics to writing macros along the way.

If you don’t already know how to program Visual Basic Script for excel, then I recommend this book Excel VBA: for Non-Programmers Kindle Edition (Amazon)

I found all of the automatic excel macros that I wanted, but remained dissatisfied. For one column, I use a scanning gun to scan a barcode. A scanning gun is essentially a keyboard that hits return as soon as you release the trigger. So, I wanted the cursor to automatically move to the right and wondered if there was a macro for that. I still haven’t found a macro for it, so I chose the easiest route. I want into the advanced settings and made the switch in there. For those of you who don’t know how to do that, go into File > Options > Advanced > Under Editing Options change the setting for “After pressing Enter, move selection Direction” to Right.

I estimated that originally, it took about 16 seconds to insert all of the data per line. After all of these modifications, I managed to save 9-10 seconds per line. That’s equivalent to roughly 600 lines per hour.

I still have one more idea. There is certain data on our mainframe that I would like to scrape into the data file automatically. At this time, I go to the mainframe to copy down the data. If I can accomplish this task, then I could potentially save up to 12 seconds per line and spend maybe 4 seconds typing on each line. I just have to look around the company to see if anyone has already created this macro.

Another idea that might be quicker to solve is to automatically enter the package number in the last cell of each line. So, when I type in the description, press tab, both the date and the package number are inserted and the starting cell on the next line is activated. My colleagues might be impressed. I’ll update this posting if I add these macro thoughts.

This code is the function for numbering lines semi-automatically, but had to copy it to every cell in the column.

=IF(G2="","",COUNTA(G2:$G$2))

Here is my completed list of automatic excel macros in the data file, along with notations on what each one is for.

Private Sub Worksheet_Change(ByVal Target As Range)
' automatically enter today's date in date column
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E495:E1041")) Is Nothing Then
        With Target(1, 2)
        .Value = Date
        .EntireColumn.AutoFit
        End With
    End If
    
    
    
' automatically create line numbers in cells in column A after data is filled in column G (if not empty). Numbers will remain after data is deleted
    
    Dim cell As Range
    If Not Intersect(Range("G:G"), Target) Is Nothing Then
    For Each cell In Intersect(Range("G:G"), Target).Cells
    If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
    Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1

    End If

    Next

    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Skip Log In Date column F when pressing Tab or right arrow

    Static sRg As Range
    Dim ColumnOffset As Integer
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, ([F:F])) Is Nothing Then
        With Target
        Application.EnableEvents = False
        If Not sRg Is Nothing Then
            If sRg.Column < .Column Then
                ColumnOffset = 1
            ElseIf .Column <> 1 Then
                ColumnOffset = -1
            End If
        Else
            ColumnOffset = 1
        End If
        .Offset(, ColumnOffset).Select
        Application.EnableEvents = True
        End With
    End If
    Set sRg = ActiveCell
    
' start a new line and return active cursor to cell in column B
    
    If Not Target.Column = 8 Then Exit Sub
    Cells(Target.Row + 1, 2).Select
    
    
End Sub

I decided to take this a step further and learn about placing macros modules and then calling upon the macros from the sheet.

Here is my module:

Sub My_Macro1(Optional ByVal Target As Range)
' Thomas Stone
' 2020-9-2 update
' automatically enter today's date in date column

On Error GoTo 10
  Debug.Print Target.Column
  GoTo 20
10:
Set Target = Selection
20:
    If Target.Cells.Count > 1 Then Exit Sub
    
        If Not Intersect(Target, Range("E495:E1041")) Is Nothing Then
        With Target(1, 2)
        .Value = Date
        .EntireColumn.AutoFit
        End With
        
    End If
    
    
' Thomas Stone
' 2020-9-2 update
' automatically create line numbers in cells in column A after data is filled in column G (if not empty). Numbers will remain after data is deleted
' Future edit - If data in Column E has data in it, then fill in control number and line number in a sequence.
    
    Dim cell As Range
    If Not Intersect(Range("G:G"), Target) Is Nothing Then
    For Each cell In Intersect(Range("G:G"), Target).Cells
    If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
    Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1

    End If

    Next

    End If

   
End Sub

Sub My_Macro2(Optional ByVal Target As Range)

' Thomas Stone
' 2020-9-2 update
' Skip Log In Date (column F) when pressing Tab or right arrow

    Static sRg As Range
    Dim ColumnOffset As Integer
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, ([F:F])) Is Nothing Then
        With Target
        Application.EnableEvents = False
        If Not sRg Is Nothing Then
            If sRg.Column < .Column Then
                ColumnOffset = 1
            ElseIf .Column <> 1 Then
                ColumnOffset = -1
            End If
        Else
            ColumnOffset = 1
        End If
        .Offset(, ColumnOffset).Select
        Application.EnableEvents = True
        End With
    End If
    Set sRg = ActiveCell
    
' Thomas Stone
' 2020-9-2 update
' start a new line and return active cursor to cell in column B
    
    If Not Target.Column = 8 Then Exit Sub
    Cells(Target.Row + 1, 2).Select
    
    
End Sub

Here is the new code inside my sheet that calls upon the macros in the module.

'Thomas Stone
'2020-9-2 Update
'Call on macros

Option Explicit


Sub Worksheet_Change(ByVal Target As Range)
    Call My_Macro1(Target)
    
End Sub

Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call My_Macro2(Target)
End Sub

Don’t forget to pick up a copy of Excel VBA: for Non-Programmers Kindle Edition (Amazon)

I later had an idea to learn to scrape data from the mainframe, but I couldn’t pick anyone’s brains about it because the original authors of the files were no longer employed with Macy’s. I gave up on that and then moved on by working for another company in their tech support department. I decided to share this information because tom-stone.com is a good site for learning a new tip here and there.