Unhide Specified Multiple Sheets In A Workbook

Jun 27, 2006

I would like to unhide specified multiple sheets in a workbook at one go. What I'm doing now is something like this:


Sheets("Source 1").Visible = True
Sheets("Source 2").Visible = True
Sheets("Source 3").Visible = True
Sheets("Source 4").Visible = True

I managed to get the array function when I want to hide the sheets:


Sheets(Array("Source 1", "Source 2", "Source 3", "Source 4")).Select
ActiveWindow.SelectedSheets.Visible = False

but I can't do this when I want to unhide them. I do not want to unhide all sheets in the workbook, only those I specify in the code. How can I use the something like the array code to unhide the sheets.

View 9 Replies


ADVERTISEMENT

Unhide Multiple Sheets Using Drop Down?

Oct 7, 2011

I am making a workbook that populates most of the data off of a data sheet, but i need it to hide the 10 other sheets until they are needed. I was hopint there would be a way to hide all but the data sheet, then use either a drop down box or if necessary a checklist (NOT my first choice, but i'll do it if i have to) to select the sheets that the tech will need to print or e-mail. Is there a way to do this:

View 1 Replies View Related

Unhide Rows In Multiple Sheets?

Oct 12, 2011

Is it possible to unhide a row in one sheet and the same line can be un hidden automatically ini 2 other sheets within the same workbook

View 1 Replies View Related

Cannot Edit A Macro In A Hidden Workbook. Unhide The Workbook Using The Unhide Command?

Apr 23, 2009

using Excel 2007/Vista

when I go a new edit a macro, I get the message

"Cannot edit a macro in a hidden workbook. Unhide the workbook using the unhide command"

Where do I find the relevant "unhide command"?

View 9 Replies View Related

VBA Code - Unhide Multiple Sheets With Password?

May 18, 2011

Am trying to find the VBA code to "ask a user for the password to unhide multiple sheets/tabs"

I have got the script for unhiding all sheets which is:

Sub Sheets_Unhide()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = True
Next
End Sub

What i would like though is for it to prompt with a password first before the code is executed for unlocking 50 odd sheets.. I know its not 100% fool proof, but would do for what i need!

View 6 Replies View Related

Printing Multiple Sheets In Workbook With Hundreds Of Sheets

Feb 18, 2014

I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.

The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.

View 3 Replies View Related

Copy Data From Multiple Sheets In Workbook To Different Workbook But On One Worksheet

Feb 7, 2014

I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.

View 4 Replies View Related

Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

View 1 Replies View Related

Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook

Mar 16, 2008

I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.

View 9 Replies View Related

How To Protect Multiple Sheets In Workbook

Jun 19, 2006

How to protect multiple sheets in a workbook and not having to protect them 1 by 1. I have copied and pasted all the different macro's/visual basic script that has been posted for that purpose, but I can't get it to work and I'm always getting an error. The error from visual basic says "compile error: invalid outside procedure" and then the visual basic screen shows the following:

Range ("AM52")
If Ans >= 0 Then
"+."

It does not like the "AM52" part because it is highlighted in the error message.

Also, the error I get from recording my own macro step by step is "Run time error 40036" from visual basic.

View 8 Replies View Related

Copy Multiple Sheets To New Workbook?

Mar 31, 2014

I am looking for some code to copy the exact values in a couple sheets over to a new workbook. There are images in the sheet that need to come over and formatting of cells including merging.

View 2 Replies View Related

Using Macro For Multiple Sheets In Same Workbook?

Oct 18, 2013

I have a macro listed below that I would like to use on multiple sheets within the same workbook.

Sub PrintMacro1()
'
' PrintMacro1 Macro
' To change print format from landscape to protrait

[Code].....

View 4 Replies View Related

Sort On Multiple Sheets In Workbook?

Nov 18, 2013

Trying to sort on multiple sheets in the workbook. The range that is to be sorted will vary on each sheet, so i would like to write the range as .end(xlDown).Row and then do the sort. So far, what I have written is giving me a type mismatch error

Code:
.Sort.SortFields.Add Key:=Range("B2:B" And Range("B2")).End(xlDown).Row, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

View 3 Replies View Related

Vlookup In A Different Workbook But Multiple Sheets

Jan 18, 2007

I have this one sheet (File 1) where I have the unique codes of people and am trying to perform a vlookup() for a value by specifying the lookup in a dfferent workbook (File 2) but multiple sheets. In other words the data (unique codes) is scattered in different sheets in that workbook (File 2). I need to perform a vlookup() wherein the codes finds a match in whichever sheet in File 2 and the value in the specified column be entered against the respective cell in File 1.

I tried selecting all the sheets in the formula but it does not return a value for all the inactive sheets in File 2. Vlookup() works fine for the Active sheet only.

View 9 Replies View Related

Searchbox On Workbook Multiple Sheets

Apr 11, 2007

I have a workbook that contains 25 worksheets and in those sheets there are common items for different equipment, all the sheets are exactly the same when it comes to lay out, except the first sheet, the data differs accordingly on the subsequent sheets. Being new to excel I do not have a clue how to set a seach box at the click of a button and copy and paste the part searched for thru all the sheets in the first sheet and at the same time deletes the latest search when a new one is carried out.

View 9 Replies View Related

Exporting Multiple Sheets To Another Workbook

Feb 12, 2010

I expect this may have been done before, I have a workbook with 6 sheets. Each sheet lists a register, each register has between say 12 & 25 column, 1 column in each sheet denotes a "y" for exporting to another spreadsheet (not the same column in each sheet though)

what I would like is some code that will open a new workbook, and copy each of the sheets (all the fields/columns etc) into the new book, but only the lines that have the "y" next to them.

I have codes that export one sheet , but its not really relevant to what i need to do.

View 9 Replies View Related

Copy Records From Multiple Sheets Into New Workbook

Feb 2, 2012

Following problem:

I have a workbook containing data about some students grade on different sheets (4). The structure is almost the same on every sheet:

The first 2 row contain headers the each row contains a name and result for different test:

name - test A - test B - ...etc.
Peter - 90 - 60 -....etc.

there are 25 columns on each sheet - 4 sheets in all.

What I want is, to consolidate each students results in a separate workbook.

View 1 Replies View Related

Macro Protecting Multiple Sheets In Workbook?

Sep 6, 2012

I have a large workbook with 10+ sheets i need to protect all at once. I have created a macro in VBA below. It works, but I would like another user to be able to change formatting on the sheets while they are protected. below is my macro.

Sub ProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String

[Code].....

View 1 Replies View Related

VBA To Copy Multiple Sheets Into New Workbook And Save

Jun 23, 2014

I have managed to pull together code that does the required task - save two sheets from a work book in to a new workbook on to a dorectory each day. However I woul;d liek to paste special the values and cannot figure our how to reference that on the below:

'saves text file in day on day folder
Dim WS As Worksheet, CheminDest As String, fNAME As String
'create directories as needed
On Error Resume Next
CheminDest = "T:DMRatesReportsChecks" & Year(Date) & ""

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

View 3 Replies View Related

Total A Cell From Multiple Sheets Within A Workbook

Nov 5, 2007

I have a workbook containing 120 sheets. Each sheet contains a column labelled "Subject", and a row below labelled "Totals:" with a numeric value in the intersecting cell.

I need a formula that will total the value in all these cells on the last sheet.

The trouble is, the cell address of the intesecting cell fluctuates somewhat from sheet to sheet because the column and row for the "Subject" and "Totals:" are not always the same.

******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF30=
ABCDEFGHIJKLM5**Check*Date*01/05/2007*to*09/28/2007,*Job*616003*to************6**6440009,...,Job#*642010*-*HEATHER*GLEN@MONUMENT*BO************7*************8***Record#****Check#**Period***Employee*****9****Comp*Code*********Hours**10*************11*************12*************13*Totals*by*Comp*Code:*************14*Comp*Code*************15*****Hours****Wages***Overtime**Subject***Rate*16*************17*5183***PLUMBERS*UNDER*$23*************18*****19.00****376.50****376.50***11.5400*19*5187***PLUMBERS*ABOVE*$23*************20*****5.00****120.00****120.00***6.5300*21*************22**Totals:***24.00**496.50*0.00496.50**Sheet8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I want to be able to total the "Subject" wages for codes 5183 & 5187 (unfortunately, the payroll amounts are located one row below) for all 120 sheets in the workbook.

View 9 Replies View Related

Unhide Only Certain Sheets - Not All

Nov 17, 2012

I am trying to use the macro I downloaded from OZ Grid called "Enable Macros" to check if macros are enabled. Its works beautifully, except for the fact that it will unhide all sheets if macros are enabled. I would like to only unhide certain sheets. The sheet names are:

Sheet11(Weight & Balance)
Sheet5(Nav Log)
Sheet2(Database)

I believe this is the part of the code that must be modified, but I'm not sure how:

Code:
Public bIsClosing As Boolean
Dim wsSheet As Worksheet

Sub HideAll()

Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets

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

Also, I am planning on locking this workbook. If this macro can still run if the workbook is locked?

Link to where the macro can be downloaded:

View 8 Replies View Related

Unhide All Sheets

Dec 5, 2006

I receive a lot of excel files and would like a macro that I can run to unhide any and all hidden sheets. The sheets will have different tab names. I looked on this forum and could not find the answer to this question, usually I can.

View 5 Replies View Related

Create IF / LOOKUP Formula To Look At Multiple Sheets Within Workbook?

May 5, 2014

New to using Excel formula's and am trying to create an IF/LOOKUP formula to look at multiple sheets within a workbook and display the information within the 'compare' sheet.The yellow cells are where data will be entered.

What I am trying to achieve:

Type the store numbers on the compare sheet (B2 and D2). For the sheet attached I have put 190 (in B2) and 2012 (in D2)

B2 store shows the predicted and actual values of 190 in columns B & C

D2 store shows the predicted and actual values of 2012 in columns E & F
(No need to worry about variance and difference columns)

So, if I change the store numbers in B2 and D2 to any of the sheet numbers, I want it to display the correct info for that particular store within the compare sheet.

I have attempted a formula, which you can see... I have basically looked at some previous sheets that had IF and LOOKUP on it and tried to replicate that for my sheet, but with no luck.

View 3 Replies View Related

How To Protect Cells/columns In Multiple Sheets In A Workbook

Sep 9, 2009

I'm trying to protect the contents of columns in multiple sheets in a workbook. I've tried to group the columns ( - months, i.e. sept, oct, nov 09 through to march 10) but when I go to Protection options the 'sheet' protection option is not available, all I can do is protect or unprotect the entire workbook.

I've also tried to protect the workbook, but individually set the specific columns in each sheet to be protected (repetitious but seemingly unavoidable), but this doesn't work well either as there's now a mix of a protected workbook and protected columns, both with individually set passwords to unprotect... which seems a bit silly.

There must be a way around this? - the columns are H, J, K and L, and the entire contents of the summary sheet '2009-10 Financial Year'... basically I'd like to be able to protect all of the contents of all of the cells here, ideally just with one password.

View 9 Replies View Related

Repeating Columns And Information On Multiple Sheets In Same Workbook

Nov 18, 2013

Is it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?

View 3 Replies View Related

Merge Multiple Sheets Of Existing Workbook Into One Sheet?

Mar 27, 2014

I want to merge different worksheet of an existing workbook into one by macro .I did not want to repeat the rows label for each worksheet data.Also I want to get at right hand side i.e in G column the data to be extracted for respective worksheet in the merged data.I have enclosed in attachment an expected solution in a sheet name "merged".However the number of worksheet is here only 3 for sample purposes.However,in reality there is more than 3 .

View 9 Replies View Related

Excel 2007 :: Merge Multiple Sheets Into One Workbook

May 1, 2012

I am using Excel 2007. I'd like to merge multiple sheets (about 13) into one workbook. The sheets are placed in one folder, and they all include 2 sheets, - only the first sheet should be merged into the final workbook.

The sheets will be updated every 3 months and merged again (-thus replacing the old data).

View 2 Replies View Related

Programming Macro To Operate Over Multiple Sheets Or Workbook

Aug 1, 2012

I'm trying to set up a way to format column widths and row heights in a macro (so that it can read a value from a cell and format a column to that width). I've used the code below which can be repeated for the number of rows and columns desired below

Worksheets("Sheet1").Range("d1").ColumnWidth = Range("d1")

However, I want to be able to do this on a workbook that could potentially have 40+ sheets. Is there a way of applying this code to operate over the whole workbook rather than just one sheet at a time?

View 7 Replies View Related

Copy Range From Multiple Sheets & Paste To Another Workbook

Sep 13, 2007

I am looking for either formula which will allow me to copy data from a workbook with multiple named sheets into a workbook with a single named sheet. For instance workbook 1 has sheets named bob, sue, and tom and there is a workbook 2 which only has sheet bob. I want to copy the range fo data from sheet bob in workbook1 to workbook 2 as long as workbook 2 has sheet bob.

View 2 Replies View Related

Unable To Unhide Sheets

Oct 21, 2009

I have a workbook with several sheets that I created some time ago. I have hyperlinks on the main sheet which point to other sheets in the same workbook which can be viewed when using the hyperlink, however the linked sheets are hidden. Somehow in this workbook I removed both the horizontal scroll bar and the lsiting of sheet names. I can not figure out how to get them back so I can unhide and or add new sheets.

View 4 Replies View Related







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