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.
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]
Source: Stackoverflow.com