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?
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.
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? ......
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
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: Type Selection
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!!
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 Country Scotland
Start Date (dd/mm/yyyy) 30/04/2013
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.
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
I have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:
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.
On the Form which I call my main menu, theres a help button, with this code
Private Sub cmdHelp_Click() MsgBox ("Help file not ready yet, good luck on your own, Have a Nice Day!") End Sub For some reason when this is click, and after the user clicks ok on the msgbox, the frmmain, which this command is on, minimizes itself
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
I've created an Excel spreadsheet (Excel 7) to help me price sign printing jobs consistently. In my spreadsheet a few cells allow me to enter markups for materials & labour rates. These are used for calculating the retail price.
I would like to give my spreadsheet to some of my customers so they can calculate the retail price of a job themselves BUT, I would like to hide the contents of those few cells where I fix the markup. How can I achieve this?
I'm trying to hide all but one duplicate value in a list; I have a report that can give multiple results for each record, but to make it cleaner to read I want to hide the duplicate values, such as customer names etc. after they have first appeared. I'm aware this may not be a very clear explanation so have attached an example spreadsheet.
The left-hand list is similar to what I would start with and the right-hand list is what I am looking to achieve. Conditional formatting will format duplicates but will obviously all to all and can't use the remove duplicates function as it will take away the unique values alongside them in the row. Preferably I'm after a solution that works in both '03 and '07 but it's not a disaster if I can only do it in '07.
I was wondering what would be the best way to go about hiding either rows or columns based on whether a particular cell is empty or not. The reason being, I want a graph and table to only display data when present.