Excel VBA

Jump Between Multiple Workbooks

Sub GetStandardDirs()
    Dim MainWork    	As String
    Dim CurWork     	As String
    Dim currRow     	As Long
    Dim PasteRow    	As Long
    Dim LastRow     	As Long
    Dim MemberCell  As Variant
    
    MainWork = "acronyms.XLS"    'name
    
    CurWork = ActiveWorkbook.Name
    Windows(CurWork).Activate    'activate
    ActiveCell.SpecialCells(xlLastCell).Select
    LastRow = ActiveCell.Row     'name the last row
    Range(Cells(1, 1), Cells(LastRow, 1)).Select     'get work area
    Windows(CurWork).Activate
    Application.ScreenUpdating = False
    '------ Find Directories
    For Each MemberCell In Selection
        If MemberCell.Text = "Directory" Then
            currRow = MemberCell.Row
            ActiveSheet.Cells(currRow, 3).Select
            Selection.Copy
            Windows(MainWork).Activate       'Jump to other workbook
            PasteRow = Application.CountA(ActiveSheet.Range("A:A")) + 1
            Cells(PasteRow, 1).Select
            ActiveSheet.Paste
            Windows(CurWork).Activate   'Grab working workbook
        End If
    Next
   ActiveWorkbook.Save
   MsgBox "Hey, I'm done!"
End Sub

I followed someone into a project recently where he named fields with his favorite cartoon characters.