MartMartnz
New member
- Joined
- Nov 27, 2018
- Messages
- 3
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2016
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 ��
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
Attachments
Last edited by a moderator: