Hiding Sheets And Rows

Aug 8, 2008

Why this doesn't error but yet it doesn't work either.

Sheets("2 plans, 3 plans, 4 plans").Visible = False

Sheets("1 plan").Range("a26:a416").EntireRow.Hidden = True

View 9 Replies


ADVERTISEMENT

Toggling (hiding/unhiding) Blank Rows Across Sheets

May 5, 2006

I have groups of data in A5:AW200 with various blank rows in between, and would like to be able to automatically hide and unhide (toggle) the blank rows, but only for those rows where cells in column A are blank.

I'd also like to hide those same groups of blank rows on Sheets 2-5, which are laid out exactly the same as Sheet 1, but since they contain cell references to and are mirrors of Sheet 1, certain rows on those sheets may appear blank, but in fact are not. (I should probably have separate buttons on each sheet, but not sure). I know Sheet 1 can be used to test for conditions (blanks rows) and translate the results to Sheets 2-5, but I'm not sure how to do it.

View 4 Replies View Related

Hiding And Unhiding Rows In Multiple Sheets Based On Specific Cell Value With VBA

Jan 4, 2013

I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?

For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.

View 9 Replies View Related

Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets

Nov 10, 2008

i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf

i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro

my macros involve printing certain pages dependng on what button is pressed

i get an error whatever
how do i stop this

View 14 Replies View Related

Re-Naming Sheets Per Cell Data & Hiding Sheets

Aug 26, 2009

on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1

To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.

View 4 Replies View Related

Hiding Sheets

Jun 11, 2009

A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.

Sub Button10_click()
Worksheets("Account Type").Activate
End Sub
Sub Button11_Click()
Worksheets("Name Change").Activate
End Sub
Sub Button12_Click()
Worksheets("Address-Phone").Activate
End Sub
Sub Button13_Click()
Worksheets("Main Menu").Activate
End Sub
Sub Button15_Click()
Worksheets("Cust-Owner").Activate
End Sub
Sub Button16_Click()
Worksheets("Misc").Activate
End Sub

View 9 Replies View Related

Hiding Of Sheets Through Yes Or No Selection?

Aug 19, 2013

I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.

Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.

View 6 Replies View Related

Hiding Many Sheets In List?

Jan 8, 2013

Cells B2:B100 contain a list of sheet names, cells C2:C100 have entries TRUE or FALSE.

Is there away to setup a macro that goes through the list and if false, make the sheet very hidden, if true it should be visible?

View 1 Replies View Related

Hiding By Criteria Throughout Sheets?

Jun 3, 2013

I wrote a macro that hides every row that contains an "X" in column X for every sheet. The problem is that it runs very slowly (assumedly because it is checking every cell on every sheet). Is there a way to clean this up and make it run faster?

My code:
Sub HideRows()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim Cell As Range
For Each Cell In ws.Range("X:X")
If Cell.Value = "X" Then
Cell.EntireRow.Hidden = True
End If
Next
Next ws
End Sub

View 2 Replies View Related

Prevent Un-hiding Of Sheets

Nov 21, 2007

As many are aware, there is an option to make a sheet very hidden. However, a "very hidden" sheet remains visible in the VBE project window. A user with basic VBA knowledge can easily selected the hidden sheet & go in the property window and change its visibility setting to "-1 Sheetvisible". Is there any way to make hidden sheet invisible in VBE as well?

View 3 Replies View Related

Hiding Sheets Using Conditional Formatting?

Aug 7, 2014

i have a list of services with "yes" or "no" options in the column beside it. Description for each service is given on separate sheets (Workbook sheet 1, Workbook sheet 2..etc). i want to format it in a way that if i choose "no" for one of the services, its description sheet hides.

View 3 Replies View Related

Hiding And Unhiding Sheets Using A Macro

Apr 27, 2012

I would like to be able to hide the tab i am in and unhide another tab at the click of the button,

I have tried recording the macro myself and it works up to a point,

I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)

View 4 Replies View Related

Code For Deleting Rows Verses Hiding Rows

May 25, 2013

The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.

Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell

Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell

View 4 Replies View Related

Hiding Protecting Sheets After Someone Clicks On Cell

Jan 5, 2013

I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.

I need columns

A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?

I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.

View 3 Replies View Related

Hiding Sheets Based On Value In Dropdown Cell

Mar 21, 2007

I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.

I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.

if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.

View 14 Replies View Related

Ctr+PgUp/PgDn Not Workinng After Hiding Sheets

Aug 24, 2006

I have a worksheet that veryhides all the sheets but one when closes and when opens if unhides the ones a user needs. For some reason the quick key Alt + PageUp or Alt + PageDown doesn't work until each sheet is scrolled through by clicking on their tab with the mouse.

View 4 Replies View Related

Combining Two Workbooks Into One (hiding Sheets, Password Protection, Etc.)

Aug 26, 2008

='NAME OF SHEET'!$#

where # equals the exact cell in the other sheet I want to copy. I'm also starting to realize that with this formula, Senior Monkette and I could take Dingleberry and Bliddiboo and combine them into a more powerful and robust spreadsheet (called "Voltron!").

1. If we're going to be sending Voltron! to the client, all I would need to do is password protect, then hide the Dingleberry sheets so that the client doesn't look at them accidentally, right?

2. If I hide and protect the Dingleberry sheets, the cell information will still show up on the Bliddiboo sheet, right?

3. Since Senior Monkette isn't as Excel-savvy as I am, the entire process would have to be as painless as possible. What I was thinking of doing was having one master Voltron! where Senior Monkette could make her changes and updated. Then every week, save the entire thing, protect/hide the Dingleberry sheets, and then save a copy as a separate Voltron! file, marked by the date, which then gets shipped off to the client. Is there a macro I can run that will do that all with the press of one button? (And how do I install macros?)

View 10 Replies View Related

Hiding Columns And Sheets Based On Combo-box Values

Nov 4, 2008

I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.

Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.

When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".

None of the sheets in the workbook are protected.

I would sincerely appreciate any help/code that could circumvent this error.

View 9 Replies View Related

Hiding Chart Sheets If Users Disables Macros

May 10, 2008

I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0

Splash.Visible = True

View 4 Replies View Related

Hiding Sheets Causing A Runtime Error On External Data Refresh

Apr 20, 2007

I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.

View 4 Replies View Related

Hiding Rows...

Jul 1, 2009

I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.

View 2 Replies View Related

VBA For Hiding Rows?

Oct 29, 2012

I am building a real estate model. What I am trying to do is the following:

If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.

View 1 Replies View Related

Hiding Specific Sheets Along With Sheets Containing Specific Number

Dec 3, 2013

I have a workbook with the following sheets i do not want to hide called "Detailed Template", "INTERFACES", "STATUS".

I then have a number of worksheets which have their names as cell addresses i.e. "E4", "E5", "D4", "F4", "G7" and so on.

I would like to hide all worksheets (except the 3 mentioned at the top [which are sheets 1-3]) and any which contain say the value of '4'. This value is defined by a cell in the "INTERFACES" worksheet say 'A1' for example.

I want the macro to be able to automatically do this for which ever value is in 'A1'. So if it were to change to '7' it would hide every sheet that doesn't contain '7' in the name.

View 6 Replies View Related

Hiding Rows With A Cell Containing 0

Oct 25, 2006

a bit of code so that when I open a sheet (which takes data from another sheet) it hides rows which contain a zero in a certain cell?

View 10 Replies View Related

Hiding/Unhiding Rows

Feb 8, 2010

I have a problem with hiding and unhiding columns. Is there a way wherein I can automatically hide the row(s) if the cell = "N/A" and will unhide if it's not equal to "NA". see the attachment Im using excel 2003 btw. You can see in Sheet2 to that it contains the table for the student grades. "N/A" means that they are not enrolled on that subject. While in sheet1, What I want to happen is that you will select a student name and below, it will automatically display the grades of the student on their enrolled subjects only. So, those fields with "N/A" will hide and when I update the value of N/A it will unhide.

View 5 Replies View Related

Hiding Rows Based On Value

Apr 22, 2014

The attached sheet takes information from another sheet in my work book (I am only providing the sheet I was to work with). I want all rows where the value in F is 0 to hide automatically.

Attached File : Book2.xlsx‎

View 3 Replies View Related

Hiding Blank Rows

Feb 1, 2010

I have had great success using this code to hide and unhide unused space, but for some reason this morning it broke. Most likely it is my error so I am looking for another solution to resolve the issue. What i would like to be able to do it hide an entire row based on a specific cell being blank. maybe that is what this code is supposed to do but like i said it broke this morning. the cell in ref in the code is unchanged, but i did add formulas to some other cells in the same row. this is why i am looking for a way to do the same thing but based on a specific cell being blank vs what is occuring now.

View 4 Replies View Related

Error 424 When Hiding Rows

Feb 2, 2010

I have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this?

As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.

View 12 Replies View Related

VBA Hiding Rows With Asterisks

Jan 1, 2012

I have a worksheet with 4000 rows and only 4 columns.

In quite a lot of the rows in column 2 there are asterisks (**'s).

I have to print out the report without these rows.

First i would like to hide the rows and print the report

Second i then need to delete the rows if there is no data in column 4

View 4 Replies View Related

Hiding Rows Based On Value?

Mar 27, 2012

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = Sheets("Sheet2").Range("A3") Then 'A3 is the cell where your Yes/no choice is
Select Case Target.Value
Case "Yes": Sheets("Sheet1").Range("A7:A22").EntireRow.Hidden = True
Case "No": Sheets("Sheet1").Rows("A7:A22").EntireRow.Hidden = False
End Select
End If
End Sub

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved