How to select multiple workbooks in a macro

[cat80]

Hello all,

I have a macro that is opening up several workbooks using hyperlinks. Each workbook has a different name(CT.xls, BB.xls etc). The macro copies a static range of data from each of these sheets and then and pastes it into a workbook called "Extractor". I only have 2 workbooks open at any time, the "Extractor" and the book I'm copying from.

The problem I have is that when I switch between workbooks, I am referring to the workbooks by their unique name which makes the macro very very long.

Is there a way to change this so I can either refer to them with a generic name in VBA or is there a way you can set up a macro to say "Select workbook that isn't active"?

Thanks for any help you guys can give me. I've attached my...rather large macro below. I'm sure it's really very messy.

Cat80

Sub SLEA()
'
' SLEA1 Macro

Application.DisplayAlerts = False
Sheets("Sheet1").Select
Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets("FP and LL MD and BoM Control").Select
Range("D11").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("SLEA Extractor.xlsm").Activate
Sheets("BOM").Select
Range("FreecellBOM").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("CT.xlsx").Activate
Sheets("Control Document").Visible = True
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWindow.SmallScroll Down:=-3
Application.CutCopyMode = False
Sheets("Control Document").Select
Range("A6:N499").Select
Range("A500").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination:=Range("A500:M1000"), Type:=xlFillDefault
Range("A500:M1000").Select
Range("A6").Select
ActiveSheet.Range("$A$6:$N$1000").AutoFilter Field:=5, Criteria1:="<>"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("SLEA Extractor.xlsm").Activate
Sheets("MD").Select
Range("Freecell").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("CT.xlsx").Activate
Sheets("Control Document").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.Close

 

[AlphaFrog]

This sets the hyperlinked workbook to the variable wb. Then you can refer to that variable when you want to refer to that workbook.

If later you set the wb variable to a different workbook, the same code that uses the wb variable as a reference will work with the different workbook.

Example:

Sub SLEA()
[COLOR="Green"]    '
    ' SLEA1 Macro[/COLOR]
    
    
    Dim wb As Workbook, rngCopy As Range, rngHyper As Range
    
    Application.DisplayAlerts = False
        
    For Each rngHyper In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        
        rngHyper.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
        Set wb = ActiveWorkbook [COLOR="Green"]' The workbook just opend with the hyperlink[/COLOR]
        
        With wb.Sheets("FP and LL MD and BoM Control")
            Set rngCopy = .Range("D11", .Cells(.Range("D11").End(xlDown).Row, .Range("D11").End(xlToRight).Column))
        End With
        rngCopy.Copy
        
        [COLOR="Green"]' Thisworkbook is the workbook with this macro in it.[/COLOR]
        ThisWorkbook.Sheets("BOM").Range("FreecellBOM").PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            
[COLOR="Green"]        '
        'more code here
        '[/COLOR]
            
        wb.Close SaveChanges:=False [COLOR="Green"]'Close the hyperlink workbook[/COLOR]
    
    Next rngHyper  [COLOR="Green"]'Open the next Hyperlink[/COLOR]
    
    Application.DisplayAlerts = True
    
End Sub

 

[Norie]

Why not just open the workbooks with Workbooks.Open?

 

[cat80]

AlphaFrog, thank you so much! Worked perfectly and was exactly what I was looking for! Thanks for taking the time to reply and help me out.

Norie, not sure I know how to write that piece of code. Each of the workbooks I want to extract from are stored a sharepoint and their names change from month to month, even if their locations do not. I thought the easiest thing would be to put their locations into a spreadsheet as hyperlinks and then connect to them using that.

Ideally I would want to open the workbooks that are contained in specific folder but I don't know how I would do that. Any ideas?

Thanks again to both of you,

Cat80

 

[AlphaFrog]

Ideally I would want to open the workbooks that are contained in specific folder but I don't know how I would do that. Any ideas?

This opens all xls files in a specific folder.

Sub SLEA()
    '
    ' SLEA1 Macro
    
    Dim wb As Workbook
    Dim strFile As String, strPath As String
    
    strPath = "[COLOR="Red"]C:\MyFolderPath\[/COLOR]"    'The source files folder path
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    strFile = Dir(strPath & "*.xls*") 'Get the 1st filename in the folder
        
    Do While strFile <> ""  'Loop until there are no more files to open
        
        Set wb = Workbooks.Open(Filename:=strPath & strFile) ' Open the workbook
                    
        '
        'more code here
        '
            
        wb.Close SaveChanges:=False 'Close the hyperlink workbook
        
        strFile = Dir() 'Get the next file name
    
    Loop  'Open the next file
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[cat80]

AlphaFrog, I am once again in your debt. Thank you yet again