Store Current Sheet Name As Variable

  • I have a workbook that is composed of forty (or so) worksheets containing data and a single summary worksheet that has command buttons that take the user to the appropriate data worksheet for their specific project. Each data worksheet is exactly the same in terms of where the header row starts, and the specific headings.

    On each data worksheet there is a command button that when clicked, builds a pivot table of the data for the current project. I have been able to create VBA code that hides the columns containing the data and then creates the pivot table in the empty (unhidden) columns n the same worksheet. This works fine, but is not a good solution from a useability standpoint.

    What I have been trying to do is when the command button is clicked I want to capture the name of the current worksheet as a variable in VBA, go to a separate worksheet to build the pivot table, and when the user clicks a ‘Review Data’ command button on the pivot table worksheet they are taken back to their original worksheet containing their data. Is this possible?

    I have searched for hours on this forum (and the web in general) for a solution and haven’t been able to find one (possibly because this is such a basic problem…) I’m just learning VBA so let me apologize in advance if this is a dumb question. Any coding help is greatly appreciated! Cheers, Pete

  • Re: Store current sheet name as variable and call it when on a different worksheet


    Dim strSheetName As String
    strSheetName = ActiveSheet.Name

    Or, you could store the sheet name in any cell like shown here
    Return an Excel Worksheet/Sheet Name to a Cell

  • Re: Store Current Sheet Name As Variable

    Thank you both for the help but I am having trouble applying it to my situation. I’m posting the code for the two macros I’m using to switch between the “data” sheet and the “pivot” sheet.

    Here is the code that builds the pivot cache, takes you to the sheet named “Summary View” and builds the pivot table:

    Here is the code for that is supposed to take you back to the original page the data is on:

    When I run the PivotByQuarter code from a worksheet containing data the pivot table gets created on the “Summary View” sheet without trouble. However when I run the ShowData code from the “Summary View” worksheet I get the following VB error: Run-time error ‘9’, Subscript out of range with the Sheets(DataSheetName).Activate line highlighted when I click debug. Any idea what I’m doing wrong?

  • Re: Store Current Sheet Name As Variable

    I've been working at this since posting the last update. If I add the line "Sheets(DataSheetName).Activate" to the end of the PivotByQuarter code it works as expected. Pivot table is created on sheet "Summary View" and then you are returned to the original sheet containing data. It looks like my problem is that I store the current sheet name as a variable when running the PivotByQuarter code, but then when I try to call that saved variable from another module, it's no longer saved or available for use. How do I store a variable in one module and then call that stored value in another module? Please help... Thanks in advance.
    (If this should be a new post, please let me know. It's related to my original title/question, but I don't want to violate forum rules. Thanks)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!