Results 1 to 2 of 2

Thread: Printing Sheets Based On Criteria VBA

  1. #1
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14

    Printing Sheets Based On Criteria VBA

    I have following codes which prints the selected sheet within range in the workbook. Now, I want to be able to without deleting or disturbing the range, select the sheets which i want to print by marking them "x" in the next column. And other thing which i want to do is to be able to print some sheets from other workbook (as the workbook should be opened and selected sheet printed) but in same directory, if i specify the print range in the existing range of sheets which are to be printed.

    Kindly advise.

    Code:
    Option Explicit
    Sub Print_Ranges()
        Dim strShtname As String, strRngName As String
        Dim i As Long
     
        With Worksheets("INDEX")
     
            'sort the named range list according to page number order
            .Range("A2").CurrentRegion.Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlYes, ordercustom:=1, Orientation:=xlTopToBottom
     
            'loop through the cells and determine parent of named range and specific range addresses
            For i = 3 To 34
                strRngName = .Cells(i, 2).Text
                strShtname = Range(strRngName).Parent.Name
     
                'clear any existing print areas and reset to named ranges areas
                With Worksheets(strShtname)
                    .PageSetup.PrintArea = ""
                    .PageSetup.PrintArea = Range(strRngName).Address
                    .PrintOut
                '  .PrintPreview
                End With
            Next i
        End With
     
    End Sub
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    HI excel_learner,

    Welcome to ExcelFox!!

    Try this. Untested.

    Code:
    Option Explicit
    Sub Print_Ranges()
        Dim strShtname As String, strRngName As String
        Dim i As Long
     
        With Worksheets("INDEX")
     
            'sort the named range list according to page number order
            .Range("A2").CurrentRegion.Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlYes, ordercustom:=1, Orientation:=xlTopToBottom
     
            'loop through the cells and determine parent of named range and specific range addresses
            For i = 3 To 34
                If LCase$(.Cells(i, "C").Text) = "x" Then 'I assume the 'X' is in Col C,if not, replace "C" with appropriate Col
                   strRngName = .Cells(i, 2).Text
                   strShtname = Range(strRngName).Parent.Name
        
                   'clear any existing print areas and reset to named ranges areas
                   With Worksheets(strShtname)
                       .PageSetup.PrintArea = ""
                       .PageSetup.PrintArea = Range(strRngName).Address
                       .PrintOut
                   '  .PrintPreview
                   End With
                End If
            Next i
        End With
     
    End Sub
    
    '//This routine is for print sheets from other workbook
    
    Sub PrintRangesFromOtherWorbook(ByVal WbkName As String, ParamArray Sheets2Print() As Variant)
        
        'workbook name should be with the extension
        
        Dim i           As Long
        Dim wbkOther    As Workbook
        
        With Application
            .ScreenUpdating = 0
            .EnableEvents = 0
            .DisplayAlerts = 0
        End With
        
        On Error Resume Next
        Set wbkOther = Workbooks(CStr(WbkName))
        If Err.Number <> 0 Then
            Err.Clear
            Set wbkOther = Workbooks.Open(ThisWorkbook.Path & "\" & WbkName, 0)
            If Err.Number <> 0 Then
                MsgBox "Workbook '" & WbkName & "' not found in" & vbLf & ThisWorkbook.Path, vbInformation
                Err.Clear
                GoTo QuickExit
            End If
        End If
        
        For i = LBound(Sheets2Print) To UBound(Sheets2Print)
            'I hope print area is there in every sheet
            With wbkOther.Worksheets(Sheets2Print(i))
                .PrintOut
            '  .PrintPreview
            End With
            MsgBox Sheets2Print(i)
        Next
    QuickExit:
        With Application
            .ScreenUpdating = 1
            .EnableEvents = 1
            .DisplayAlerts = 1
        End With
    End Sub
    
    '// Call the macro like..
    
    Sub kTest()
        PrintRangesFromOtherWorbook "OtherWorkbookName.xls", "Sheet1", "Sheet2", "Sheet3"
    End Sub
    HTH

Similar Threads

  1. Printing range of sheets in excel or in PDF
    By excel_learner in forum Excel Help
    Replies: 18
    Last Post: 10-11-2015, 01:36 PM
  2. Split data based on criteria
    By Mahesh.sreekakulam in forum Excel Help
    Replies: 3
    Last Post: 06-08-2012, 09:30 PM
  3. Replies: 7
    Last Post: 03-06-2012, 07:49 AM
  4. Lookup Based on Criteria
    By maruthi in forum Excel Help
    Replies: 2
    Last Post: 02-15-2012, 08:49 AM
  5. Find Value Based On Criteria
    By excel_learner in forum Excel Help
    Replies: 10
    Last Post: 07-30-2011, 10:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •