Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Store Current Sheet Name As Variable

  1. #1
    Join Date
    2nd February 2008
    Posts
    23

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

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

    try;

    VB:
    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 at 09:50.

  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: Store Current Sheet Name As Variable

    Dave,
    I think you meant to type ActiveSheet.Name (rather than without the period). Hence
    VB:
    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.

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

  5. #5
    Join Date
    2nd February 2008
    Posts
    23

    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:
    VB:
    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:
    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd February 2008
    Posts
    23

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Store Current Sheet Name As Variable

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

  8. #8
    Join Date
    2nd February 2008
    Posts
    23

    Re: Store Current Sheet Name As Variable

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Store Current Sheet Name As Variable

    Thanks, you too.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Store Cell Location in Variable
    By farful in forum EXCEL HELP
    Replies: 7
    Last Post: April 9th, 2011, 05:32
  2. Store Multiple String In Variable
    By tristevoix in forum EXCEL HELP
    Replies: 3
    Last Post: September 14th, 2007, 01:39
  3. Store An Entire Row Or Column In A Variable
    By Source in forum EXCEL HELP
    Replies: 4
    Last Post: March 14th, 2007, 17:58
  4. Store Cell Address in Variable
    By A Timmer in forum EXCEL HELP
    Replies: 21
    Last Post: September 13th, 2006, 21:19
  5. Store Variable in macro
    By dalton6275 in forum EXCEL HELP
    Replies: 5
    Last Post: November 5th, 2004, 19:20

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno