Results 1 to 2 of 2

Thread: VBA: Automatically filter and print

  1. #1
    Neophyte MartMartnz's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    2016

    VBA: Automatically filter and print



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am new to VBA and would appreciate some help with a problem I am trying to solve.

    I have a table where I have information of various products. I would like to automatically filter each product name (column A), one by one, and perform a print action. In the table I can have up to 80 different products, so I need to find a solution to print everything automatically just by pressing a button but also to have a option to have a print preview.

    I adapted a code from the net, but the result is not the ideal, the headers of my table begin in A7:C7, and I can have values (formulas) that can be empty.

    I need that the macro filters only the no blank cells (A8:C??)and print one by one the table by product.

    Is that possible?Any suggestions would be greatly appreciated ��

    Code:
    Sub PrintALL()
    Dim TempWks As Worksheet
    Dim wks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    Set wks = Worksheets("ProductTable")
    Set TempWks = Worksheets.Add
    wks.AutoFilterMode = False
    wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=TempWks.Range("A6"), Unique:=True
    With TempWks
      Set myRng = .Range("a7", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    With wks
      For Each myCell In myRng.Cells
        .UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
        .PrintOut Preview:=True
      Next myCell
    End With
    wks.AutoFilterMode = False
    Application.DisplayAlerts = False
    TempWks.Delete
    Application.DisplayAlerts = True
    End Sub
    Attached Files Attached Files
    Last edited by p45cal; 2019-11-13 at 11:02 PM. Reason: split the lines of code and enclosed in code tags

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,620
    Articles
    0
    Excel Version
    365
    If you acknowledged the help that people have tried to give you in the past, they'd be more willing to help you again.

Posting Permissions

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