Macro to change view of sheets

I have a workbook with about 85 sheets happen that we have to change the
view from Normal to Page break preview wvery month, I could record a macro
(with the recorder), page by page and it works, but could somebody help me
to get a condensed macro, because I have to set another macro to changed
them back to Normal.
Thanks for your help.

Fernando



0
fgomez1 (14)
12/9/2003 2:58:25 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
510 Views

Similar Articles

[PageSpeed] 2

One way:

    Public Sub ToggleViews()
        Dim wkSht As Worksheet
        Dim nView As Long
        Dim sOldActive As String
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        With ActiveWindow
            If .View = xlPageBreakPreview Then
                nView = xlNormalView
            Else
                nView = xlPageBreakPreview
            End If
        End With
            sOldActive = ActiveSheet.Name
        For Each wkSht In Worksheets
            With wkSht
                .Activate
                ActiveWindow.View = nView
            End With
        Next wkSht
        Worksheets(sOldActive).Select
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub

This will toggle all the sheets to page break preview if the 
activewindow's view is normal, and vice versa.


In article <eUmSGTmvDHA.2308@TK2MSFTNGP11.phx.gbl>,
 "Fernando Gomez" <fgomez@golden.net> wrote:

> I have a workbook with about 85 sheets happen that we have to change the
> view from Normal to Page break preview wvery month, I could record a macro
> (with the recorder), page by page and it works, but could somebody help me
> to get a condensed macro, because I have to set another macro to changed
> them back to Normal.
> Thanks for your help.
> 
> Fernando
> 
> 
>
0
jemcgimpsey (6723)
12/9/2003 4:23:31 PM
Thanks for this macro:
I have a problem runing it it stops here:
ActiveWindow.View = nView

with this error
Runtime error 1004
application-defined or Object-defined error
I do not know how to fix it or an idea why is happening

Thanks for your help



"J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-B5FD5A.09233109122003@msnews.microsoft.com...
> One way:
>
>     Public Sub ToggleViews()
>         Dim wkSht As Worksheet
>         Dim nView As Long
>         Dim sOldActive As String
>
>         With Application
>             .ScreenUpdating = False
>             .EnableEvents = False
>         End With
>         With ActiveWindow
>             If .View = xlPageBreakPreview Then
>                 nView = xlNormalView
>             Else
>                 nView = xlPageBreakPreview
>             End If
>         End With
>             sOldActive = ActiveSheet.Name
>         For Each wkSht In Worksheets
>             With wkSht
>                 .Activate
>                 ActiveWindow.View = nView
>             End With
>         Next wkSht
>         Worksheets(sOldActive).Select
>         With Application
>             .ScreenUpdating = True
>             .EnableEvents = True
>         End With
>     End Sub
>
> This will toggle all the sheets to page break preview if the
> activewindow's view is normal, and vice versa.
>
>
> In article <eUmSGTmvDHA.2308@TK2MSFTNGP11.phx.gbl>,
>  "Fernando Gomez" <fgomez@golden.net> wrote:
>
> > I have a workbook with about 85 sheets happen that we have to change the
> > view from Normal to Page break preview wvery month, I could record a
macro
> > (with the recorder), page by page and it works, but could somebody help
me
> > to get a condensed macro, because I have to set another macro to changed
> > them back to Normal.
> > Thanks for your help.
> >
> > Fernando
> >
> >
> >


0
fgomez1 (14)
12/9/2003 4:42:36 PM
When it stops, what is the value of nView?

For my version at least, it should be either 1 (xlNormalView) or 2 
(xlPageBreakPreview).

Is the sheet it stops on the first sheet? a hidden sheet? 


In article <eOIjUNnvDHA.540@tk2msftngp13.phx.gbl>,
 "Fernando Gomez" <fgomez@golden.net> wrote:

> Thanks for this macro:
> I have a problem runing it it stops here:
> ActiveWindow.View = nView
> 
> with this error
> Runtime error 1004
> application-defined or Object-defined error
> I do not know how to fix it or an idea why is happening
0
jemcgimpsey (6723)
12/9/2003 8:53:05 PM
Reply:

Similar Artilces:

R squared not the same in graph and spread sheet.
I have an X-Y plot displayed with a trend line equation and R squared. I also calculate R squared (RSQ) and the numbers are not the same; 0.8918 and 0.928. Can someone explain this (Excel 2010 and Windows 7)? -- Jim Silverton Extraneous "not" in Reply To. "James Silverton" <jim.silverton@verizon.net> wrote: > I have an X-Y plot displayed with a trend line equation > and R squared. I also calculate R squared (RSQ) and the > numbers are not the same; 0.8918 and 0.928. Can someone > explain this (Excel 2010 and Windows 7)? You would need to post [1] m...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

Tr again: Modifying Calendar's "reading" view in 2003
In Outlook 2003, Calendar allows me to show the event's content in a "Reading" pane at the right or bottom of the calendar. But the layout of the Reading pane wastes a lot of space. Can its layout or content be modified? No, the content can't be modified. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com...

Changing Interface OWA (exchange 2003)
Not sure if you all remember but back in the Exchange 5.5 days we use to be able to go onto a site and put in the username of the person mailbox you want to access, then a windows pop up would come up asking you for your credentials. I was wondering if that is still possible to do that in Exchange 2003? If so how can that be done? Configure Forms-Based Authentication (FBA). -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "John" <nospam@comcast.net> wrote in message news:evIllMndHHA.596@TK2MSFTNGP06.phx.gbl... > Not sure...

Change License / Company name
Hi, we tried CRM 1.2 with the MSDN license key. Now my company bought a 25 user Proffesional Suite license and I want to change the key on the production server. Is there an easy way to do this?? The only solution I see, is via the Data Migration Framework. Erik. How about the re-deploy tool? "Erik" <none> wrote in message news:u0SXdbXlEHA.3452@TK2MSFTNGP15.phx.gbl... > Hi, > we tried CRM 1.2 with the MSDN license key. Now my company bought a 25 > user > Proffesional Suite license and I want to change the key on the production > server. Is there an easy wa...

gp10 change clients pointing to different server
In version GP 10 I have clients pointed to a test server. When production server is ready what do i do to point the clients to the production server e.g. GPServer. Would I change the odbc connection? Or Rerun the client installs? Any help is appriciated. Thank YOu As long as your production server has THE SAME level service pack as your clients and THE SAME third party products all you need to do is repoint your ODBC to the production server. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "2020" wro...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Excel 2000 macro security Level
Hi! I am running an ASP.NET application which ideally opens an Exce spreadsheet with a macro included in it. When I try opening spreadsheet without a macro, everything works fine, but with macros, i hangs on the opening command. I tried to go to Excel 2000 and go t Tools->Macros->Security Level->Low but it still hangs when I try t open the macro spreadsheet. All works fine with the spreadsheet no containing macros, so it really is a question of macro security. But put it to low, so I don't understand how it is still not opening!! An help would be greatly appreciated! Thanks and...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

Running an Excel Macro from an Access Command Button
Hi , Can anyone help me with this, I am trying to run an excel macro from an access command button. I want the macro to Bold the Headings on a excel spreadsheet. The code runs without any errors but the macro doesnt seem to work, I think I have got the line ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong The code: Private Sub cmdRunMac_Click() Dim ExcelApp As Excel.Application Dim ExcelBook As Excel.Workbook Set ExcelApp = Excel.Application ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and Settings\...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

web preview not same as publisher view
Reading past postings it appears others too are having serious problems with Web preview in both 2002 and 2003. I think the problem is that text handling within text boxes in normal Publisher view simply does not reflect that as seen in Web Preview (currently Explorer 6) On a totally blank (wide setting) webpage, using standard Times New Roman 12pont (no italic underline or bold) I typed six lines of Mary had a little lamb into a 3.85x3.029cm text box with all margins set to 00. I outlined the box with a 1/4pt line Whereas the text just fitted into the box in Publisher view, in Web ...

How to view the same email message at the different PC users
My husband & I use the same PC(Windows XP) but have an individual user accounts. We have several email addresses for individual usages and shared usages and all enable to receive and view by Outlook 2000 inbox. We have noticed that the emails came in while I'm on Outlook (or minimized at the bottom) can not view when my husband goes to his Window user and open the Outlook. And vise versa. We want to be able to look at all emails that came into the Outlook inbox at both Window user's Outlook inboxes. How can we do that? ...

stop automatically changing formula!
i have a countif function COUNTIF(Locking!I16:I40,"f") when i copy this and paste it to the next cell, the formula automatically change to COUNTIF(Locking!J16:J40,"f") How do I stop it from changing column I to J?!?!?! thanks. Caryn, =COUNTIF(Locking!$I$16:$I$40,"f") or =COUNTIF(Locking!$I16:$I40,"f") HTH, Bernie MS Excel MVP "caryn" <caryn.tan@gmail.com> wrote in message news:d7n4u3$hgi$1@avnika.corp.mot.com... > i have a countif function > COUNTIF(Locking!I16:I40,"f") > when i copy this and paste it to the nex...

unable to change password via OWA
Hi, I am having a problem with users changing passwords via OWA. When they click on the change password button, it comes up with change password screen and asked for domain name, user name , old password ,etc. The problem that I have is that when I submit it, it comes up with message saying "Either the password is too short or password uniqueness restrictions have not been met." But I didn't define any password policy on DC. What can I do ? Kindly please give me some idea. Thanks Losa If you are running windows and exchange 2003 then there are password requirements that...

vba code to enable macros
Hi, Can we write a vba code to enable macros. In most of the sites it says we cant do that . The only thing we can do is change the macro setting or work around is hide the sheet which has macros. But none of them will work in my case. Can we change an excel security setting to from macro on open so that macros are enabled. No. If we can do that with code, then so could a hacker with bad intentions. Mike F "varsha12" <varsha12@discussions.microsoft.com> wrote in message news:DBEB1614-C956-491B-B351-02DD9F711891@microsoft.com... > Hi, > Can we w...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

linking sheets
Hi All, How do i link my drop down list in a13 sheet 1 to column f in sheet 2. Hi Jinx if using Data Validation to create your drop down list the easiest way is to range name column F in sheet 2 (go to sheet 2, click on F, click in name box (left hand side of formula bar) type name (no spaces), press enter) then click in Sheet1!A13, choose Data / Validation, choose List - click IN the big white box, press you F3 key to bring up list of names in workbook, choose the name, click OK, click OK again and it should work fine. Hope this helps Cheers JulieD "Jinx" <anonymous@discus...

Macro on a protected worksheet in a shared workbook.
Hi, I have a macro in protected worksheets that can't run once the Workbook is Shared. I have wrapped the Macro code so as the worksheet is unprotected for the time the Macro runs, and unable the Autofilter. The code is as follow at the moment: Sub Newaction() Sheets("Critical Path").Unprotect ("") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheets("Critical Path").EnableAutoFilter = True Sheets("Critical Path").Protect contents:=True, userInter...

OWA - change password
Getting the "Object Required" error, when somebody is trying to change their password through OWA. (Win2003/Ex FE 2003) Any ideas what could be causing this? TIA! / Per Did you follow the steps in: http://support.microsoft.com/kb/297121 ? -- Pablo D. Vernocchi Microsoft Exchange MVP MCSA + M / MCSE + Sec https://mvp.support.microsoft.com/profile=8E94A6C0-F6D9-4686-96D5-04230CC0141F http://www.itpros.com.ar http://www.msglue.org http://msmvps.com/exchangebloglatino "Per Hagstrom" <per.hagstrom@swic.eduNOSPAM> wrote in message news:Ojo%23EozNHHA.1240@TK2...

iisadmpwd change pw gives error
Just cannot get password change to work on Exchange2003 (front-end server) & IIS 6.0 Is this a known issue Any help would be greatly appreciated. It could be that you are not (yet) allow to change your password either because password change is prohibited or because user has to wait for x days before he is allowed to change his password (domain policies). Whenever you reset a password for a user and the 'Cannot change pass before X days' is enforced, the user is not allowed to change it. David. "ss" <anonymous@discussions.microsoft.com> wrote in message news...

copying rows from next sheet over
I'm trying to copy a range of rows from one sheet over from the activ sheet, and paste them onto another sheet. This is what I tried: ActiveSheet.Next.Range("a2:b100").select selection.copy However, I'm getting the error "selection method of the range clas failed" What am I doing wrong, and is there an alternative way to do this? Any help would be appreciated -- ayl32 ----------------------------------------------------------------------- ayl322's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984 View this thread: http://www.excel...

Need to have more Columns available in advanced view
I know how to add columns in advanced view but i can't add all the columns I'd like to add. I can choose more fields (attributs) as search attributes than as result columns. Is there somewhere a switch to turn a field (attribute) into not only beeing searchable but selectable as a column in advanced search? Example: "Invoice Product": Is there a way to make an advanced search or view which delivers field (attributes) of "Invoice Product" as a result? Marko ...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...