Creating a macro to print sheets from a list

Jonas1

New Member
Joined
Jun 22, 2012
Messages
15
Hi
I am having a lot of difficulty in creating a macro designed to print preview a set of sheets from a workbook, currently i have a macro that summarises a set of sheets and then filters them by a specified criteria i then want a macro that enables the selected sheets to be printed (print preview at the moment to check output).

The sheet names are in a list in column S.

Here is what i have so far.

Code:
Sub PrintSheets()
    Application.ScreenUpdating = False
     
    Sheets(Range("S1:S166")).PrintPreview , , 1
    Sheet1.PrintPreview , , 1
    Application.ScreenUpdating = True
End Sub

However the range gives a type mismatch error.

any help would be most appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi Jonas1 and welcome to the forum!

You may try something like,
Code:
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim rCell As Range
For Each rCell In Sheets(1).Range("S1:S166")
    Sheets(rCell.Value).PrintPreview
Next[/COLOR][/SIZE][/FONT]
 
Upvote 0
Hi Mohammad Basem

Many Thanks,

Just tryed the code and it works :),

Only query is is there a way to make it put them in one big print preview so i can just click the print button once, just being lazy, if its alot of extra code then dont worry about it.
 
Upvote 0
Yes, you can. Try this,
Code:
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub PreviewSheetList()

    Dim rCell As Range
    Dim bl1stSheet As Boolean

    bl1stSheet = True
    For Each rCell In Sheets(1).Range("S1:S166")
        If bl1stSheet Then
            Sheets(rCell.Value).Select
            bl1stSheet = False
        Else
            Sheets(rCell.Value).Select False
        End If
    Next
    ActiveWindow.SelectedSheets.PrintPreview

End Sub[/COLOR][/SIZE][/FONT]
 
Upvote 0
Unfortunatly I get a 'Subscript out of range' error at the [Sheets(rCell.Value).Select False] line, any idears whats causing this?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top