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.

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.
>

'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.
>

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.
>