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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Mohammad Basem

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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]
 

Jonas1

New Member
Joined
Jun 22, 2012
Messages
15
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.
 

Mohammad Basem

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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]
 

Jonas1

New Member
Joined
Jun 22, 2012
Messages
15

ADVERTISEMENT

Unfortunatly I get a 'Subscript out of range' error at the [Sheets(rCell.Value).Select False] line, any idears whats causing this?
 

Mohammad Basem

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
May be the sheet does not exist. Try to check the value of rCell when the error occur with existing sheets.
 

Jonas1

New Member
Joined
Jun 22, 2012
Messages
15
Many thanks had a missing sheet name, doh, now I have added that its working.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,024
Members
414,037
Latest member
Roamingsmile

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
Top