Print Same Range on Multiple Sheets?
I was wondering if its possible to print the same range on multiple sheets in
one command. (Rather than doing it in two parts by setting the print area first and then printing the sheets) for instance, i'd like to print the area named by the range name "March" on Sheet1 and Sheet2. Is there a way to do this? Thanks in advance. |
Print Same Range on Multiple Sheets?
Al,
This is not tested but should head you in the right direction. I have done it as two subs so it is reusable. You will need to add error traps if there is a possibility that the sheet is not open, doesn't have the right sheets, a chart is selected in a sheet you want to print, etc. Sub Printout() Dim vSheetList As Variant Dim strRangeName As String vSheetList = Array("Sheet1", "Sheet2") strRangeName = "March" PrintNamedRangeInSheets vSheetList, strRangeName End Sub Sub PrintNamedRangeInSheets(vSheetList As Variant, strRangeName As String) Dim vSheet As Variant Dim shOrigin As Worksheet Dim rngInSheet As Range Set shOrigin = ActiveSheet For Each vSheet In vSheetList Sheets(vSheet).Select Set rngInSheet = Selection Range(strRangeName).Select Selection.Printout Copies:=1, Collate:=True rngInSheet.Select Next vSheet shOrigin.Select End Sub Robin Hammond www.enhanceddatasystems.com "Al" wrote in message ... I was wondering if its possible to print the same range on multiple sheets in one command. (Rather than doing it in two parts by setting the print area first and then printing the sheets) for instance, i'd like to print the area named by the range name "March" on Sheet1 and Sheet2. Is there a way to do this? Thanks in advance. |
Print Same Range on Multiple Sheets?
Thanks Robin! That did the trick!
"Robin Hammond" wrote: Al, This is not tested but should head you in the right direction. I have done it as two subs so it is reusable. You will need to add error traps if there is a possibility that the sheet is not open, doesn't have the right sheets, a chart is selected in a sheet you want to print, etc. Sub Printout() Dim vSheetList As Variant Dim strRangeName As String vSheetList = Array("Sheet1", "Sheet2") strRangeName = "March" PrintNamedRangeInSheets vSheetList, strRangeName End Sub Sub PrintNamedRangeInSheets(vSheetList As Variant, strRangeName As String) Dim vSheet As Variant Dim shOrigin As Worksheet Dim rngInSheet As Range Set shOrigin = ActiveSheet For Each vSheet In vSheetList Sheets(vSheet).Select Set rngInSheet = Selection Range(strRangeName).Select Selection.Printout Copies:=1, Collate:=True rngInSheet.Select Next vSheet shOrigin.Select End Sub Robin Hammond www.enhanceddatasystems.com "Al" wrote in message ... I was wondering if its possible to print the same range on multiple sheets in one command. (Rather than doing it in two parts by setting the print area first and then printing the sheets) for instance, i'd like to print the area named by the range name "March" on Sheet1 and Sheet2. Is there a way to do this? Thanks in advance. |
All times are GMT +1. The time now is 11:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com