Announcement

Collapse
No announcement yet.

Store Current Sheet Name As Variable

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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
    Last edited by Dave Hawley; August 28th, 2008, 09:50.

    Comment


    • #3
      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
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Re: Store Current Sheet Name As Variable

        Oops, thanks Tom. All fixed.

        Comment


        • #5
          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
          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
          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?

          Comment


          • #6
            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)

            Comment


            • #7
              Re: Store Current Sheet Name As Variable

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

              Comment


              • #8
                Re: Store Current Sheet Name As Variable

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

                Comment


                • #9
                  Re: Store Current Sheet Name As Variable

                  Thanks, you too.

                  Comment

                  Working...
                  X