15

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.

Improve this question

1 Answer 1

Reset to default
36

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]
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Agreed. That's just what I was looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.