My Excel workbook (a financial forecast) is composed of a dozen or so worksheets, each in its own tab.
I keep all the rows and columns in view while working (Outline level 3).
But I would like to collapse the view to Level 1 in most of the spreadsheets, just prior to printing. To save myself the manual work, I'm hoping to create a subroutine that automatically collapses those rows/columns as the first part of a printing macro. The challenge lies in collapsing the outline levels in multiple spreadsheets.
P.S. any recommendations on a good Excel VBA book?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Oh ok yes I do have that, sorry I didn't understand :oP
Try using this code (you will have to change it to suit what you are doing)
Sub PrintOut()
'collaps levels
ActiveSheet.Outline.ShowLe
ActiveSheet.Outline.ShowLe
'print sheet
ActiveWindow.SelectedSheet
'reset levels
ActiveSheet.Outline.ShowLe
ActiveSheet.Outline.ShowLe
'move to next sheet
ActiveSheet.Next.Select
'or select a sheet
'Sheets("SheetName").Selec
'etc...
End Sub
I don't know if there is a master command to do that (I doubt it)
what about a loop:
Sub PrintOut()
Dim NoOfSheets 'total number of sheets in workbook
NoOfSheets = 12
Sheets(1).Select
For I = 1 To NoOfSheets
ActiveSheet.Outline.ShowLe
ActiveSheet.Outline.ShowLe
'print sheet
ActiveWindow.SelectedSheet
'reset levels
ActiveSheet.Outline.ShowLe
ActiveSheet.Outline.ShowLe
'move to next sheet
ActiveSheet.Next.Select
Next I
End Sub
No worries.
Well I pretty much learnt using one book, but it's for excel '95
I found it really easy to read and it was my reference "Bible" for about two years.
It was published by "Microsoft Press"
and titled "Excel/Visual Basic Programmer's Guide"
I'm sure they will have an up-to-date edition released.
The best thing about it was that it didn't talk about anything except Excel and macros!
Regards,
m8rix
Business Accounts
Answer for Membership
by: m8rixPosted on 2003-04-01 at 16:24:55ID: 8249858
Ok first I am not sure what the Outline levels are. So I can't give you the code you want.
However do you know how to record a macro? (tools => macro => record new macro) manually make the changes and it will be recorded in VB. If you know a little VB you can then tweak it to your exact needs after it is recorded.
Also a hint for making changes to multiple worksheets is you can hold the shift key and select more than one sheet at a time. Then what ever changes you make whilst they are selected will reflect accross all of the selected sheets.