Macro to print multiple non-adjacent area in a worksheet.

[kane]

How can I print multiple non-adjacent areas in a single worksheet.

For example I want to run a macro that will print:

area 1 a1:b5
area 2 c7:e8
area 3 a30:g56

Thx,

[avkb03]

Well, Not sure if you can get them on the same page...

But this will print them each on their own page:

Range("a1:b5,c7:e8,a30:g56").PrintOut

[kane]

Yes I wanted them on their own page.

Thx a bunch,

[Justinlabenne]

I am not sure what kind of layout your looking for with this, but give this a try, it hides some rows, the nsend it to print-preview, change "PrintPreview" to "PrintOut" if it looks the way you want it, kind of a have a feeling it doesn't though.

Option Explicit

Sub NonConPrint()
Rows("6:6").EntireRow.Hidden = True
Rows("9:29").EntireRow.Hidden = True
With ActiveSheet
    .PageSetup.PrintArea = "$A$1:$G$56"
    .PrintPreview
End With
Range("A1:G56").EntireRow.Hidden = False
With ActiveSheet
    .PageSetup.PrintArea = Empty
End With
End Sub

[Justinlabenne]

The more I am looking at this, The code above may not suit. This one creates a temp sheet that get's the ranges you specified into a new sheet, prints it, the removes it, may need some fine tuning but it's a start:

Option Explicit

Sub NonCon2()
    Const szTempSheet$ = "temp" 
Sheets.Add
    On Error GoTo ErrHandle
    ActiveSheet.Name = szTempSheet
With Sheet1
        .Range("A1:B5").Copy Sheets("temp").Range("A1")
        .Range("C7:E8").Copy Sheets("temp").Range("D1")
        .Range("A30:G56").Copy Sheets("temp").Range("A7")
    End With
With Sheets(szTempSheet)
        .PageSetup.PrintArea = "$A$1:$G$33"
        .PrintOut
    End With
Application.DisplayAlerts = False
    Sheets(szTempSheet).Delete
    Exit Sub
ErrHandle:
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    MsgBox Err.Description
End Sub

[Dave]

This seems convaluted but it might help. Dave

Sub PrintRanges()
Dim Astring As String, Bstring As String, Bigstring As String
Dim MyData As DataObject, Mydata2 As DataObject, Wdapp1 As Object
'prints seperate ranges in sequence on same document
'A1:B3 & C1:E3 in this example
Worksheets("sheet1").Range("a1:b3").Copy
Set MyData = New DataObject
MyData.GetFromClipboard
Astring = MyData.GetText(1)
Worksheets("sheet1").Range("c1:e3").Copy
Set Mydata2 = New DataObject
Mydata2.GetFromClipboard
Bstring = Mydata2.GetText(1)
'etc
Application.CutCopyMode = False
Bigstring = Astring + Bstring '& etc.
On Error GoTo Below
Set Wdapp1 = CreateObject("Word.Application")
Wdapp1.ChangeFileOpenDirectory "c:\"
Wdapp1.documents.Add
With Wdapp1.activedocument
.content.insertafter Bigstring
.PrintOut
.SaveAs Filename:="temp.doc"
.Close savechanges:=True
End With
Application.Wait (Now + TimeValue("0:00:3"))
Wdapp1.Quit
Kill "c:\temp.doc"
Exit Sub
Below:
Wdapp1.Quit
Set Wdapp1 = Nothing
End Sub