Macro For Selecting Hidden Sheets?
Dec 26, 2013
I have spreadsheet with 13 sheets the first of which is a title page, what I wish to do is hide the other twelve sheets and somehow be able to select them from a drop down box (or similar what ever works with a macro)on the front page, the selected sheets would then appear unhidden.
I am OK with formulas etc but macros are beyond my ability even trying to follow examples & tutorials online has me lost.
The sheets all have specific but different names and none need to be selected more than once (although if possible it would be good). I understand where to put the macro in but as to how it is written ......
View 7 Replies
ADVERTISEMENT
Sep 2, 2009
I have the following code which works fine when all of the sheets are not hidden:
View 3 Replies
View Related
Aug 21, 2009
OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"
Is there anyway around this?
Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?
View 13 Replies
View Related
Apr 30, 2008
I have a workbook that I am continually adding and removing pages from. The pages are used together in groups of 3 and as a result are named 1.1,1.2,1.3; 2.1,2.2,2.3 and so on. I have a macro that works on sheet 1.3 and now need it to repeat on sheets 2.3, 3.3 etc depending on how many sheets I have open and active in the workbook. Is there anyway that I can create a macro that will work on all sheets that have titles ending in 3 and then end if there are no more? Thus it would work if my workbook goes to either 2.3 or to 7.3 and so on.
View 14 Replies
View Related
Feb 28, 2014
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies
View Related
Jun 15, 2012
Here is my macro
MyFileName = Sheets("Macros").Range("B1").Value
MyFileNameTwo = Sheets("Macros").Range("B2").Value
Sheets("Blank").Select
Sheets.Add After:=Sheets("Blank")
[Code] .........
Issue with array that's bolded The way it is set up is to rename the 3rd sheet to MyFileName and rename the last sheet to MyFileNameTwo. The file names will remain constant. They will always be the 3rd and last sheets, but the number in between will vary. Is there anyway to select the 3rd sheet through the last sheet to delete these? When I use the array it wants sheet names but those are based on multiple variables in other workbooks.
View 3 Replies
View Related
Feb 19, 2014
I have some reports that I run that go out to analyst daily and I use this script to merge all the documents together. They are the same everytime. However it includes a series of blank rows because the vba I use to create them I believe causes this. Is there a command to remove the VBA when merging them together. Here is the selection copy piece.
HTML Code:
'Import a sheet from found files
Do While Len(fName) > 0
If fName ThisWorkbook.Name Then
[Code].....
View 3 Replies
View Related
Mar 19, 2009
I have a worksheet that currently has columns AB to XFB hidden. I want to unhide them, so I selected column AA and then drug the mouse over to the right (toward the empty space), intending to right-click and select "unhide". I do this all the time with no problems.
However, from the instant of my drag to the right, Excel has become totally unresponsive. I see it using 25% of my quad CPUs in task manager, but there is no active memory delta and it has been sitting for over 20 minutes now.
I'd rather not lose the work I just entered, but I can't seem to get Excel to come back to life by pressing escape or clicking in the worksheet. I've noticed slowdowns before when there is a lot of actual data in the cells selected, but these columns were empty to the best of my knowledge.
View 2 Replies
View Related
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
Jan 2, 2007
i have an old spreadsheet that i created about a year or so ago.
i managed to make some sheets "very hidden" as opposed to just "hidden" or "visible".
the problem is i can't find them ! i have tried "format, sheet, unhide" but that is greyed out.
i do have passwords for them, but i can't find them to put these in!
View 9 Replies
View Related
Oct 3, 2008
I have a 15 sheet spreadsheet that has many formula and results that are obtained from a "Master Sheet." I want to hide the "Master Sheet" from users view.
When I do so the formulae can't find the info they require. How do I hide a sheet so that users can't see it, but the formula from other sheets can still access it?
View 4 Replies
View Related
Dec 5, 2007
i've an excel file where a final presentable sheet is linked to some hidden sheets which i dont want the user to see. Thru VBA, i'm performing some format related stuff in those hidden sheets, using: Sheets("Historical1").Visible = True. later, VBA sets it back to FALSE. But, i want to get rid of this hiding/unhiding, if its possible. My code has the following structure:
Sub tame_blph1()
Sheets("Historical1").Visible = True
Sheets("Historical1").Activate
Dim coll As Integer
Dim startcoll As Integer
Dim endcoll As Integer
startcoll = 25
endcoll = 124
For coll = startcoll To endcoll Step 2
If Len(selection) > 0 Then Call Macro1
Next coll
Sheets("Historical1").Visible = False
Sheets("Table").Select
End Sub
In other words, can we perform the above without hiding and unhiding the Sheet called "Historical1"
View 3 Replies
View Related
Mar 14, 2014
I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
View 1 Replies
View Related
Jun 15, 2013
I have a worksheet with 28 sheets. I want the first sheet to be the only sheet displayed when opening the file and then want people to have the ability to click a hyperlink to unhide a particular sheet. Then when they go back to the main page (via another hyperlink), the sheet that was unhidden will be hidden again. I was able to put together some code from [URL]. When I click on a hyperlink on the main page, I get a "Subscript out of range" error and it highlights Sheets(strSheet).Visible=True from the Worksheet_FollowHyperlink code. I made a basic spreadsheet with just 3 sheets and plugged this code in and it worked fine so I'm not quite sure what the error is. I attached the spreadsheet I'm using.
View 3 Replies
View Related
Nov 18, 2009
I have been sent a workbook which contains a number of formulae that reference hidden worksheets (see attached JPEG).
I cannot work out how to display these sheets? The workbook does not seem to be protected, I can edit and save the visible sheet. Can anyone shed some light on how to view the referenced sheets?
View 4 Replies
View Related
Apr 5, 2013
I have a total of 15 sheets on my excel documents. I've hidden 10 (5 sheets will be shown) of those sheets so i can hyperlink to them from the sheet called "The plan". The name of the hidden sheets are called:
"Week 1"
"Week 2"
"Week 3"
"Week 4"
"Week 5"
"Week 6"
"Week 7"
"Week 8"
"Week 9"
"Week 10"
On "The plan" I have hyperlinked the words to the corresponding sheets then I hid the sheets, now the hyperlinks don't work. What should I do?
View 2 Replies
View Related
Aug 21, 2013
I just came across file that needs to be cleaned up. Problem is, the main summary references more than 50 hidden sheets for pay periods dating back to 2011.
I want to make everything values from previous pay periods then get rid of the hidden sheets.
View 4 Replies
View Related
Jul 31, 2007
ive got a macro which works with data on hidden sheets within the workbook - when i hide the sheets the macro gives errors and will not run. when i unhide the sheets everything runs fine!
View 9 Replies
View Related
Dec 4, 2006
how can i see hidden sheets in a workbook?
View 3 Replies
View Related
Oct 17, 2007
I have 5 sheets in a workbook. Three are hidden. My code allows user to make as many copies of the hidden sheets as needed and place them at the end by means of a macro button. This works fine, but after multiple copies (4 to 15) it crashes! Don't know what I did wrong.
Sub CopyWs()
Sheet8.Visible = True
Sheet8.Select
Sheet8.Copy After:=Sheets(Worksheets.Count)
Sheet8.Visible = False
Sheet9.Visible = True
Sheet9.Select
Sheet9.Copy After:=Sheets(Worksheets.Count)
Sheet9.Visible = False
Sheet10.Visible = True
Sheet10.Select
Sheet10.Copy After:=Sheets(Worksheets.Count)
Sheet10.Visible = False
End Sub
View 9 Replies
View Related
Nov 22, 2008
I have 5 sheets, 3 of them are hidden, how can i put a 3 different password to unhide the hidden sheets.
View 6 Replies
View Related
Feb 25, 2009
I tried to Google this question but cannot find the answer. What is the maximum number of worksheets that Excel can hide within a workbook? I have a macro which hides all worksheets with the exception of the active worksheet and it has never failed. But this morning I had started working on a workbook containing many sheets and when I ran my HideSheets macro a maximum of 14 sheets hides every time. Is it my code (see below)?
View 2 Replies
View Related
Jul 6, 2009
I want this macro to run when I select sheet1 so I have dropped the code into sheet1. The macro runs fine as a stand alone but I get a 1004 protection error when placed into the worksheet module. Sheet "data2" is unprotected. I have tried "calling" the macro from the sheet1 module but it loops. I know it's simple but it's kicking my ***. data2 is hidden and workbook is unprotected as of now.
View 3 Replies
View Related
Sep 22, 2009
I'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:
View 2 Replies
View Related
Jan 3, 2012
I have to review work papers with 50+ sheets. The users often hide the sheets they do not use. As part of my review I have to review all sheets, even the hidden ones.
What I would like to achieve:
To create a list of all hidden sheets with hyperlinks so I can go directly to the hidden sheet. This would be the same as a table of contents but for hidden sheets.
View 4 Replies
View Related
Feb 1, 2013
Is there a way to use a userform and hidden sheets at the same time?
I want to limit the access to the information through a userform but my userform requires me to unhide the userform to show. Is there a way to go around this? Because the information might be misused if the user can simply close the form and have access to the information in the sheet.
View 3 Replies
View Related
Mar 25, 2007
I downloaded a cool add-in from XL-Logic that lets you select sheets to print. It's great, except that I need to print very hidden sheets and it does not display very hidden sheets. Does anyone know how to modify this code so it will list all sheet including very hidden sheets? And is there a way to make the sheets appear in alphabetical order? I've tried to play with it, but I don't know enough about VB....
View 9 Replies
View Related
Jul 20, 2009
Regarding the age old problem of trying to ensure macros are enabled in a workbook, using the process of hiding all sheets bar one:
Automatically Close Workbook If Macros Are Disabled
Reafid inserts some excellent code so that worksheets are hidden BOTH before closing AND on a normal Save event (I have also attached Reafid's zip file with the workbook that includes his/her code).
Having done much searching it seems to be the best solution out there, however I can still break it fairly easily and I'm worndering if anybody can stop me doing the following to break it:
Open the file in its restricted state (i.e. only Warning tab shown, all other sheets very hidden) and NOT enabling macros on opening. I'm therefore at the position Reafid intended; macros are disabled so all sheets are very hidden. Then to break it, in my personal macro workbook I have a simple macro that unhides all worksheets in ThisWorkbook, I run that personal macro and everything in Reafid's file is unhidden.
Reafid's file therefore has been opened with macros DISABLED and now all worksheets are visible.
View 9 Replies
View Related
Aug 2, 2006
I have written some code to be executed in Sheet1 which copies formats from Sheet1 to Sheet2 and then values from Sheet 3
Sheets("Sheet1"). Range("E4:M9").Copy Sheets("Sheet2").Range ("A1").Offset(9 * i + 3, 1)
Sheets("Sheet3").Range("P90:W93").Copy _
Sheets("Sheet2").Range("A1").Offset(9 * i + 5, 2).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The code does exactly what I want it to do, but when Sheets 2 and 3 are hidden images of what is being pasted briefly flash up on the screen. Is there a way of avoiding this? The amount of data in the whole sheet is quite large (about 4MB), could it just be that the program is running too slowly?
View 3 Replies
View Related
Jul 25, 2007
I am using some VBA I found here to password protect a hidden sheet. It works perfectly for 1 sheet, but I am trying to hide 6 but am having problems. Under the HideSheets part I've modified it fine, by using
Sub HideSheets()
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
Worksheets("Confidential1").Visible = xlSheetVeryHidden
Worksheets("Confidential2").Visible = xlSheetVeryHidden
Worksheets("Confidential3").Visible = xlSheetVeryHidden
End Sub.........
View 9 Replies
View Related
