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
    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

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-2024

Updated:  01/23/2024 13:34
This page added:  28 July 2009