Cutting down code
 

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.

Cutting down code
 

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

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


Aidan;527310 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144876

Cutting down code
 

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

"Aidan" wrote:

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.

Cutting down code
 

'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
---------------
Jacob Skaria


"Aidan" wrote:

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.

Cutting down code
 

Thanks Mike, I'll give it a go.

"Aidan" wrote:

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.