PDA

View Full Version : [SOLVED] Macro to print multiple non-adjacent area in a worksheet.



kane
07-11-2005, 01:30 PM
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,
:beerchug:

avkb03
07-11-2005, 01:52 PM
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
07-11-2005, 02:59 PM
Yes I wanted them on their own page.

Thx a bunch,

Justinlabenne
07-11-2005, 06:39 PM
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
07-11-2005, 10:39 PM
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
07-12-2005, 12:30 AM
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