Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 befo 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. " 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 " 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions |