Get Excel sheet name and use as variable in macro

Question

I'm trying to find a way to use an Excel sheetname as a variable in a macro that I've written. Every month I deal with a workbook that is sent to me with 2 sheets. Part of the macro uses the 'Open File' interface to navigate to the folder and open the file.

The first sheet in the file is called 'Report', which is static. All I do with that is delete it (because I don't need it).

The second sheet could be called anything and that's the sheet upon which I need to run the macro. Is there a way to say:

shtName = %whatever_the_sheetname_is_called%

and then use the 'shtName' variable throughout the macro? I suppose getting this new filename as a variable would help as well.

Answer 1

in a Visual Basic Macro you would use

pName = ActiveWorkbook.Path      ' the path of the currently active file
wbName = ActiveWorkbook.Name     ' the file name of the currently active file
shtName = ActiveSheet.Name       ' the name of the currently selected worksheet

The first sheet in a workbook can be referenced by

ActiveWorkbook.Worksheets(1)

so after deleting the [Report] tab you would use

ActiveWorkbook.Worksheets("Report").Delete
shtName = ActiveWorkbook.Worksheets(1).Name

to "work on that sheet later on" you can create a range object like

Dim MySheet as Range
MySheet = ActiveWorkbook.Worksheets(shtName).[A1]

and continue working on MySheet(rowNum, colNum) etc. ...

shortcut creation of a range object without defining shtName:

Dim MySheet as Range
MySheet = ActiveWorkbook.Worksheets(1).[A1]