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