VBA - Setting a Custom Print Range

[DKSmash2014]

Hello,

I am trying to come up with a way so that a manager can print a report showing which employees fall under which manager and their salaries.

So in the file I have Managers 1,2,3, & 4 and various employees. All the managers are in column A, employees Column B. So I guess this is similar to a print Group By

I have the basic print VBA set up, I am just not sure how to approach the select statement. Any help is much appreciated.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub PrintArea()

Range("A:A").Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Employees.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

With ActiveSheet.PageSetup
.Zoom = False
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End Sub</code>

 

[Tyler_Analyst]

Hello,

I am trying to come up with a way so that a manager can print a report showing which employees fall under which manager and their salaries.

So in the file I have Managers 1,2,3, & 4 and various employees. All the managers are in column A, employees Column B. So I guess this is similar to a print Group By

I have the basic print VBA set up, I am just not sure how to approach the select statement. Any help is much appreciated.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub PrintArea()

Range("A:A").Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Employees.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

With ActiveSheet.PageSetup
.Zoom = False
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End Sub</code>

Are you literally just trying to select range A1:B4?

ActiveSheet.Range("A1:B4").Select

 

[DKSmash2014]

Are you literally just trying to select range A1:B4?

ActiveSheet.Range("A1:B4").Select

I'm trying to get the macro to print different PDFs based on ranges of names.

So 1 PDF will print for all the employees under Manager 1, all employees under Manager 2, etc...

 

[Tyler_Analyst]

You thought about creating another tab where you put in the format you want, then looping through the rows and doing an if statement that copies and paste the row to the tab based on which manager is selected? I'll post an example in a second if you don't know what I am talking about.

 

[Tyler_Analyst]

Sub Employees()

Dim Y As String
Dim X As Integer
Dim Z As String
Dim R As String

X = 1
Y = "B1"
i = 2

Do Until Range(Y).Value = ""

Y = "B" & X
Z = "A" & X

If Range(Z) = Range("H1") Then
R = "B" & i

ActiveWorkbook.Worksheets("PrintSheet").Range(R).Value = Range(Y).Value


i = i + 1
End If

X = X + 1


Loop

ActiveWorkbook.Worksheets("PrintSheet").Range("B1").Value = Range("H1").Value

With ActiveWorkbook.Worksheets("PrintSheet").Range("B1")
     .HorizontalAlignment = xlCenter
     
End With

End Sub

Create a tab named PrintSheet

Make sure Manager is in Column A and Employee is in Column B of your main tab.
Put Manager number in column H1


This code will loop through and copy one by one all of the employees names for the selected manager into the PrintSheet tab.

 

[shg]

Why not just autofilter by manager name?

 

[Tyler_Analyst]

I'm only a few months into VBA. No one had answered his post so I was trying to help give him a code that would work, even though it probably isn't the most efficient.

 

[shg]

Maybe it's not, but helping other people is a great way to learn. That wasn't a dis