How can I activate the sheet that is running code from a VBA module?

Hi

This will hopefully be fairly straight forward.

I would like to reference the current worksheet in vba code so that I can activate the sheet that is running the code. I need to do this as I am switching between 2 wordbooks and the workbook name in 1 instance is not fixed.

I tried retrieving the workbook name like this:

Function WorkbookName() As String
WorkbookName=ThisWorkbook.Name
End Function

but could not work out how to implement that in code such as:

Windows("filepath.xlsm").Activate

Windows.WorkbookName.Activate

The above does not work and I tried a few variations

I did not know how to deal with the quotation marks and parenthesis.


I have another option, the workbook name is contained in a cell on a worksheet within the workbook, the name is in text form minus the .xlsm part so this might be of use.



Can anybody possibly help me achieve the desired outcome?



Many thanks in advance, your wisdom is always appreciated.

Regards,

Michael.

Hi Mick86,

Function WorkbookName() As String
WorkbookName=ActiveWorkbook.Name
End Function

Hello and thank you for the speedy reply.

So with the above code do I can place that as a function then call in from another sub using

WorkbookName.Activate

???

I tried this but it didn't seem to work. I'm not sure if I can include the Function as part of the Sub. I assume if this function runs before I switch worksheets it retains memory of the earlier active worksheet as I need to call the function while a different sheet is active.

My current coding set up is as follows:

Option Explicit
Option Compare Text
 
Function IsWbOpen(wbName As String) As Boolean
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
        If Workbooks(i).Name = wbName Then Exit For
    Next
    If i <> 0 Then IsWbOpen = True
End Function
 
 
 '\\ Example
Sub Pre_Subtract()
WorkbookName
    Dim wb As Workbook, strName As String, strPath As String
    strName = "My File name.xlsm"
    strPath = "C:\excel\"
    If IsWbOpen(strName) Then
     Call Subtract
    Else
        Set wb = Workbooks.Open(strPath & strName)
        Application.WindowState = xlMinimized
        Call Subtract
    End If
End Function

    Workbooks(1).Activate  '<<== This is where I would like to reference the workbook running the code

Various cell functions

   Windows("PreKnowFileName.xlsm").Activate

Various cell functions

    Application.WindowState = xlMinimized
    Workbooks(1).Activate  '<<== This is where I would like to reference the workbook running the code
    Application.WindowState = xlMaximized
End Sub

I use 1 module to check if a certain workbook is open or not then call a macro to make some changes, then I need to return to the original worksheet which could have various names and as such I need to reference it in a cleaver fashion. My current index reference only works if I open the files in the right order.

Something Like this:

Set wb = Workbooks(WorkBookName)

Without the function:

Set wb=ActiveWorkbook

Because the function gives you the Active Book Name - it's already activated!

Hi mick86,

To get a reference to the original workbook:

Dim wo As Workbook             ' wo for original workbook
Set wo=ActiveWorkbook

right at the start!

You're welcome!