Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Thread Tools Search this Thread
Old 08-04-2009
Join Date: Jan 2009
Posts: 74
How to print multiple selections on one sheet using VBA

Hello friends,

I am facing an issue in my Excel Sheet.I want to have a printout of multiple selections on one sheet using VBA.I have heard from my friends that it is possible but don't know the code for it.I have also try to find it over Internet but did not got any results.Hope my query gets solved over here.
Reply With Quote
Old 08-04-2009
Join Date: Feb 2008
Posts: 1,848
Re: How to print multiple selections on one sheet using VBA

Sure i will help you out with this try to follow the below code:-

Sub PrintSelectedCells()
' prints selected cells, use from a toolbar button or a menu
Dim aCount As Integer, cCount As Integer, rCount As Integer
Dim i As Integer, j As Long, aRange As String
Dim rHeight() As Single, cWidth() As Single
Dim AWB As Workbook, NWB As Workbook
    If UCase(TypeName(ActiveSheet)) <> "WORKSHEET" Then Exit Sub 
    ' useful only in worksheets
    aCount = Selection.Areas.Count
    If aCount = 0 Then Exit Sub ' no cells selected
    cCount = Selection.Areas(1).Cells.Count
    If aCount > 1 Then ' multiple areas selected
        Application.ScreenUpdating = False
        Application.StatusBar = "Printing " & aCount & " selected areas..."
        Set AWB = ActiveWorkbook
        rCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
        cCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        ReDim rHeight(rCount)
        ReDim cWidth(cCount)
        For i = 1 To rCount 
            ' find the row height of every row in the selection
            rHeight(i) = Rows(i).RowHeight
        Next i
        For i = 1 To cCount 
            ' find the column width of every column in the selection
            cWidth(i) = Columns(i).ColumnWidth
        Next i
        Set NWB = Workbooks.Add ' create a new workbook
        For i = 1 To rCount ' set row heights
            Rows(i).RowHeight = rHeight(i)
        Next i
        For i = 1 To cCount ' set column widths
            Columns(i).ColumnWidth = cWidth(i)
        Next i
        For i = 1 To aCount
            aRange = Selection.Areas(i).Address 
            ' the range address
            Range(aRange).Copy ' copying the range
            With Range(aRange) ' pastes values and formats
                .PasteSpecial Paste:=xlValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                .PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            End With
            Application.CutCopyMode = False
        Next i
        NWB.Close False ' close the temporary workbook without saving
        Application.StatusBar = False
        Set AWB = Nothing
        Set NWB = Nothing
        If cCount < 10 Then ' less than 10 cells selected
            If MsgBox("Are you sure you want to print " & _
                cCount & " selected cells ?", _
                vbQuestion + vbYesNo, "Print celected cells") = vbNo Then Exit Sub
        End If
    End If
End Sub
Reply With Quote

  TechArena Community > Software > Software Development
Tags: , , ,

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads for: "How to print multiple selections on one sheet using VBA"
Thread Thread Starter Forum Replies Last Post
How to link multiple spreadsheet inside a single sheet via VBA cupid! MS Office Support 6 24-02-2012 01:50 PM
Is there any way to print 3 4x6 photos on single sheet using the print photo wizard mich43 Operating Systems 4 01-01-2011 10:05 PM
Volume and Songs Selections issue in my 160GB iPod imrios Portable Devices 1 17-11-2010 01:45 PM
Not able to print multiple pages in Silverlight 4 Feng Windows Software 6 05-08-2010 11:55 PM
how to print 2 different pictures on one sheet of paper prakashseth MS Office Support 3 20-12-2008 12:21 AM

All times are GMT +5.5. The time now is 08:41 PM.