VBA/macro To Unhide A Sheet?
I'm having a challenge with a spreadsheet that puzzles me: I'm using this quite simple code to hide one of the spreadsheets (named "Ark2"). It written directly in a module as a subprocedure and there's no more code than this:....
View Complete Thread with Replies
Related Forum Messages:
Unhide All Sheets That Do Not Contain Certain Words In Sheet Name
I pulled some code off the internet to “force” users to enable macros by hiding all sheets except a “Welcome Page”. It works great except that it also unhides sheets that are meant to be hidden by when the workbook is opened.
How would I adjust the following code so any sheets/ tabs containing the words “New Project” are not automatically unhidden? Below is the code pertaining to unhiding. I did not paste the code in its entirety as it is fairly long.
Const WelcomePage = "Macros"
Private Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
VBA Code To Unhide Column
I have 2 worksheet as shown below.
IMAGE - 1 : Assume that there are 3 entries in column A
Excel Jeanie HTMLSheet3 *A1Person 12Person 23Person 3 Excel tables to the web >> Excel Jeanie HTML 4
IMAGE 2 - Notice that column G and H are hidden ...
Hide/Unhide Sheet When A Certain Cell Is Changed
I am trying to create some code that will work each time a cell is updated. For simplicity, when cell A1 on Sheet 1 is greater than or equal to 25,000, I want Sheet 2 and 3 to be shown (they will already have been hidden). When A1 on Sheet 1 changes to less that 25,000, I want only Sheet 4 and 5 to be shown. I want to be able to change the number back and forth in A1 so only Sheets 2 and 3 show when A1 is >= 25,000, and only Sheets 3 and 4 to be shown then A1 < 25,000. I can't seem to find how to hide sheets based on a cell changing. I can only find how to permanently hide sheets.
Protect Sheet But Hide/Unhide Columns
I have a speadsheet that has 27 columns for time entries, however employees seldom need to use more than the first 12 columns. I need to protect the sheet but would like the employees to be able to Unhide the columns when they need to use them, then reHide them. I can't find this as an option in either 2003 or 2007.
Hide / Unhide Columns On Protected Sheet
When my spreadsheet opens it automatically protects the sheets using the following
.Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
.EnableOutlining = True
and I use the following to hide the column if the toggle button on my form is 'true'. The problem is it gives an error if the sheet is protected. it worksfine without protection.
If ToggleButton1.Value = True Then
Range("column_calories").EntireColumn.Hidden = True
Range("column_calories").EntireColumn.Hidden = False
Hide Or Unhide Worksheets From Cell Values In Sheet 1
I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks.
I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.
Capture Cells From One Sheet Into Another. Hide/Unhide Sheets
There is 2 parts to my question and I will explain them as best as i can.
1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.
2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.
Hide & Unhide Button On Top Of Sheet ?
I want to hide & unhide columns using only (plus minus) button which is appearing on top of current sheet. I have couple of files with that options built in, but I dont know how to do it. I tried to see macros but appears that there is no macros at all in that file??
VBA To Hide/Unhide Sheets Based On Cell Value
I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc.
VBA Code To Hide Unhide A Number Of Rows
I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size. My best effort so far looks something like this:
Dim i As Integer
Dim myRow As Integer
myRow = Range("A1") + 2
Application. ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction. Sum(Selection.Rows(i)) > myRow Then
Selection.Rows(i).EntireRow.Hidden = True
Application.ScreenUpdating = True
the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with. My attempt did not work.
Allow Hide & Unhide Column/Rows On Protected Sheet
I'm familiar with the code to allow grouping/ungrouping in a protected spreadsheet; http://www.ozgrid.com/Excel/outlining-protected.htm. How would I code to allow for hiding/unhiding cloumns and rows while still protecting the spreadsheet? Also, how could i allow for this file to be shared? Currently i'm getting an debug error when another person enters the file.
Unhide Row Macro
If I have say ten rows hidden so that the numbers go from 50 to 61, how do I write a macro that will only reveal rows 51 to 60 one at a time, starting with row 51. If the macro is run a second time it should reveal row 52. A thirs time will reveal row 52 and so on. Finally, when all rows are revealed, the macro should not give a error wanring. Instead, it should say something like "No more rows available"
Macro To Hide/Unhide Worksheets
Develop a macro that will enable me to hide or unhide worksheets.
Basically, I have a few worksheets in my workbook and I want to have a page at the front with two buttons. One for Unhide sheet and the other for Hide sheet. Once activated, I want it to give me a list of worksheets that I can click to hide or unhide (depending on the button i click).
Hide/ Unhide Sheets With Macro
I want to run 2 different macros:
Macro 1- hides Sheet1 and unhides Sheet2
Macro 2- Hides Sheet2 and unhides Sheet1
I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?
Macro To Hide/unhide A Row
I made this simple little macro to hide/unhide a row on a different sheet based on a check box. The row hides fine. The problem I have is unhide. I uncheck the box, but the row stays hidden. What am I missing to make this little gem come to life? This is for tracking popcorn sales for scouting and I'm trying to make it as easy to use as possible.
If True Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = True
If False Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = False
Macro To Unhide Then Hide Sheets
I have 16 sheets and 4 additional sheets that will kind of 'Group' these 16sheets. For example: I have 'Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16'.
4 additional Sheets are: 'Group1', 'Group2', 'Group3', 'Group4'.
I need a help with macro so that when this workbook is open all 20 sheets ('Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16') will go into hiding and only 4 additional Sheets ('Group1', 'Group2', 'Group3', 'Group4') will be visible. Now, these 4 sheets will have the command button links to the following sheets:
Sheet 'Group1':'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Hide All'
Sheet 'Group2':'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Hide All'
Sheet 'Group3':'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Hide All'
Sheet 'Group4':'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Hide All'
This being said, when you click on each command buttons, the respective sheets will open up and when click on 'Hide All', all of the open sheets for that *additional sheet' for example sheet 'Group1' will go into hiding again.
Hide Unhide Rows Via Macro
I have a workbook that has 500 rows. In order to be able to print the spreadsheet, I have added a Macro in that hides any rows that have a "0" in the A column. I then put an if/then formula (ex. if(isblank(A5),0) so that if the cell was blank and 0 would be there and therefore the row would be hidden.
The problem now is that I want a new row to become unhidden everytime the row above has data in it. So, if row 5 gets data put into it, row 6 would become unhidden. The problem I'm running into is that the data in column A is peoples names, and therefore each row will have a different name (i.e. different data).
1. Is there a macro to do this?
2. (This may be a really stupid question, but...) Can you run 2 macros in the same sheet?
Hide/Unhide Columns With Macro
I need hide/show some column by using Macro Button. I have attached the excel sheet( name VBA testing.xls). I need to hide column K,L,N,O & visible column G,H by clicking button "Plan A".Similarly i need to hide the column G,H,N,O & unhide the column K,L by clicking the button "Plant 2. Similarly by clicking the Button "Plant 3", hiding the column G,H,K,L are needed whereas column N,O will be unhide.
VBA Macro To Copy Entire Row To Another Sheet
I have written Macro to copy every row in "sheet1" 24 times into new sheet called "NewSheet". but it keep giving me error message. Actually, I don't know why. Can any one please help.
here is my macro
Worksheets.Add().Name = "NewSheet"
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Loop to copy every row 24 times
For i = 1 To 24
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheet Preparation By Ordering And Swapping Cells Vba Macro
in 1 workbook i have 2 sheets (sheet1 and sheet2) with same format of data that I need to compare and compile in sheet3 by a primary key column.
sheet 1 & 2 data format:
To be more specific I can compare this with an electrical wiring table, where:
Key is the wire number
Atr1, Atr2, Atr3 are general atributes of the wire number
Set1 are the atributes of the first end of the wire, called "from":
SubKey1 is the "from" device and its subatributes SubAtr1_1 & SubAtr1_2
Similar for the other end of the wire is Set2.
This means that Set1 and Set 2 have similar format & type of data.
I have found on your site an example of macro that is doing the comparision between the 2 sheets of data but I encountered a problem...sometimes on sheet2 Set1 and Set2 are reversed and are highlighted as a difference but I don't want this.
Please help me with macro that would create a rearranged copy of sheet 2 respecting the following conditions:
1. if Key from sheet1 cannot be found on sheet2 then copy entire row on sheet3.
2. if Key from sheet2 cannot be found on sheet1 then copy entire row on sheet3.
3. If Key from sheet1 = Key from sheet2
If SubKey1 from sheet1 = SubKey2 from sheet2
& SubKey2 from sheet1 = SubKey1 from sheet2
Then reverse Set1 with Set2 by swapping cells
and copy entire row (with Set1 and Set2 reversed)to sheet3.
All data on sheet 3 should be sorted by Key.
Macro To Unhide / Hide Sheets With Combobox Selection
I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.
The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.
The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.
I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;
How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?
Hide/unhide Col Macro Based On Cell Value
I have a worksheet that contains 10 columns of data. In row 2 I have a formula that will display the result "x" if the data in that column should NOT be hidden, and for all columns of data without an "x" in row 2 I would like to hide the entire column.
Therefore, I am looking for a macro that looks across the range of cells E2:N2 and if there is not an "x" in the cell, then hide that column...
To make things a little more complicated, the value in row 2 will change when other values are amended on other worksheets that feed into this one and I will need the macro to 'unhide' the column as soon an "x" appears in row 2
VBA Macro To Move Data To Summary Sheet Based On ID's
I have had several intro programming classes, but not with visual basic. I've noted the problem lines with comments below. The problems are probably very easy syntax error.
Dim counter As Integer
Dim counter2 As Integer
Dim id As Double
Dim id2 As Double
Dim palletnum As Integer
ActiveSheet. Name = "Pallet Detail"
ActiveSheet.Name = "Pallet Summary"
Vba To Unhide Rows, Print, And Rehide Rows
I am attempting unhide the same exact rows in multiple worksheets in Excel, Print the entire workbook, and then re-hide the same cells. As I am still learning VB i have been unsuccesfull. I have attached the code that I am using,
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
Selection.EntireRow.Hidden = False
Selection.EntireRow.Hidden = True
Macro - Unhide 'x' Number Of Worksheets
I have no idea about VB code and writing macros so I was hoping to get some help on this issue.
Is it possible to unhide a specific number of worksheets based on an input value, i.e. if someone inputs a value of '3' in the specific cell, then 3 worksheets with a certain name is unhidden?
Vba Code For Switching From Sheet To Sheet
I need to write a code for a workbook with multiple sheets. Starting at sheet 1, I need to perform an action, and then move on to the next sheet to perform the same action. I need to do this for all sheets in my workbooks (sometimes over 20 sheets).
How should my macro look? I have learned the code to perform the action I need. I just can't move from sheet to sheet. I would assume this would be some sort of Loop statement until there are no more sheets.
Macro Pull Data From Each Sheet And Create A Summary Sheet
I need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better.
The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
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').
ActiveSheet.Name = Range("O4").Value
Macro For Copying Rows Of Data In Sheet 1 To Sheet 2
I have a couple of spreadsheets that requires me to copy data from Sheet 1 that matches certain criteria to Sheet 2.
I have tried using posted VB codes that have been written for other people but they dont seem to work (this is probably to do with me not actually knowing what I am doing, as I am not sure what I should be replacing and what I need to be deleting etc out of the pre-written code )
I need to be able to search on column e in Sheet 1 for anything that has just h written in the cell and then copy all the rows that match into Sheet 2. I also would like to ensure that when it is copied it doesnt go in row 2 but maybe 4 or 5 as I have quite allot of headings.