Excel VBA

css navigation by Css3Menu.com

Jump Between Multiple Workbooks

I needed an example for a friend where I jumped back and forth thru multiple workbooks and sheets. Probably not the best way but good for a “quick and dirty”.
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
    LastRow = ActiveCell.Row     'name the last row
    Range(Cells(1, 1), Cells(LastRow, 1)).Select     'get work area
    Application.ScreenUpdating = False
    '------ Find Directories
    For Each MemberCell In Selection
        If MemberCell.Text = "Directory" Then
            currRow = MemberCell.Row
            ActiveSheet.Cells(currRow, 3).Select
            Windows(MainWork).Activate       'Jump to other workbook
            PasteRow = Application.CountA(ActiveSheet.Range("A:A")) + 1
            Cells(PasteRow, 1).Select
            Windows(CurWork).Activate   'Grab working workbook
        End If
   MsgBox "Hey, I'm done!"
End Sub

Just remember to make the names meaningful so you don't get lost in the code.

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

© 2009-2017

Updated:  12/10/2017 15:42
This page added:  28 July 2009