+ Reply to Thread
Results 1 to 3 of 3

HELP!! Unhide Sheet with Macro and focus on other sheet

  1. #1
    saraqpost@yahoo.com
    Guest

    HELP!! Unhide Sheet with Macro and focus on other sheet

    I have 2 sheets in a workbook. I'd like one to be hidden, and then
    have the user press a command button to run a macro to:

    1. unhide the hidden sheet
    2. Copy some cells on the hidden sheet to another location on the
    hidden sheet
    3. Rehide the sheet
    4. Clear some cells on the remaining (un-hidden) sheet.


    The code is below.
    ' Unhide the totals, copy NEW YTD to Curr YTD for next week start
    Sheets("Depts and LW").Visible = True
    Range("D9").Select
    Range("H3").Select
    Range("H3:I22").Select
    Selection.Copy
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
    SkipBlanks:= _
    False, Transpose:=False

    ' Hide the YTD sheet
    ActiveWindow.SelectedSheets.Visible = False

    ' Activate Week Sheet and clear the data entered on the tracking sheet
    Range("C3").Select
    Range("B3:C22").Select
    Selection.ClearContents


    The problem is:
    1. Sheet is unhidden, fine.
    2. When the next line executes ( Range("D9").Select ) it is
    selecting in the Original, UNhidden worksheet. (I added the Selec of
    D9 when the select of H3 didn't work.

    Can anyone HELP???? Please??? I am at a total loss here.

    Thanks
    Sara


  2. #2
    Miguel Zapico
    Guest

    RE: HELP!! Unhide Sheet with Macro and focus on other sheet

    You may use the "activate" method of the worksheet object in the code, to
    ensure that the proper sheet is active. In this case, you may use:
    Sheets("Depts and LW").Visible = True
    Sheets("Depts and LW").Activate
    Range("D9").Select

    And then use it again before:
    ActiveWindow.SelectedSheets.Visible = False

    To get back to the original sheet you want to hide (or even replace with
    Sheets("Depts and LW").Visible = False)

    Hope this helps,
    Miguel.

    "saraqpost@yahoo.com" wrote:

    > I have 2 sheets in a workbook. I'd like one to be hidden, and then
    > have the user press a command button to run a macro to:
    >
    > 1. unhide the hidden sheet
    > 2. Copy some cells on the hidden sheet to another location on the
    > hidden sheet
    > 3. Rehide the sheet
    > 4. Clear some cells on the remaining (un-hidden) sheet.
    >
    >
    > The code is below.
    > ' Unhide the totals, copy NEW YTD to Curr YTD for next week start
    > Sheets("Depts and LW").Visible = True
    > Range("D9").Select
    > Range("H3").Select
    > Range("H3:I22").Select
    > Selection.Copy
    > Range("E3").Select
    > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
    > SkipBlanks:= _
    > False, Transpose:=False
    >
    > ' Hide the YTD sheet
    > ActiveWindow.SelectedSheets.Visible = False
    >
    > ' Activate Week Sheet and clear the data entered on the tracking sheet
    > Range("C3").Select
    > Range("B3:C22").Select
    > Selection.ClearContents
    >
    >
    > The problem is:
    > 1. Sheet is unhidden, fine.
    > 2. When the next line executes ( Range("D9").Select ) it is
    > selecting in the Original, UNhidden worksheet. (I added the Selec of
    > D9 when the select of H3 didn't work.
    >
    > Can anyone HELP???? Please??? I am at a total loss here.
    >
    > Thanks
    > Sara
    >
    >


  3. #3
    CLR
    Guest

    RE: HELP!! Unhide Sheet with Macro and focus on other sheet

    This line, modified to suit, should help...switch to the sheet you want
    before selecting the range....

    Sheets("Sheet3").Select

    Vaya con Dios,
    Chuck, CABGx3



    "saraqpost@yahoo.com" wrote:

    > I have 2 sheets in a workbook. I'd like one to be hidden, and then
    > have the user press a command button to run a macro to:
    >
    > 1. unhide the hidden sheet
    > 2. Copy some cells on the hidden sheet to another location on the
    > hidden sheet
    > 3. Rehide the sheet
    > 4. Clear some cells on the remaining (un-hidden) sheet.
    >
    >
    > The code is below.
    > ' Unhide the totals, copy NEW YTD to Curr YTD for next week start
    > Sheets("Depts and LW").Visible = True
    > Range("D9").Select
    > Range("H3").Select
    > Range("H3:I22").Select
    > Selection.Copy
    > Range("E3").Select
    > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
    > SkipBlanks:= _
    > False, Transpose:=False
    >
    > ' Hide the YTD sheet
    > ActiveWindow.SelectedSheets.Visible = False
    >
    > ' Activate Week Sheet and clear the data entered on the tracking sheet
    > Range("C3").Select
    > Range("B3:C22").Select
    > Selection.ClearContents
    >
    >
    > The problem is:
    > 1. Sheet is unhidden, fine.
    > 2. When the next line executes ( Range("D9").Select ) it is
    > selecting in the Original, UNhidden worksheet. (I added the Selec of
    > D9 when the select of H3 didn't work.
    >
    > Can anyone HELP???? Please??? I am at a total loss here.
    >
    > Thanks
    > Sara
    >
    >


+ 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