Hide Rows On Multiple Worksheet Pages

Sep 7, 2006

This code will hide row 2 across Sheet1, Sheet2 and Sheet3 while recording in the macro record mode but when played back after recording will only hide row 2 on Sheet1. How do I get this code to work?

Sub MultiplePageHideRows()
Sheets( Array("Sheet1", "Sheet2", "Sheet3")).Select
Selection.EntireRow.Hidden = True
End Sub

View 5 Replies


Hide Multiple Rows When The Text Changes In A Cell On A Worksheet

Oct 28, 2009

The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.

What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.

If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.

I have found the following
If Target.Value = 1 Then
End If
End Sub

Which I have changed to:

If F4.Value = Green Then
End If
End Sub

So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?

View 9 Replies View Related

Inserting Rows On Multiple Pages

Mar 21, 2006

I am working on a database with Names and Volunteer Hours worked over about five years.

What I have is one sheet with names and info, and the respective sheets for each year and different events they worked that year.

on each year sheet I have only the first and last name, which is all that I need for personal info. I have these names on the seet using the sheet1!A1 formula. However whenever I add someone new they will not update the new row on the other sheets, they will simply ignore it. How can I make it respect my row updates?

View 3 Replies View Related

Print Macro For Multiple Ranges And Graphs On One Worksheet To Separate Pages

Mar 26, 2014

There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.

Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?

Sub Load_Data_Report()
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
' Keyboard Shortcut: Ctrl+j

[Code] ........

View 1 Replies View Related

Hide Worksheet That Contains Over 15k Rows

Mar 12, 2009

I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.

I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.

I don't know exactly if I should use a checkbox, listbox, combo box ect.

View 9 Replies View Related

Hide Pivot Pages But Enable Drilldown

Dec 1, 2007

I need to send out mailings to a group of attorneys, and each attorney should get a separate mailing. I take the main pivot tab, which reads the information for all the attorneys from the detail tab, and then do "show pages" by attorney name.

I then use VBA to kick each tab into a new workbook, so each attorney has a separate workbook, but the pivot still reads from the detail from the main workbook's detail tab.

I need to prevent the attorneys from viewing eachother's information, but if I password the sheet, they can't drilldown to detail.

how to enable drilldown for each attorney but prevent them from viewing other attorneys' mailings?

The only way I can think of is to move the detail tab to each individual attorney's workbook and delete all of the other entries, and this is too much work.

View 3 Replies View Related

Hide Columns / Rows While Having Worksheet Protected?

Jun 19, 2014

I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?

View 3 Replies View Related

Make Rows Hide In One Worksheet Based On Value In Another?

Apr 30, 2012

I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.

I have put this code on the features segment tab but it is not working.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "B40" Then


View 3 Replies View Related

Macro To Show / Hide Rows On Background Worksheet

Apr 9, 2014

Getting a macro to work. I've looked through the forums pretty extensively but ad I'm not too hot with the old vba, I haven't been able to get it working.

I have two worksheets in the same workbook. The first worksheet, let's call it Input, is one for data entry; and the second one, let's call in Output, is formatted for printing.

There are 8 drop down boxes from a data validation list, that when a particular option or three are selected, I need the Output worksheet to unhide only the rows associated with those options.

In trying to get this all to work, I'd added a function in the cell to the left of each option in the Output page that will show the text "show" when that option is selected on the Input page, or the text "hide" if not selected.

I think I could do this with some time with a clunky and long macro, but would prefer to us some kind of "for each" option to hide rows that have "hide" shown in column A, as I'm looking at a range of 100~ cells.

View 3 Replies View Related

How To Hide Specific Rows Based On Values In A Different Worksheet

Mar 7, 2014

Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.

View 5 Replies View Related

Hide Rows Where Multiple Cells = 0

Aug 10, 2009

I am looking for a macro that will hide the rows when the cells in columns D:Z each = 0. I cannot use something that hides the row if the sum of the cells in that row = 0, because some of the cells may contain positive & negative values which cancel each other out within that row.

I would include code, but I can't find anything on the web or on this site which I could use. Plus, I am really bad at making these up from scratch.

View 6 Replies View Related

VBA - Hide / Unhide Multiple Rows

Oct 26, 2011

I have a workbook with multiple tabs. Each tab has about 600 rows. In each tab, I need to hide/unhide rows based on the value of a particular column in that row. For example,

Row1: 10,7.89,John,TRUE
Row2: 16,9.08,Ram,FALSE
Row3: 98,8.09,Joseph,FALSE
Row4: 76,1.23,Harry,TRUE

Using the 4th column (that has either TRUE or FALSE), I need to hide entire row. I am using a loop on the range and hiding each row. But it is taking about 4 minutes for each tab to loop through the 600 rows and hide/unhide the required rows. Is there a faster way to achieve this? Following is my code.

Sub Toggle_Rows()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Range("D1:D600")
If UCase(Cell.Value) = "TRUE" Then
Cell.EntireRow.Hidden = Not Cell.EntireRow.Hidden
End If
Application.ScreenUpdating = True
End Sub

View 1 Replies View Related

Hide Rows Based Over Multiple Columns

Jan 29, 2013

a code that will search each cell across 4 columns and hide the row only if all cells are blank. The macro should search columns "b", "c", "e", and "f" to display all rows where at least one of the cells has a value.


Col.B Col.C Col.D Col.E Col.F
1. 123 xxxxx 150
2. 56 xxxxx 50
3. (blank) (blank) xxxxx (blank) (blank)

In this ex. row 3 would be hidden.

View 1 Replies View Related

Hide Multiple Rows Based On Cell Value Being Blank

Feb 20, 2013

I have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.

These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).

In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.

Code that will hide multiple rows (45) based on a cell value being blank.

View 2 Replies View Related

Hide Rows Based On Condition - Multiple Sheets

Mar 7, 2008

I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.

Sub Hide_Rows()
Dim i As Integer
For i = 3 To 418
Sheets("AFA - UMBI").Select
If ActiveSheet. Range("b" & i).Value = "2008-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub

View 7 Replies View Related

Combining/merging Multiple Rows From One Worksheet Into One Cell In Another Worksheet

Jan 28, 2010

I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.

This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.

I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.

I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.

View 12 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 Rows Based On Multiple Conditional Formatting

Aug 6, 2009

I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:


The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...

View 3 Replies View Related

Can't Delete Pages From Worksheet

Mar 3, 2014

I was trying to print a workbook which was about perhaps 20-30 sheets of roughly one page of printed info each. However, I saw the print job and it said it was printing over 7,000 pages!!!

It turns out there were about 4-5 worksheets which were blank (and also didn't have gridlines even though gridlines were checked on in options).

When I switched the sheets to page break view, lo and behold each sheet contained a few thousands "pages". I simply can't seem to reduce it down to a relatively normal size.

I tried going right past the edges of page 1 and deleting all columns/rows to the right and bottom of it to no avail. I selected the whole worksheet and removed all borders and any cell content with no luck.

I saw other "solutions" on the internet about copy/pasting the other unaffected sheets to a new workbook and deleting, but this seems cumbersome.

View 1 Replies View Related

Setting Repeating Pages In A Worksheet?

Aug 16, 2014

I need to prepare count sheets for stock take on my company and I have a long list for data with hundreds of locations. What my colleagues have been doing is to filter the locations 1 by 1 and clicking print because we want the page numbering to be 'page 1 of 3', page 2 of 3 etc. for each location.

For example

Location a has 2 pages we want it to be page 1 of 2, page 2 of 2

Location b has 1 page we want it to be page 1 of 1

This is so that we can detect if any count sheets have gone missing.

I want to be able to print everything at 1 go, without filtering the locations manually and clicking print as it takes hours to do so.

Is there anyway I can do it? I have some knowledge on excel but not macros.

View 1 Replies View Related

Show Pages Feature In Pivot Table - Is It Available In Regular Worksheet

Jan 22, 2014

One of the best tools that Pivot Tables let's me use is the ability to create new tabs very easy. Anything field placed in the Report Filter is doable. My question is, is there a similar feature available in the regular data worksheet?

View 1 Replies View Related

Web Queries For Multiple Pages?

Dec 10, 2008

My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )

I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.

View 7 Replies View Related

Delete Row From Multiple Pages

Oct 20, 2011

I have a command box that I type a number into, when you type this number and hit "OK" it searches that sheet in column B for that number, if it finds that number it deletes it, if not close. This works perfectly. I have 7 other sheets however, and I want it to search those sheets as well for the same number (still in column B) at the same time, if found delete the row, if not keep going. I have it working on Sheet2 (laser) but every time it fails on Sheet3 (punch). The thing that kills me is it's the same code. I have tried numerous things but have had no success.

Private Sub CommandButton1_Click()
Const strDelete As String = "TextBox1.Value"

Dim DeleteValue As String
Dim rng As Range
Dim rng2 As Range

[Code] .........

View 6 Replies View Related

Workbook With Multiple Pages

Mar 25, 2009

I have a workbook with multiple pages, two of which are labled "Summary" and "Questionnaire" On the Summary sheet, I would like to have a button or permanent dialouge box that asks the user if he wants to print the "Questionnaire" sheet. The button should have 2 options YES and NO. If they say Yes, change the contents of cell A2 on the Questionnaire page to "1". If they selct NO, make contents of A2 on the Questionnaire sheet to "0"

I have a macro that reads the contents of A2 on all sheets and prints if >o

View 9 Replies View Related

Repeat Information On Multiple Pages?

Jan 24, 2010

I recently undertook an excel based knowledge test and whilst I haven't been informed of the answers/scores yet I have been frustrated with my inability to find the answer to what seemed like a simple question on the test....

Q: Without using copy and paste, put A1:H11 and its associated formulas and formats onto all 7 worksheets within the workbook.

Bearing in mind all questions on the test generally used only a few keystrokes, I felt fairly stumped and with copy and paste excluded from the options I am convinced I answered incorrectly. But the worst of it is I left fairly contently from the test and thinking I would get home and satisfy myself with the answer outlining a simple oversight on my part, have been shocked that I've been unable able to find one anywhere, is anybody able to put me out of my misery here as it's beginning to really bug me!

View 7 Replies View Related

Printing Multiple Pages On One Page

Feb 3, 2010

I have a list that I need to print. It is only 2 columns, but 12 pages long. I would like to print 4 or 6 pages, side by side on the same sheet. I tried changing the print setting to 4 pages per page. But, when I looked at the print preview, it showed only 1 page per page.

View 11 Replies View Related

Conditional Formatting Over Multiple Pages

Dec 17, 2012

I need setting up a conditional rule over 2 pages.

Page 1 displays a list of countries.

Page 2 pulls in the RSS feed from the BBC World News website.

I need to find a way to set a rule that will highlight the country on page 1 if it is mentioned anywhere in page 2.

View 7 Replies View Related

Printing Multiple Pages Per Sheet?

Jan 22, 2014

I'am trying to create a document which automatically prints the next page on the same sheet.

I have created a spread sheet which prints the first 5 rows in the top of every page, but I can't figure out how to make excel print two A4's on one A3 side-by-side.

View 2 Replies View Related

Multiple Calculations From Different Pages - Macro

Feb 3, 2014

I'm working on this excel tool that I would like to make as user-friendly as possible. It would act like a survey almost where the user would choose responses or input values for specific questions and then on other worksheets it would have the actual calculation and math.

I see that if one were to add a value onto worksheet 1 and which would be a variable that is essential to a calculation on worksheet 2 it does not run automatically.

If there is a combo box w text values that also needs to have a calculation on another page, can i just do an IF statement on worksheet 2 with combo boxes?

View 3 Replies View Related

Code To Print Multiple Pages

Nov 8, 2008

I have a macro that cycles through several student ID numbers and prints a progress report for each one. It works great, but I would like to add an option to pick which printer to use at the beginning of the code. In other words, when I run the macro, the printer box opens and I can pick the printer. When I click okay to print, the macro continues.

View 4 Replies View Related

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