Keep Hidden Sheet Made Visible As Active Sheet
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.
Sub UnhideSheet1()
Sheet2.Visible = True
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sorting A Very Hidden Sheet That Is Not Active
I have tried to use absolute references (perhaps I didn't do it right) but it didn't work. Below is the code that does work but I need to first make the sheet visible then activate it. Sub Row_Sort(tmpsheet As String) ' Sort the first 4 columns of the tmpsheet sheet Dim tmprng As Range Dim OldActiveSheet As Worksheet ' Activate the desired workbook (in this case Linewkbk) Workbooks(LineWkbk.Name).Activate ' Save old active sheet Set OldActiveSheet = ActiveSheet.............
View Replies!
View Related
Visible Sheets To Be Hidden With The Exception Of The Sheet Called "open". Then Is Saves The File
i am using the macro below to get excel to hide all sheets but the one called "open"... PHP Sub savemini() ActiveWorkbook.Unprotect "letmein" For Each sh In ThisWorkbook.Worksheets If Not sh.Name = "open" Then sh.Visible = xlVeryHidden Next sh ActiveWorkbook.Save End Sub So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file. Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not. The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.
View Replies!
View Related
Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated: 1-the sheet is renamed to the value of F1, 2-a new tab is made (a carbon copy of the hidden sheet "Template") 3-the new tab is named "New Tab" and marked as unhidden. Public Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Name = Range("F1").Value End Sub Function WorksheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0) End Function..................
View Replies!
View Related
Macro Copy Of The Active Sheet And Then Rename The New Copied Sheet
I'm trying to create a copy of the active sheet and then rename the new copied sheet to what's in cell O4, which is a formula (see below) and then paste value cell O4 in B3 of the copied sheet. However, when I run this macro it doesn't seem to like the second line where I am renaming the sheet (run time error '1004'). "O4" =DATE(YEAR($B$3),MONTH($B$3)+1,DAY($B$3)) Sub NewMonth() ActiveSheet.Copy Before:=Sheets(Sheets.Count) ActiveSheet.Name = Range("O4").Value ActiveSheet.Range("O4").Copy ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues End Sub
View Replies!
View Related
Copy/Paste From Active Sheet To Another Sheet
I want to copy data from the current sheet, and past it in the "montly" sheet. 'i and j were defined above, with a Case statement Range(" Ai:Cj").Select Selection.Copy Sheets("monthly").Select ' I want the upper left cell of the pasted area to be A11 Range("A11").Select ActiveSheet.Paste I get an error saying that I should select one cell and then paste. But I thought that that is what I did...
View Replies!
View Related
Tracking Changes Made To Worksheets On A Hidden Worksheet
I've developed a workbook that is designed to allow users to produce a list of aspects (one per worksheet) and edit them as the aspect changes. However, i would very much like to create a 'log' of changes. For example, everytime a cell is changed the cell reference, worksheet reference and date are added to a list on a hidden worksheet.
View Replies!
View Related
No Name Visible In Sheet
When I open Excel sheet it doesnt say the name of the file anymore, usually seen above the menubar. For example when I open a file called book.xls it usually says the name in the blue bar on the top left sideof the menu bar, but not anymore. Im sure there is a simple way to restore this but cant seem to find it. more details: 1. I open blank excel sheet. 2. Within Excel I click on File>Open and browse to the xls file i want to open. 3. I open file, no problem there, just on top of the blue bar it should say the name of the file and it doesnt. 4. This is the case for ALL excel files I open. 5. Opening a file directly by doubleclicking on it is the same problem.
View Replies!
View Related
Sheet Only Visible In VBE
In my VBA Project Editor Window I have a Sheet17 without the normal brackets and a sheet name. Also I do not think there is a sheet 17 in the workbook. put differntly I have Sheet1 (InfoOptions) Sheet2 (Something) Where the stuff between brackets the name of the sheet in Excel is. Also in the " properties" window, if I click on the above sheets it tells me the name is Sheetx etc. I have a strange sheet that only dislays this Sheet17 -No brackets AND in the properties window it gives ThisWorkbook How do I fix this. I am more than willing to send the workbook via email. I can also, if I may, insert a screenshot of this. I have already tested - this is not a hidden sheet.
View Replies!
View Related
Sheet Tabs Not Visible In 1 Workbook
I know this is probably an easy one, but can anyone help, I have a spreadsheet with aboout 8 worksheet tabs, for some reason they have disappeared I have emailed the spreadsheet to my work email and everything is fine, does anyone out there know what I have done. I have checked the tools options and view tab as informed and the sheet tabs are definately checked, also I have ensured that the tabs are not hidden.
View Replies!
View Related
Open A Workbook With Only One Sheet Visible.
How do I get a workbook to open with a selected sheet visible, or preferably all hidden? I already have a userform that opens upon opening the workbook that the user 'Enters' the workbook with. My untidy method would have been to use an If -Then statement for each sheet in the Private Sub Workbook_Open. Is there a neater way of doing it?
View Replies!
View Related
Paste To Hidden Sheet
Sheets("Sheet1").Select Range("A1:B2000").Select Selection.Copy Windows("SL Forms.xls").Activate Sheets("Hidden Sheet").Select Range("A1:B2000").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone Sheets("Hidden Sheet").Select The macro starts on open, opens the workbook with the data to copy, copies and pastes the data into the original workbook. The problem is that "Hidden Sheet" is hidden, so the macro can't see it! how do I get the macro to use this hidden sheet without keeping it 'un-hidden'?
View Replies!
View Related
Copy Visible Cells From Filtered Sheet
I have data in one column that is the result of a formula, ie: =B2*B3. The result may display a decimal answer that goes out 4 places. My boss likes us to clean this up by using the = ROUND(B4,2). That is fine and easy enough, but the problem is that these numbers maybe spaced throughout a large sheet. I have to filter to make sure similar items are calculated the same way. The problem is that when I copy and paste, it copies and pastes all the data hidden between the displayed cells of the filtered sheet. How do I make it so I am only copying and pasting the data displayed and not the cells that are filtered out in between?
View Replies!
View Related
Compile Error In Hidden Sheet
I've written some VB code in Excel 2003 which hides and unhides worksheets dependant upon 'Yes' or 'No' in a named range. This works Ok in Excel 2003, however if I try and use it in Excel 2007 it shows error 'Compile Error in Hidden Sheet'. When I debug it the error 'Compile Error - Method or Data Member Not Found' appears. The code in question is as follows:
View Replies!
View Related
Run Macro From Hidden Sheet
Sub Wright() ' ' Wright Macro ' ' ActiveSheet.Shapes("Object 5").Select Selection.Verb Verb:=xlPrimary End Sub What do I need to add to this so that the Macro will run when the sheet is hidden? The Macro runs an Embeded presentation, which I do not want to be visible in the workbook.
View Replies!
View Related
Running Code On A Hidden Sheet
I have placed the code I am using below. It takes values from a hidden sheet (PACTAct) and places them on a visible sheet (Email). The code moves between the two. It works at the minute by unhiding the PACTAct sheet then at the end re-hiding it. However I don't want the user to see this sheet whilst the vba is running. Is there a way I can simply refer to the worksheet without unhiding it?
View Replies!
View Related
Copy Content To Hidden Sheet
I have a workbook, wich copies content from an overview sheet to different other sheets. that works fine, but if I try to hide the content-placeholder sheets, I can't copy my content anymore. here the part where I get the error; 'OldValue contains the name of the "copy to" sheet as a string Sheets(OldValue).Select
View Replies!
View Related
Copy Data From Hidden Sheet
I have a hidden holding sheet where I copy data into col A as follows: Sheets("April_June"). Range("a10:A110").Copy Destination:=Sheets("Staff_Import").Range("a1") This works fine but what I also need to do is delete the blank rows in the holding sheet "Staff_Import" and copy back to another sheet "July_Sept" without removing the formatting in "July_Sept" sheet ie cell fill & borders.
View Replies!
View Related
Add An Option To Print All Except Hidden Sheet
I have been using this to print multiple sheets. The only problem is if you want all sheets in the workbook you have to check every one. How would I add an option to 'print all'? But I still wouldn't want to print the hidden sheets. Option Explicit Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If...............
View Replies!
View Related
Seeing Cell Comments On Sheet With Hidden Rows
I have a spreadsheet where i have security set to prevent a user from selecting locked cells. However when a user selects a cell that is in a row at the bottom of the vissible area the comment text can not bee seen. What i need is to determine what the bottom vissible row is and use that to compare the row number of the current row and thus decide if i should scroll down to improve the visibility of the help text for the cell in question.
View Replies!
View Related
Updating Hidden Sheet From Closed Master
Whilst my question is partially answered in other threads, I don't seem to be able to get enough info to piece together a complete solution for my required task. I have a "master" workbook that contains worksheets that are simply lists to be referred to by other workbooks... IE; one worksheet is a list of customer names, another is a list of products, etc, etc... The idea is that I only have to update the data in one place, and all the workbooks that use this information can draw the current data from a single source... What I require; is some VBA code to import the require worksheet from the closed "master" workbook, and hide the imported worksheet so it's not seen by the user... this action would have to replace the previous copy of the hidden worksheet... Ideally this would happen automatically when the user workbook was opened...
View Replies!
View Related
Populate Listbox With Hidden Sheet Names
iam trying to populate a listbox with only the workbooks hidden sheets. iam trying to adapt this code which i found in the forums Private Sub UserForm_Initialize() Dim wsSheet As Worksheet Dim lngIndex As Long With ThisWorkbook Redim strarray(.Worksheets.Count - 1, 1) As String lngIndex = 0 For Each wsSheet In .Worksheets strarray(lngIndex, 0) = wsSheet. Name lngIndex = lngIndex + 1 Next End With With ListBox1 .List = strarray End With End Sub
View Replies!
View Related
Show UserForm On No Cell Value In Hidden Sheet
I have a Workbook with multiple worksheets. On one of my worksheets (Sheet4) I have three cells, let's call call them A1, A2 and A3. A1 = Annual Budget A2 = Money carried over from last year A3 = Current financial year. Formulas in other sheets are dependent upon these cells. I also have 3 userforms, one relating to each cell, with each form containing a textbox which enters its contents into its respective cell after clicking a command button. The userforms should display if the cell it relates to is empty. For example, Private Sub Worksheet_Calculate() If .Range("A1) = "", Then Budget_Form.show End If End Sub The problem I have is that the userforms only appear if I manually change the contents of the cells that they relate to. I need the sub to run every time the workbook recalculates. On top of that, I want the sheet to be hidden but the subs to still run. After looking around on other sites, I have pieced together the following code and placed it in the code for Sheet4 Private Sub Worksheet_Calculate()..................
View Replies!
View Related
Making One Sheet Visible Once A Week Or When Command Button Is Pressed
I have a workbook with about 53 worksheets one for every week in the year. They are named as follows: VA-1-1-09 TO VA 12-28-09 All sheets are hidden except the (VA-1-1-09) What I am trying to do is create some logic that would keep all 52 sheets Very hidden except sheet VA-1-1-09 Till the second week starts at that time what I would like to see happen sheet VA-1-5-09 come out of very hidden and appear visible and send sheet VA-1-1-09 to just a hidden state. I like to have this happen for every week of the year? If this can happen automatically that would be great however a command button clicked once a week would do the trick as well.
View Replies!
View Related
Assign Password To A Hidden Sheet, Only 1 Cell Populated
This is my input for password prompt: Sub CommandButton1_Click() Dim wSheet As Worksheet On Error GoTo ErrHandler Currently, my excel is protected with a password "PSWD" when users open the file. In my vba script, I have a function that can unprotect my excel without any password input box to the users. This method is to unprotect the file and allow users to do any formating columns and rows tasks, such as add or delete cells. After users finish their job, the file will be protected back. The function is such as below: Dim wSheet As Worksheet For Each wSheet In Worksheets Application.ScreenUpdating = False If wSheet.ProtectContents = True Then wSheet.Unprotect Password:="PSWD" Else My questions are: 1. How I can create a hidden sheet that has one cell populated only? 2. How can I assign the password that the user enters at the "TextBox1" (first code) to a cell of of the hidden sheet? Do I have to modify the first code? 3. For the second code, how am I going to assign the value to at the wSheet.Unprotect/Protect Password:="..." with whatever the user has previously chosen as a password that refers at the hidden sheet?
View Replies!
View Related
Automatically Check For Existence Of Hidden Sheet Upon Open
I use the code below to check and see if a sheet exists or not when a workbook is opened. If it does not, continue. If it does I need to run a different sub on it. Twist, I need to check for a hidden sheet. How would I chg the code to do this? Private Sub Workbook_Open() 'Also need to check the code in the userform. Has On.Time command. 'SplashForm.Show Sub Sheet_Test_1() Dim sh As Worksheet On Error Resume Next Set sh = ActiveWorkbook.Sheets(" total") If Err.Number <> 0 Then MsgBox "The sheet doesn't exist" Err.Clear On Error Goto 0 Else MsgBox "The sheet exist" End If End Sub
View Replies!
View Related
Hidden/Visible Sheets In Listbox
I'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:
View Replies!
View Related
Spreadsheet Open But Not Visible. Hidden
I was working on a multi-tab spreadsheet (7 tabs to be exact) and I went to hide one of the tabs and all of a sudden the worksheet went away and I was left with my empty personal.xls spreadsheet. If I go into tools and visual basic I see my spreadsheet listed as VBAProject (Durex Sales Oct 1 to Oct 28 06.xls) with the 7 sheets listed below it within a folder called Microsoft Excel Objects, but I can't get it back in spreadsheet form.
View Replies!
View Related
Transfer The Data In Those 5 Fields To 5 Specific Cells In A Hidden Sheet
I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document. Anyone know how I can go about this or where I can go that explains the specific coding? I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.
View Replies!
View Related
Workbook Code Errors When Orginal Sheet Hidden Or Deleted
I copied the sheet and redid the format. I renamed the original dashboard sheet from count to "keep" and named the copy after the original "count" Everything still works great - until i either hide or delete the original count which is now named "keep". I get an error at the red colored line below ".publish false". I have attached an image of the error....
View Replies!
View Related
Check Only Active Sheet
I have this macro that looks in all sheets in column A, except the invoice sheet. How do I alter it to only check the active sheet? Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name ("Invoice") Then On Error Resume Next With ws .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True With .Columns("A").CurrentRegion .Offset(2, 0).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible).Copy _ Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp)(2) End With .Cells.Rows.Hidden = False End With Err.Clear End If Next ws
View Replies!
View Related
Jump To Last Active Sheet
Is there a way to create a macro to jump to the last active sheet? For example, the last sheet I was on was Sheet1 and now I'm on Sheet20 and I want to jump back to Sheet1 without scrolling through the tabs at the bottom of the screen.
View Replies!
View Related
Return To The Last Active Sheet
I have a spreadsheet that is nearly 100 worksheets. I am new to VBA and would like to put a button on each tab to return me to the last active sheet. I've tried other codes I've found on here but with no luck. I'm not sure if i'm posting them to the right area within VBA.
View Replies!
View Related
How To Run Macro From Active Sheet
I have written the below macro to copy data from a sheet called "Fixit Example 1" to a sheet called "Fixit Summary Example". It works fine, but I'd like to be able to run it from any "Fixit Example XX" worksheet. As written, the data is always copied from "Fixit Example 1" regardless of what worksheet I have open. I tried replacing Windows("Fixit Example 1.xls").Activate with Windows(Active.Worksheet).Activate but got an error "Run time error 424. Object required." Sub MoveData() ' ' MoveData Macro ' Macro recorded 18/03/2009
View Replies!
View Related
Vlookup Code For Non Active Sheet
I wrote this code today and it works - sometimes. Really it only seems to work when I have one of the sheets(zvur013) is in focus (ie, it was the last sheet to have been tabbed to). I dont understand what I'm missing, please help. Sub lookforitems() Dim items() Dim groupnums() Application. ScreenUpdating = False Set allitems = Workbooks("zvur013.dbf").Sheets("zvur013") Set allgnums = Workbooks("Groupnumbers.xls").Sheets("sheet1") Countgroups = Application.WorksheetFunction. CountA(allgnums.Columns(1)) countitems = Application.WorksheetFunction.CountA(allgnums.Rows(1)) ........................... allgnums.Cells(j + 1, i + 2).Value = x Debug.Print x Next j Next i End Sub
View Replies!
View Related
Find Active Sheet In A Workbook
I am creating a macro that will open another workbook and take all the contents off a page in it and paste the contents to itself. The problem is that sometimes those workbooks will only have one page and it will have the data I need, but sometimes there will be two or three pages in the workbook, but again, with only one sheet that has data on it. How can I have the macro find the one page with data on it?
View Replies!
View Related
Save Active Sheet To Another Workbook
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as Report1_11-03-08.xls. Then just continue incrementing. Sub CopySave() Application.ScreenUpdating = False ActiveSheet.Copy Application.DisplayAlerts = False ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls" ActiveWorkbook.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
View Replies!
View Related
Find Active Cell Value On Another Sheet
I am having problems getting a VBA search function to work... this is the code I am currently using with very little success: Sub FindApp() Selection.Copy Sheets("Applications").Select Cells.Find(What:="ADT32109", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub What I am trying to achieve is to copy the contents of the active cell in the first sheet and find it within another sheet but I am unable to get the What:= section of the code to take the value of the copied cell.
View Replies!
View Related
Reference Chart By Name That Is Not On Active Sheet
I am trying to select cells, tables and charts by just knowing their name. Thus if possible I would like to only refer to a chart by its name i.e. not know what worksheet they are on. This seems to work for named ranges of cells/tables, but not charts (I can't get my named charts to appear in the "Define Name" dialog). I found this code that loops through charts on the activesheet and shows the names. However, I would much prefer not to have to specify the sheet or to loop through all the sheets trying to locate the one with the named chart. Public Sub GetEmbeddedCharts() Dim myChart As ChartObject Dim myCharts As ChartObjects Set myCharts = ActiveSheet.ChartObjects For Each myChart In myCharts Debug.Print myChart.Chart.Name Next End Sub
View Replies!
View Related
|
TRACKER
