VBA - Setting Workbook Names After Opening Multiple Workbooks

[beartooth91]

The below vba works.

Sub Open_WB()
' Opens each workbook in the Standard-Format IO Lists subfolder
Dim SfFolder As String, SfList As String
SfFolder = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists")
SfList = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists" & "\*.xlsx")

Do While SfList <> ""
  Workbooks.Open FileName:=ThisWorkbook.Path & "/Standard-Format IO Lists" & "\" & SfList
  SfList = Dir
  
Loop
End Sub

The result is I have 6+ open workbooks that I want to copy out of. Is there a **simple** vba way to reference the open workbooks, either by number or name? The number of workbooks - which I will have to routinely copy out of - dynamically grows and shrinks, but will probably have 30+ workbooks to copy out of as this project moves along.

 

[beartooth91]

[Edit] It occurs to me the better way to do this - which I need help with - is to open/copy out of/close one workbook at a time before moving to the next......
How can I refer to each open workbook name without hard coding?

Sub Open_WB()
' Opens each workbook in the Standard-Format IO Lists subfolder
Dim SfFolder As String, SfList As String
SfFolder = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists")
SfList = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists" & "\*.xlsx")

Do While SfList <> ""
  Workbooks.Open FileName:=ThisWorkbook.Path & "/Standard-Format IO Lists" & "\" & SfList
  SfList = Dir

' vba to copy out of this workbook...how to reference/refer to the open workbook without hard coding?
'vba to close workbook

Loop
End Sub

 

[Logit]

Untested here. Advise if this works.

Sub Open_WB()
    ' Opens each workbook in the Standard-Format IO Lists subfolder
    Dim SfFolder As String, SfList As String
    Dim OpenedWorkbook As Workbook
    
    ' Define the folder and the file list pattern
    SfFolder = ThisWorkbook.Path & "\Standard-Format IO Lists"
    SfList = Dir(SfFolder & "\*.xlsx")     '<-- change file type as required
    
    ' Loop through all files in the folder
    Do While SfList <> ""
        ' Open the workbook and set a reference to it
        Set OpenedWorkbook = Workbooks.Open(FileName:=SfFolder & "\" & SfList)
        
        ' Perform actions on the opened workbook
        ' For example, copying a sheet:
        OpenedWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        
        ' Close the workbook (save changes if needed)
        OpenedWorkbook.Close SaveChanges:=False
        
        ' Move to the next file
        SfList = Dir
    Loop
End Sub

 

[beartooth91]

Untested here. Advise if this works.

Sub Open_WB()
    ' Opens each workbook in the Standard-Format IO Lists subfolder
    Dim SfFolder As String, SfList As String
    Dim OpenedWorkbook As Workbook
   
    ' Define the folder and the file list pattern
    SfFolder = ThisWorkbook.Path & "\Standard-Format IO Lists"
    SfList = Dir(SfFolder & "\*.xlsx")     '<-- change file type as required
   
    ' Loop through all files in the folder
    Do While SfList <> ""
        ' Open the workbook and set a reference to it
        Set OpenedWorkbook = Workbooks.Open(FileName:=SfFolder & "\" & SfList)
       
        ' Perform actions on the opened workbook
        ' For example, copying a sheet:
        OpenedWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
       
        ' Close the workbook (save changes if needed)
        OpenedWorkbook.Close SaveChanges:=False
       
        ' Move to the next file
        SfList = Dir
    Loop
End Sub

Tested and works. Thanks!

 

[Logit]

Wonderful ! Glad you have an answer.