What is the syntax to retrieve all open workbooks in Excel?

'wb.Name holds the name of the open workbook
Dim wb As Workbook
For Each wb In Application.Workbooks
 <code here with involving wb.Name>
Next wb
'example
Dim wb As Workbook
For Each wb In Application.Workbooks 'for each open workbook, pass the name to lookupEGA()
   MsgBox wb.Name
   lstWorkbooks.AddItem wb.Name
Next wb

 

Advertisements

What is the syntax to access the right-most sheet?

Worksheets(Worksheets.Count)
'example
Worksheets(Worksheets.Count).Range("A1:A4") = "hello"
Worksheets(Worksheets.Count).Range("D1") = "world"
Worksheets(Worksheets.Count).Cells(2, 4).Value = "goodbye"

What is the syntax to write to another workbook?

'to retrieve data
Workbooks("<workbook file name>").Worksheets("<sheet name>").Range("<your range>").Value
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Workbooks("<workbook file name>").Workbooks("Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Workbooks("example.xlsm").Worksheets("Example").Range("A1:A4") = "hello"
Workbooks("book3.xlsx").Worksheets("Example").Range("D1") = "world"
Workbooks("book3.xlsx").Worksheets("Example").Cells(2, 4).Value = "goodbye"

What is the syntax to return the index of an active multipage?

Me.<multi-page name>.Value
#returns an integer value
'example for multipage named "mtp"
Private Sub mtp_Change() 'called whenever user goes to new multipage
   If Me.mtp.Value = 1 Then 'if user opens the "Show Guest List" page
        Call loadGuestLists
   End If
End Sub