Cutting down code

[Aidan]

I have recorded a macro to print the same range on a series of sheets and
then return to first worksheet. Is there a way of cutting down on repetition
in code. Please see example of what I have...

Range("B328:L347").Select
ActiveSheet.PageSetup.PrintArea = "$B$328:$L$347"
Selection.PrintOut Copies:=1
ActiveSheet.Next.Select
ActiveSheet.PageSetup.PrintArea = "$B$328:$L$347"
Selection.PrintOut Copies:=1
ActiveSheet.Next.Select
Range("B328:L347").Select
ActiveSheet.PageSetup.PrintArea = "$B$328:$L$347"

Thanks in advance, Aidan.

 

[Simon Lloyd]

Not tested but should do the job!

Code:
--------------------

Sub Print_Out()
Dim Rng as Range, Sh As Worksheet
Set Rng=Range("B328:L347")
For Each Sh in Sheets
Sh..PageSetup.PrintArea = Rng
Sh.Printout
Next Sh
End Sub

--------------------

I have recorded a macro to print the same range on a series of sheets
and
then return to first worksheet. Is there a way of cutting down on
repetition
in code. Please see example of what I have...

Range("B328:L347").Select
ActiveSheet.PageSetup.PrintArea = "$B$328:$L$347"
Selection.PrintOut Copies:=1
ActiveSheet.Next.Select
ActiveSheet.PageSetup.PrintArea = "$B$328:$L$347"
Selection.PrintOut Copies:=1
ActiveSheet.Next.Select
Range("B328:L347").Select
ActiveSheet.PageSetup.PrintArea = "$B$328:$L$347"

Thanks in advance, Aidan.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Mike H]

Hi,

Not tested but looks OK. Change the line
S = "Sheet1,Sheet2,Sheet3"
to the sheet names that you want this to work on


Sub Sonic()
Dim R As Range
Dim V As Variant
Dim S As String
Dim sh As Worksheet
S = "Sheet1,Sheet2,Sheet3"
V = Split(S, ",")
For Each sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(CStr(sh.Name), V, 0)) Then
sh.PageSetup.PrintArea = "$B$328:$L$347"
sh.PrintOut Copies:=1
End If
Next sh

End Sub

Mike

 

[Jacob Skaria]

'for specified sheets
Sub Macro1()
Dim strSheets As String, arrSheets As Variant, intCount As Integer
strSheets = "Sheet2,Sheet3"
arrSheets = Split(strSheets, ",")
For intCount = 0 To UBound(arrSheets)
Sheets(arrSheets(intCount)).Range("$B$328:$L$347").PrintOut Copies:=1
Next
End Sub


'if for all sheets
Sub Macro2()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("$B$328:$L$347").PrintOut Copies:=1
Next
End Sub


If this post helps click Yes

 

[Aidan]

Thanks Mike, I'll give it a go.