Hide Workbook Tabs Based On Certain Criteria

Jun 10, 2014

I have tabs within my workbook that are pulling from other tabs in the same workbook. There are 30-40 tabs, and only 10-15 are used at a time. Is there a way to hide tabs that are not being used? If I do a calculation in A1 and it's greater than 0, I'd like the tab that is pulling from A1 to be visible. If A1 is 0, then I'd like to hide it.

View 14 Replies


VBA Code To Hide Sheet Tabs In Active Workbook

May 17, 2013

i need a code to hide the sheets tab from the workbook. and only show the horizontal scroll bar and vertical scroll bar.

View 1 Replies View Related

Hide Tabs Based On A Value In Another Tab?

Mar 23, 2009

I have a "master" tab that has a dropdown menu (via a validation list) which will determine what the spreadsheet does. Based on the value selected via this dropdown, I would like to show the relevant tabs and hide the irrelevant tabs. I would prefer for this to happen automatically without a macro.

View 5 Replies View Related

Sum Tabs Based On Criteria

Feb 18, 2010

I need to create the formula in "Sum state" sheet B2 to sum items for tab AZ (b2:b6). The formula needs to work when I drag it down to other cells.
For example if I drag formula to B3 it needs to adjust the tab from AZ to CA, and add things for that tab. Each cell from A2 :A 11 represent a state and each tab has actual info for each state

View 3 Replies View Related

Pulling Rows From Other Tabs In Workbook Based On Date?

Jan 25, 2014

I have a workbook with multiple tabs, each tab is representative of a person and their ongoing project list. What I am trying to do is to be able to pull rows from each tab identifying the persons active projects based on an assigned due date.

Details: Tabs for Tom/Susan/Phil/Jerry each person will be updating their own sheet with project completion details. Each tab has similar headers with a corresponding due date or completion date. On the master sheet I would like to pull those rows that correspond to the specific dates for all persons.

Example - give me all rows that reflect projects due on 1/21/2014.

View 6 Replies View Related

Hide Rows Based On A Criteria

Oct 6, 2009

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

View 12 Replies View Related

Hide Sheets Based On Criteria

Sep 3, 2006

i would like to hide different sheets at one shut. i tried this, but did not work:

Dim sh As sheet
For Each sh(1 3 5 7 9).Visible =xlSheetVeryHidden

View 6 Replies View Related

Excel 2007 :: Change Colour Of Tabs To Match Other Tabs In Workbook

Sep 22, 2012

I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook

Using this code I get the colour code of the current tab

x = ActiveSheet.Tab.ColorIndex

But when I use this value in a procedure like this:

ActiveSheet.Tab.ColorIndex = x

The tab is a different colour!

View 3 Replies View Related

VBA To Hide / Show Columns Based On Filter And Multiple Criteria

Mar 8, 2014

I have a very large table and i need to be able to Hide/show specific ranges based on:

Filter +and+ specific cell values in columns

brief example of the table : tablee.png

1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.

There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".

View 1 Replies View Related

Excel Macro / VBA That Will Hide Rows Based On Multiple Criteria

May 23, 2012

I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.


1 ROOM: 101
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P

So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.

View 8 Replies View Related

Hide/Show Pivot Table Data Based On Criteria

Dec 12, 2007

I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:

Sub FilterDates()

Application. ScreenUpdating = False


Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated

View 9 Replies View Related

Hide/Show Pivot Table Data Based On Date Criteria

Feb 22, 2008

I have looked at the below Hide/Show Pivot Table Field Items help web pages:

Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria

I am trying to use the above, but with dates in the following format in each cell:

etc, etc

The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.

Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation

View 4 Replies View Related

Hide/Show UserForms Based On Workbook And Worksheet

Sep 6, 2007

I'm just beginning to work with userforms and have created a couple of forms for workbook navigation. The Menu form is set to appear only on the menu worksheet and the Navbar form is set to show on several database sheets. Both forms are set th hide when the workbook is deactivated.

Private Sub Workbook_Deactivate()
End Sub

The problem is that when I return to the workbook the forms do not unhide. Using the workbook_activate event causes both forms to show simultaneously. I also have the worksheet_activate event set to show the applicable form.

Private Sub Worksheet_Activate()
Application. ScreenUpdating = False
ActiveWindow.DisplayGridlines = False
Navbar.Left = Range("B3").Left
Navbar.Top = Range("B3").Top
Application.ScreenUpdating = True
End Sub

Upon returning to this workbook, I only want the appropriate form to show relative to the worksheet that is active. That is, if I was on sheet2 when I switched to another workbook, sheet2 would be active when I return to the workbook and should show the Navbar form. The menu form should show on sheet1, and the navbar form on sheets 2-5. How do I get the correct form to show when I re-activate the workbook?

View 4 Replies View Related

VBA To Hide / Unhide Tabs

Dec 11, 2011

I am looking for a way to hide and unhide tabs. The first sheet of the workbook is an information sheet and I have a dropdown box in cell N3 to select Yes or NO to correct financial issues. If I select NO I would like to also hide tabs for sheet 4 and sheet 22.

I have used this for hiding rows:

If ActiveSheet.Cells(2, 2).Value = 0 Then Rows(146).Hidden = True

Can this be adapted to hide tabs?

View 8 Replies View Related

Hide Tabs According To Cell Value

Apr 21, 2008

I was wondering if it is possible to hide workbook tabs according to cell value?

Take for instance, if cell B30 in sheet "Fee Schedule Finder" is blank then the sheet "ES Schedule" is hidden. I know it would be some sort of VBA code but I am lost on this one.

If I didn't explain well enough please let me know!

View 9 Replies View Related

Hide Worksheet Tabs

Mar 16, 2007

I've done a search for this but cant find the complete answer for what I'm looking for. Basically I've got a "Home Sheet" set-up where the user can only navigate to worksheets via the buttons on the home page (with a short- cut to go back to the home page). It works ok (very basic though) but the user can still use the worksheet tabs at the bottom of the screen to navigate (making this system void).
how I can, either, hide the worksheet tabs at the bottom of excel, or, a better way of navigating these worksheets (there is only about 6 of them).

View 2 Replies View Related

Hide Userform1 Tabs

Apr 5, 2007

Im creating a userform, i have two choices, when a combobox event changes, a second form loads, thats fine, but would it not be more efficient if i were to have hidden tabs on the main form? if so - can anyone advise how i can hide the tab/tabs until the combobox event is triggered.

My "main" aim is to have a VERY quick and slick user interface.

If required i can upload the code - just shout.

View 5 Replies View Related

VBA To Open Workbook And Sum Based On Criteria?

Mar 4, 2014

I have a lot of data that I want to put into another workbook so as to free up space and make the workbook efficient, however, I do not know how to open a workbook in VBA.

Once this workbook is open, I then want to sum the data based on set criteria.

View 7 Replies View Related

Autofilter Based On Criteria From Other Workbook

May 24, 2008

I am looking for a way to search through several workbooks for a row containing the user's inputted data. I was thinking I could Autofilter each workbook but having trouble getting the code to reference a cell value in another workbook.

Sub Autofilter()
With "Sheet1"
.AutoFilterMode = True
.Range("A1:EV1").Autofilter Field:=1, Criteria1:"Workbooks("reference.xls"). Sheets(1).Cells(A2)"
End With
End Sub

I would like to have file.xls Autofiltered by the value in cell A2 which is in workbook reference.xls. Am I going about this the wrong way? Auto Merged Post Until 24 Hrs Passes;Here is the code repaired so it doesn't through the syntax error:

Sub Autofilter()
With "Sheet1"
.AutoFilterMode = True
.Range("A1:EV1").Autofilter Field:=1, Criteria1:=Workbooks("reference.xls").Worksheets("Sheet1").Range(A2).Value
End With
End Sub

But it still isn't working. Something is causing trouble at the With statement...

View 2 Replies View Related

How To Hide Available Tabs In Excel Worksheet

Jul 16, 2014

knew if there was a way to hide the available tabs in an excel worksheet.

I know you may be thinking just right click and hide, however what I am looking for is a little different.

Basically I have a Macro Enabled work sheet which works on a "one click" system. The work sheet is full of buttons with assigned macros and data validation drop down boxes which link to many different tabs. The entire sheet is for information only so I will be locking the sheets to avoid data manipulation. The problem with right clicking and hiding the tabs is that it interfears with the macros.

All I really want to do is make these tabs not visible. I have set the sheet to open in full screen mode and they obviously are still there, but this is basically what I am looking to do. Not so much hide them in terms of sending them into the background, more so make them "drop off" the bottom of the screen to they are still active but not visible.

View 6 Replies View Related

Macro To Hide Tabs And Function Bar

Jan 29, 2014

I am trying to find a library with vb/macros that can hide and unhide all worksheet tabs except one, the formula, and the menu bar.

View 1 Replies View Related

Conditionally Hide Tabs-If They Are Empty

Jul 28, 2009

I have about a dozen tabs, all of which have vlookups in them and calculate based on what a user pastes into Sheet 1. I need a macro that hides all tabs that have no data, essentially tabs where A1 = ""

However, it also needs to unhide tabs as soon as there is data. So if the user pastes new data into Sheet 1 the vlookups, on the other sheets, still need to run (even if they are hidden) and then check against the macro to determine whether to be hidden or unhidden.

View 3 Replies View Related

Hide Specific Worksheet Tabs

Aug 7, 2008

Is it possible to hide specific worksheet tabs.

I have a workbook with many worksheets and I only want to hide Sheet1 and Sheet2.

View 9 Replies View Related

Macro To Hide And Show All Tabs

Nov 19, 2009

I have looked through alot of this board and see the codes to hide all tabs old and new and code for each sheet.

My question is:
Is there a macro that can be used to do this as well. Say a button to hide all tabs and a button to show all tabs or does it only hide/show when the workbook is re-opened?

View 9 Replies View Related

Hide Tabs In HTML View

Jun 28, 2006

Does anyone know of a way with VBA to hide the tabs when saving excel as a web page?

View 7 Replies View Related

Copy Rows From One Workbook To Another Based On Criteria

Aug 4, 2009

I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.

This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.

View 2 Replies View Related

Lookup Data In Another Workbook Based On Criteria

Sep 8, 2006

I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:

Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.

View 3 Replies View Related

Update One Workbook From Another Based On Unique Criteria

Mar 11, 2008

I have two workbooks. I use excel 2003

I need to update the master workbook with data from the invoices workbook using the following criteria.

In the master workbook column C has unique job numbers.
In the invoice workbook column F has unique job numbers.
Both worksheets are the first worksheet in both workbooks.

If the job number in the invoice workbook is already in the master workbook do nothing.

If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.

If the job number is not in the master workbook add it to the master workbook.

To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ...

View 4 Replies View Related

Copy Data To New Workbook Based On Criteria

Apr 4, 2008

I have a workbook that is used to track job estimates.

What I would like to have is a macro that would search through column N on the original file and if any of the cells have "No RFE" in them - generate a new workbook with only this list that would include any data from columns C-U copied from the original file.

I've been seaching the forum, but so far have had little luck finding something that suits my needs.

View 9 Replies View Related

Deleting Workbook Based On Criteria In Active Worksheet?

Aug 16, 2012

I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.

View 2 Replies View Related

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