Stop my macro going to Print Preview PLEASE!

oliviar

Board Regular
Joined
Sep 12, 2010
Messages
184
Hi Guys,
My macro, it works SO WELL. Except that I cannibalised parts from all over, and I can't stop it going to Print Preview. I just want it to remain on the view it was on. Because right now, its running sheet 1, going to print preview, I have to close that, then sheet 2, print preview, close that, sheet 3... etc etc.

Please help:

Code:
Private Sub Workbook_Open()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Range("e3").Value = Sheets(1).Range("e3").Value + 1
ActiveWorkbook.Save
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("e3")
    With ActiveSheet.PageSetup
        .LeftHeader = r
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    Next wsheet
    
End Sub
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
Just remove the PrintPreview line from within the loop.

If you do actually want to preview you could add it, or similar after the loop.
Code:
ThisWorkbook.Worksheets(Array("Sheet2", "Sheet3")).PrintPreview
 

oliviar

Board Regular
Joined
Sep 12, 2010
Messages
184
No that doesn't work. It gives me an error saying 'next without for' or something like that:

This is what I end up with after I delete that line:
Code:
Private Sub Workbook_Open()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Range("e3").Value = Sheets(1).Range("e3").Value + 1
ActiveWorkbook.Save
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("e3")
    With ActiveSheet.PageSetup
        .LeftHeader = r
    Next wsheet
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,774
Messages
5,470,707
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top