Re-Naming Sheets Per Cell Data & Hiding Sheets
Aug 26, 2009
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
View 4 Replies
ADVERTISEMENT
Nov 10, 2008
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
View 14 Replies
View Related
Oct 18, 2006
I have a workbook with a sheet titled Variables. There are 6 additional sheets in the workbook and each sheets needs to be named with the cell contents of A2:A7 in the Variables sheet. I have attached the workbook and you can look at the names in the sheets tabs to see what I'm trying to do here.
The workbook is too large to attach but can be viewed here:
http://www.shuffleupanddeal.org/name_sheets.xls
or
http://www.shuffleupanddeal.org/name_sheets.zip
View 9 Replies
View Related
Jan 5, 2013
I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.
I need columns
A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?
I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.
View 3 Replies
View Related
Mar 21, 2007
I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.
I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.
if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.
View 14 Replies
View Related
Oct 28, 2009
I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..
View 14 Replies
View Related
Apr 20, 2007
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies
View Related
Feb 26, 2012
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
View 2 Replies
View Related
Jan 4, 2013
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
View 9 Replies
View Related
Apr 30, 2009
Can a macro be written that will automatically NAME a sheet with the contents of a particular cell?
View 3 Replies
View Related
Apr 22, 2008
If I opened a new workbook so i had sheet1 sheet2 sheet3... Starting at sheet1, how could i move on to sheet2 using VBA without actually naming it....
Is there a selectsheet.next or something?
View 9 Replies
View Related
Jan 3, 2009
I want my charts to be located in a new sheet each. I also need their names to start with "GR-Chart[number here]". So they should be GR-Chart1, GR-Chart2, GR-Chart3,......
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= "GR-Chart" & [this part I got wrong]
Do I use i? or .Count?
View 9 Replies
View Related
Apr 21, 2009
I have got this macro working OK but now I need to add a 2 letter prefix/suffix depending on what time it was created (am or pm). Detail: If the sheet is created between 0600hrs and 1800hrs then "DS" and likewise between 1800hrs and 0600hrs then "NS". Outcome: The sheet will then have a name like - "22 Mar DS"...code included below
View 4 Replies
View Related
May 5, 2006
I have a huge db that has a column "C" with a list of names. I have been creating tabs and naming them according to that list. I wonder if there is anyway I can create and name the tabs automatically. I already searched for threads in the forum, but only found this this backwards "tabs to cells". By the way, the names in column "C" sometimes repeat.
View 2 Replies
View Related
May 14, 2006
Im sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select
View 5 Replies
View Related
Feb 28, 2014
Is there a macro available which will create a pre-defined name for a pivot sheet, which will contain the word "Pivot" followed by a underscore "_" and the name of the sheet on which the pivot is applied.
Example: I have data on a sheet called "Salary" and put a pivot on "Salary" on a new sheet, the new sheet should be auto named as "Pivot_Salary"
Am using Excel 2013
View 1 Replies
View Related
Dec 3, 2013
I have a workbook with the following sheets i do not want to hide called "Detailed Template", "INTERFACES", "STATUS".
I then have a number of worksheets which have their names as cell addresses i.e. "E4", "E5", "D4", "F4", "G7" and so on.
I would like to hide all worksheets (except the 3 mentioned at the top [which are sheets 1-3]) and any which contain say the value of '4'. This value is defined by a cell in the "INTERFACES" worksheet say 'A1' for example.
I want the macro to be able to automatically do this for which ever value is in 'A1'. So if it were to change to '7' it would hide every sheet that doesn't contain '7' in the name.
View 6 Replies
View Related
Jun 11, 2009
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
View 9 Replies
View Related
Aug 19, 2013
I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.
Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.
View 6 Replies
View Related
Jan 8, 2013
Cells B2:B100 contain a list of sheet names, cells C2:C100 have entries TRUE or FALSE.
Is there away to setup a macro that goes through the list and if false, make the sheet very hidden, if true it should be visible?
View 1 Replies
View Related
Jun 3, 2013
I wrote a macro that hides every row that contains an "X" in column X for every sheet. The problem is that it runs very slowly (assumedly because it is checking every cell on every sheet). Is there a way to clean this up and make it run faster?
My code:
Sub HideRows()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim Cell As Range
For Each Cell In ws.Range("X:X")
If Cell.Value = "X" Then
Cell.EntireRow.Hidden = True
End If
Next
Next ws
End Sub
View 2 Replies
View Related
Aug 8, 2008
Why this doesn't error but yet it doesn't work either.
Sheets("2 plans, 3 plans, 4 plans").Visible = False
Sheets("1 plan").Range("a26:a416").EntireRow.Hidden = True
View 9 Replies
View Related
Nov 21, 2007
As many are aware, there is an option to make a sheet very hidden. However, a "very hidden" sheet remains visible in the VBE project window. A user with basic VBA knowledge can easily selected the hidden sheet & go in the property window and change its visibility setting to "-1 Sheetvisible". Is there any way to make hidden sheet invisible in VBE as well?
View 3 Replies
View Related
Aug 7, 2014
i have a list of services with "yes" or "no" options in the column beside it. Description for each service is given on separate sheets (Workbook sheet 1, Workbook sheet 2..etc). i want to format it in a way that if i choose "no" for one of the services, its description sheet hides.
View 3 Replies
View Related
Apr 27, 2012
I would like to be able to hide the tab i am in and unhide another tab at the click of the button,
I have tried recording the macro myself and it works up to a point,
I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)
View 4 Replies
View Related
Aug 24, 2006
I have a worksheet that veryhides all the sheets but one when closes and when opens if unhides the ones a user needs. For some reason the quick key Alt + PageUp or Alt + PageDown doesn't work until each sheet is scrolled through by clicking on their tab with the mouse.
View 4 Replies
View Related
Aug 29, 2008
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
View 7 Replies
View Related
Jan 25, 2014
[URL] ....
I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.
View 1 Replies
View Related
Aug 26, 2008
='NAME OF SHEET'!$#
where # equals the exact cell in the other sheet I want to copy. I'm also starting to realize that with this formula, Senior Monkette and I could take Dingleberry and Bliddiboo and combine them into a more powerful and robust spreadsheet (called "Voltron!").
1. If we're going to be sending Voltron! to the client, all I would need to do is password protect, then hide the Dingleberry sheets so that the client doesn't look at them accidentally, right?
2. If I hide and protect the Dingleberry sheets, the cell information will still show up on the Bliddiboo sheet, right?
3. Since Senior Monkette isn't as Excel-savvy as I am, the entire process would have to be as painless as possible. What I was thinking of doing was having one master Voltron! where Senior Monkette could make her changes and updated. Then every week, save the entire thing, protect/hide the Dingleberry sheets, and then save a copy as a separate Voltron! file, marked by the date, which then gets shipped off to the client. Is there a macro I can run that will do that all with the press of one button? (And how do I install macros?)
View 10 Replies
View Related
Nov 4, 2008
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
View 9 Replies
View Related
