+ Reply to Thread
Results 1 to 8 of 8

Running macros including hidden worksheets

  1. #1
    Registered User
    Join Date
    04-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    20

    Running macros including hidden worksheets

    Hi all, im trying to run a macro to refresh a worksheet full of pivot tables to update all my graphs on a seperate worksheet. i want to hide the worksheet with the pivot tables on the the users cant see this, but when i run my macro it comes up with errors as the worksheet is hidden any ideas?

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,024

    Re: Running macros including hidden worksheets

    Have you tried to set the visible property of the sheets to 'Visible' at the start of the macro, have the macro do its thing and then set the visible property back to 'Hidden' at the end of your macro?

  3. #3
    Registered User
    Join Date
    04-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Running macros including hidden worksheets

    my macro at the moment is:

    ' Macro5 Macro
    '
    '
    Sheets("PivotTables - DO NOT CHANGE").Select
    ActiveWorkbook.RefreshAll
    Sheets("DashBoard").Select
    Range("C1").Select

    End Sub

    do you know what i would need to add to make it visible at the start of the macro and the end?

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,024

    Re: Running macros including hidden worksheets

    Try:
    Please Login or Register  to view this content.
    Last edited by Mumps1; 04-15-2013 at 11:21 AM.

  5. #5
    Registered User
    Join Date
    04-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Running macros including hidden worksheets

    thanks, i updated the VBA with:
    Sub Macro5()
    '
    ' Macro5 Macro
    '

    Sheets("PivotTables - DO NOT CHANGE").Hidden = False
    Sheets("PivotTables - DO NOT CHANGE").Select
    ActiveWorkbook.RefreshAll
    Sheets("PivotTables - DO NOT CHANGE").Hidden = True
    Sheets("DashBoard").Select
    Range("C1").Select
    End Sub

    and i had error 438 object doesnt support this property or method

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,024

    Re: Running macros including hidden worksheets

    My apologies. I made an error in my first post and then went back in and fixed it. Please have a look at post #4 again. The "Hidden" should have read "Visible".

  7. #7
    Registered User
    Join Date
    04-15-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Running macros including hidden worksheets

    That worked!!! brilliant thanks for all your help!

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,024

    Re: Running macros including hidden worksheets

    My pleasure.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1