Selected Answer
Hello Amer omar and welcome to the forum,
In the attached file I have modified your file to include the following macro which I believe will do what you want. I added a worksheet called "Print Report" which is where your desired data is copied to for printing. On the "Print Report" sheet just click the button.
Sub PrintReport()
' macro written by WillieD24 for teachexcel. com
' 1 > macro will clear existing data from "Print Report" sheet
' 2 > macro copies col "A" & "B" and most recent date column to "Print Report" sheet
' 3 > remove rows from "Print Report" sheet where there is an empty cell in col "A"
' 4 > macro prints report to default printer
' 1 > clear existing data from "Print Report" sheet
Sheets("Print Report").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
' 2 > copy col "A" & "B" and most recent date column to "Print Report" sheet
Sheets("STOCK").Columns("A:B").Copy
Sheets("Print Report").Paste
Range("C1").Select
Application.CutCopyMode = False
' find last column with data on "STOCK" sheet
Sheets("STOCK").Select
Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Dim LRowLC As Long
Do Until LRowLC > 1
LRowLC = Cells(Rows.Count, LC).End(xlUp).Row
If LRowLC = 1 Then LC = LC - 1
Loop
' copy column to "Print Report" sheet - col "C"
Sheets("STOCK").Columns(LC).Copy
Sheets("Print Report").Paste
Range("A1").Select
Application.CutCopyMode = False
Range("A1").Select
' 3 > remove rows from "Print Report" sheet where there is an empty cell in col "A"
Sheets("Print Report").Select
Range("A1").Select
With Sheets("Print Report")
Sheets("Print Report").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
' 4 > print report to default printer
With Sheets("Print Report")
.Activate
.Range("A1").Select
.PrintOut Copies:=1
End With
End Sub
If this is what you were looking for please mark my answer as Selected.
Cheers :-)
