Store Current Sheet Name As Variable

lackeye · Aug 27th 2008

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

Dave Hawley · Aug 27th 2008

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

try;

Code

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

Anonymous · Aug 28th 2008

Re: Store Current Sheet Name As Variable

Dave,
I think you meant to type ActiveSheet.Name (rather than without the period). Hence

Code

Dim strSheetName As String 
strSheetName = ActiveSheet.Name

Dave Hawley · Aug 28th 2008

Re: Store Current Sheet Name As Variable

Oops, thanks Tom. All fixed.

lackeye · Aug 28th 2008

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:

Code

Sub PivotByQuarter()


    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalCol As Long
    Dim FinalRow As Long
    Dim DataSheetName As String
    Dim PTsheet As Worksheet
    
    Set PTsheet = Sheets("Summary View")
    Set WSD = ActiveSheet
    
          
    Application.ScreenUpdating = False


'Turn worksheet protection off
    WSD.Unprotect
 
'Define input area and set up a Pivot Cache (FinalCol assumes data starts in row 10)
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(10, Application.Columns.Count).End(xlToLeft).Column


'Assumes data begins on row 10 (To do this the range is offset from Row 1 by 9 rows)
    Set PRange = WSD.Cells(1, 1).Offset(9).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
    
'Create the Pivot Table from the Pivot Cache starting on row 10 and offset from last data column by 2 columns to the right
    DataSheetName = ActiveSheet.Name
    PTsheet.Unprotect
    PTsheet.Activate
    
    Set PT = PTCache.CreatePivotTable(TableDestination:=PTsheet.Cells(10, 1), TableName:="PivotTable1")
    
'Turn off pivot table updating while building the table
    PT.ManualUpdate = True
    
'Set up the Row, Column, and Page Fields
        PT.AddFields RowFields:=Array("Program Name", "Dept", "Location", "Project Name", "Group"), ColumnFields:="Data"
    
'Supress all subtotals except for "Program"
    PT.PivotFields("Project Name").Subtotals(1) = True
    PT.PivotFields("Project Name").Subtotals(1) = False
    
    PT.PivotFields("Group").Subtotals(1) = True
    PT.PivotFields("Group").Subtotals(1) = False
    
'Use Tabular layout for pivot table
    PT.RowAxisLayout xlTabularRow


'Supress blanks from showing in table for Row Headings
    With PT.PivotFields("Program Name")
        On Error Resume Next
        .PivotItems("(blank)").Visible = False
    End With
    
    With PT.PivotFields("Project Name")
        On Error Resume Next
        .PivotItems("(blank)").Visible = False
    End With
    
    With PT.PivotFields("Group")
        On Error Resume Next
        .PivotItems("(blank)").Visible = False
    End With
    
    
'Set up the Sum Values field (aka Data Fields)
    With PT.PivotFields("08Q1")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""?_);_(@_)"
        .Caption = "'08 Q1"
    End With
        
    With PT.PivotFields("08Q2")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 2
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""?_);_(@_)"
        .Caption = "'08 Q2"
    End With
   
'Ensure that we get zeroes instead of blanks in the data area
    PT.NullString = "0"


'Apply a Table Style to pivot table
    PT.ShowTableStyleColumnHeaders = True
    PT.ShowTableStyleRowHeaders = True
    PT.ShowTableStyleColumnStripes = True
    PT.TableStyle2 = "PivotStyleMedium2"


'Allow Excel to temporarily calculate and draw the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
'Turn screen updating back on so final result is shown on screen
    Application.ScreenUpdating = True
        
End Sub

Display More

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

Code

Sub ShowData()
    Dim WSD As Worksheet
    Dim PT As PivotTable
    Dim DataSheetName As String
    Dim PTsheet As Worksheet
    
    Set PTsheet = Sheets("Summary View")
    Set WSD = ActiveSheet
            
    Application.ScreenUpdating = False


' Delete any prior pivot tables
    For Each PT In PTsheet.PivotTables
        PT.TableRange2.Clear
    Next PT


    Sheets(DataSheetName).Activate
    
    Range("A11").Select


    Application.ScreenUpdating = True
          
End Sub

Display More

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?

lackeye · Aug 28th 2008

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)

Dave Hawley · Aug 28th 2008

Re: Store Current Sheet Name As Variable

The offending variable (<<HOVER OVER) should be dimmensioned at the Module level or Project level.

lackeye · Aug 28th 2008

Re: Store Current Sheet Name As Variable

Thanks for the fast reply Dave. This was the problem. Works perfectly now. Have a great night!

Dave Hawley · Aug 28th 2008

Re: Store Current Sheet Name As Variable

Thanks, you too.

Store Current Sheet Name As Variable

Participate now!

Share