Conditionally Hiding Worksheets

Jun 21, 2006

I have what on the outset seemed a fairly basic task. In VBA I wanted to conditionally hide worksheets based on a value in my main worksheet.

I have 1 "main interface" worksheet with quite a few "result" worksheets as part of my workbook. Instead of scrolling left and right in the tabs menu everytime I wanted to veiw a particular worksheet I wanted to rather hide all the worksheets except the "main interface" and the relevant "result worksheet", which excel would identify based on a value in my main worksheet.

I wrote the following VBA code to do this but for some reason I cannot get it to work correctly. Perhaps I have made a simple logic error? I am fairly new to VBA. Any advice? ......

View 9 Replies


Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.

As a simplified example:

Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!

So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.

I was thinking of using something like this:

Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Rows("20:30").EntireRow.Hidden = False
End If
End If

I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.

View 5 Replies View Related

Conditionally Copy From Multiple Worksheets Into One

Jun 2, 2009

I have workbook that expands or shrinks in number of worksheets each time and I need to gather information from each worksheet to compile a "total" spreadsheet. The location of the cells to be copied in each worksheet vary however it is always in the same column. Managed to find a macro that will collect the information if it is located in the same cell, across all worksheets but did not manage to find something that will conditionaly copy.

Need code, that will search based on text that will be found in the worksheets and then copy the values from the column next to it (same row) to the "total" worksheet. If any of the text that is to be searched in the worksheet does not exist, then it should leave the cell "blank" in the "total". I have attached a workbook of with the example of the worksheets that I have and the result that I want to have at the end, in the "total" worksheet.

View 4 Replies View Related

Conditionally Deleting Lines On Multiple Worksheets

Jun 17, 2014

I need to delete line if 2 conditions are meet.

If col A is blank and col D or E is blank then I want the line deleted for lines 1no more than 100 The option of D or E has me stymied I end up with it not working right. This is what I had tried

Dim lastrow As Long, c As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To lastrow
If Range("a" & c).Value " " and Range("d" & c). Value " " or Range("e" & c)Then ActiveSheet.HPageBreaks.delete
Next c

But doesn't work

I also need macro to run multiple worksheets which varies anywhere from 12 sheets to 50 sheets.

View 6 Replies View Related

Conditionally Importing Data From One Of Several Different Worksheets Based On 1 Factor?

May 22, 2014

I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.

All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).

What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?

View 1 Replies View Related

Conditionally Copy Data: Master Worksheet To Multiple Worksheets

Sep 11, 2006

I am searching for a formula or simple macro to select text data from column named "SM NAME" to corresponding worksheet. I need this to populate as many worksheets as there are SM Names. ( there are usually 10 SMs)

For Example in the Master Data there are Several SM names listed. I need to extract the "ID" and "Agent" columns in the Master and populate into the workshhet with the Approriate name tab. The Master list changes regularly.

View 4 Replies View Related

Hiding Various Worksheets

Sep 7, 2009

I have a spreadsheet with 5 worksheets going, I've been trying to find code that will hide and unhide certain tabs depending on the answer to C21 of Worksheet 1.

If C21 = 1 then show sheet 3 but not 4 or 5
If C21 = 2 then show sheet 4 but not 3 or 5
If C21 = 3 then show sheet 5 but not 3 or 4.

View 5 Replies View Related

Re-hiding Worksheets

Jan 13, 2010

I need to unhide all hidden sheets in a workbook, run some marcos on the hidden sheets, then re-hide only the sheets that were hidden. For each workbook the number of hidden sheets will vary as will the names of the sheets. Is there a way to capture the current hidden worksheet names before I unhide all worksheets and apply the captured names for re-hiding?

View 9 Replies View Related

Hiding Multiple Worksheets With VBA

Feb 2, 2014

I have a workbook with 16 different worksheets. The first worksheet is what I'm calling a table of contents where I have created ActiveX Control Buttons that I would like to attach VBA Sub procedures to. The sub procedures I'd like to include are, as an example, hide all worksheets except worksheets 1and 2. Then I'd have another button to enact the code that says hide all worksheets except worksheets 4 and 6. I have specific names for each worksheet but hopefully that doesn't matter. I've been trying to figure out why the below doesn't seem to work.

Option Explicit
Sub Division_1and2 ()
' Open the Division 1 and 2 worksheets for data entry.
Sheets(Array("Division 3", "Division 4", "Division 5", "Division 6", "Division 7", _
"Division 8", "Division 9", "Division 10", "Division 11", _
"Division 12", "Division 13", "Division 14", "Division 15")). _
SelectSheets.Visible = xlSheetHidden
Sheets("Division 1").Select
End Sub

Instead of xlSheetHidden I've also used the term False to no avail.

View 3 Replies View Related

Hiding And Unhiding Two Sets Of Worksheets

Oct 11, 2008

My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.

I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.

I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.

If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows

View 11 Replies View Related

Runtime Error '9' - Hiding Worksheets Based On Cell Value

May 6, 2009

I'm relatively new to this, and its entirely possibly (more likely probable) that I am attempting to this in the wrong way, but here's what I'm dealing with...

View 8 Replies View Related

Excel 2010 :: Hiding Worksheets When Closing File?

Apr 28, 2012

I am using MS Excel 2010.

What I want to Hide two worksheets and leave one worksheet open when I close out the file.

ws TOC will remain open when closing out the file
ws Rqmts will hide when closing out the file
ws Planning will hide when closing out the file

View 1 Replies View Related

Excel 2003 :: Hiding Rows Based On Conditions On Two Worksheets?

Jan 4, 2013

I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.

Sheet1 sample:


The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).

If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.

So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.

Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.

I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!

View 3 Replies View Related

Excel 2003 :: VBA / Hiding Rows Based On Three Conditions On Two Different Worksheets

Jan 14, 2013

I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.

So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.

Sample of Sheet1


Start Date (dd/mm/yyyy)


So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.

View 6 Replies View Related

How To Sum Data Conditionally

Nov 29, 2012

I have a long row of data with words/numbers in each cell. Whenever I see a cell starting with W, I want to add up the value 2 cells to the right of that cell starting with W.

For example, if these following 9 cells in the same row read W1, 90, 100, M2, 90, 100, W2, 90, 40, whenever the cell starts with W, I want to sum up the value 2 cells to the right of that cell starting with W. In this case, I am adding up the values 2 cells to the right of W1 and W2, which is 100 + 40 = 140. How can I do that?

View 2 Replies View Related

Conditionally Activate Macro

Dec 14, 2006

Is there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?

View 14 Replies View Related

Conditionally Transfer Data

Mar 17, 2009

on the "all data" sheet, there are two rows that have a Y for file missing - these are to be marked, so that they may be focused on finding their file

currently, i am doing as such and then filtering by that column to print out a list, and would like a simplified solution (populate sheet "missing") with just those rows who have missing files - so that another user may easily view such data without having to fondle around with the master list

View 2 Replies View Related

Conditionally Combine Information In One Row

Apr 14, 2009

I'm building a complete part number reference for all service parts of copiers of a certain brand. Every row contains three columns; partnumber, description and machine type. Problem is, some parts are used in more than one machine. When the list is complete I want to do the next: If partnumber in (column A) is found in more than one row, I want to join the information of the other columns in two cells. (see attached example).

View 2 Replies View Related

Delete Columns Conditionally

Oct 18, 2009

I have this file where i delete columns which are extra, in my real file most of the cells are formulas or links . Basically i need a macro which looks in row 4, and if it finds any zeros ( number 0 ) in the cell it deletes that whole column.
The zero is a indicator for me when i work on these files if it is needed or not. Included the file as an attachement.

View 2 Replies View Related

Conditionally Prepend Zero To Strings

Nov 23, 2009

I have a list of numbers that is imported and converted to text. I need to have all these strings beginning with zero "0" except the ones that begin with 1.
To summarize:

If the string's first caracter differs from "0"(zero) and differs from "1"(one) than prepend a "0"(zero). If the first caracter is 0(zero) or 1(one), leave the string as it is.

I am attaching a xls file with 2 columns: Column 1 is how the list is now and Column 2 is how I need it to be.

View 4 Replies View Related

Conditionally Locking Cells

Feb 23, 2008

I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?

This is what I need to do:


View 10 Replies View Related

Conditionally Program A Cell...

Jun 2, 2009

Can I program column B to show a certain value depending on the presence of a string of text in column A? For example, if A1 contains the word "Barbie", then cell B1 shows the value: "Ken"

View 5 Replies View Related

Conditionally Sum Variable Dataset

Feb 2, 2010

I have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.

Cust Name | Status | Loan Amt | etc.

I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.

View 2 Replies View Related

How To Conditionally Populate Dates

Jan 4, 2012

I do have a cash-sale & cash-deposit template as follows.

The cash from the sales proceed needs to be deposited to bank on the next working day but in reality the same is not happening.

The stores are depositing the cash for more than one days sale and then the same is getting submitted to bank.

I would like to populate the Dates(in the "Desired Result" column from D2:D11).

The desired formula needs to check the non-blank cell in "C" column and then fill in the dates in the "D" column.

For example the sales amount from 1st-3rd December is deposited on 3rd December and hence the desired result across D4:D2 needs to be 3rd December.

Similarly the sales amount from 4th-7th December is deposited on 7th December and hence the desired result across D8:D5 needs to be 7th December.

ABCD1Date Of SaleSale AmountDeposit AmountDesired Result212/1/201157335 12/3/2011312/2/201128879 12/3/2011412/3/20112328411883312/3/2011512/4/20116294 12/7/2011612/5/20116894 12/7/2011712/6/201138270 12/7/2011812/7/2011262787474212/7/2011912/8/201133308 12/10/20111012/9/20119279 12/10/20111112/10/201166956886512/10/201112Total236516262440
Spreadsheet FormulasCellFormulaB12=SUM(B2:B11)C12=SUM(C2:C11)

View 2 Replies View Related

Conditionally Attaching A Value With A Code

Aug 9, 2013

How I can calculate the average fix time for each error code. The fix time will not be counted if it doesn't have a fix time. The distance between errors is not always the same, and the fix time is not the same.

How can I:

-Search through the Fix time and essentially add one to the count (only when it requires fixing)
-Only count the fix time for the previous error that has occurred.
-I have tried different combinations of using the MATCH and INDEX formula but have only received errors
-I can add additional calculation cells
-I can use VBA(although it will add to the already fairly long run time)

Error Code

Error Count

Avg Fix Time
Fix Time
Error Code




View 2 Replies View Related

Sheet Names Add Conditionally

May 13, 2009

I want to add to it so that if the "Name" entered into the target cell is longer then the maximum allowd length it inserts an abreviation of the words (and increments where nessisary)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo StandardName
If Target.Address = "$B$4" Then Sh.Name = Target
Exit Sub
Sh.Name = "Sheet1"
End Sub

View 9 Replies View Related

Conditionally Use Cell Value In A Formula

Feb 5, 2010

I want to create a 'virtual switch' so that if I change one cell from 1 to 0, that a formula in another cell becomes active. I can't just have the formula cell * the 1/0 cell, I need to comment out the formula or it draws in too much data.

View 5 Replies View Related

Conditionally Formatting Dates

Dec 12, 2006

I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.

This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”

This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE

The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.

The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.

View 4 Replies View Related

Conditionally Delete Cells

Dec 14, 2006

If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?

View 2 Replies View Related

Conditionally Hide Columns

Oct 12, 2007

I am currently using the following code to hide each row when there is no data between a certain column range. I need to add to this code so that it will also hide each column when there is no date between the row range.

Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
ActiveWindow.DisplayZeros = False
Application. ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub

View 3 Replies View Related

Copyrights 2005-15, All rights reserved