Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets
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
ADVERTISEMENT
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
May 10, 2008
I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0
Splash.Visible = True
View 4 Replies
View Related
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
View Related
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
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
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
Sep 21, 2013
How can I print 2 or more sheets on a workbook of, say, 10 sheets at once? Is there a way to do this?
View 2 Replies
View Related
Mar 11, 2014
I've noticed with a spreadsheet at work that if you print a sheet it also prints the other sheet.
The first sheet is the data and the second is a graph formed from this data.
Is there an options to set to do this or is because its a graph formed from the data sheet that it automatically prints?
View 3 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
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
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
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
Sep 2, 2009
I have the following code which works fine when all of the sheets are not hidden:
View 3 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
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
Mar 30, 2009
Is if possible to set up two different sheets in the same workbook to print as front and back pages on a duplex printer?
View 7 Replies
View Related
Mar 13, 2008
Print sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.
(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)
I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:
View 9 Replies
View Related
Jul 10, 2009
I am looking for a macro that will, upon command, print all sheets (20+) in the workbook with a value greater than $0.00 in cell I27. Some have a value there, others don't.
View 2 Replies
View Related
Jan 19, 2013
user form that has a list box that only picks up visual worksheet (not hidden ones) and then allows the user to select which ones to print via check boxes.
View 5 Replies
View Related
