VBA to Conditionally Lookup a data field, paste to a different worksheet and print to pdf

[BillGinSC]

Hello-I'm hopeful someone can assist me in writing a command to accomplish the following:

Data Worksheet: I have a data file that is extracted from a database each month holding approximately 4,000 records...each record is a unique individual person with various data elements listed for each. One of those data elements is a "region" id and a second is a "payroll id"
Report Worksheet: The same file has a prebuilt dashboard report in this second worksheet that provides summary performance for each individual person listed in the data worksheet and is populated via lookups that are triggered by the entry of the "payroll id" for each individual.

I would like to perform the following:
1) Automate the entry of the individual's "payroll id" into the dashboard and print the dashboard to pdf with a filename that includes the employee name (which is listed in a cell on both worksheets).
2) Loop through all employees in a specific region (in other words, first filter out a specific region), generating pdf dashboards for each employee of that region and saving those to a specific drive.

I've tried multiple attempts to write this myself; however, I'm completely new to VBA and am attempting to patch together other found steps to accomplish the goal.

Thank you in advance for any advice or assistance you might be able to provide.
Bill

 

[ranman256]

we cant guess at it. you'd have to give us a 'sanitized' copy of data, and the results wanted.
or
you could just record macro, then go thru the steps on your own.
record the data import,
record the Print 2 pdf,
etc..
then tweak, and run the macros for next time.

 

[BillGinSC]

Thank you...I'm going to try to record the macro step by step and tweak.

The first step is to filter by region. Using other help threads, I crafted this; however, it's not working to lookup a value (B1 on the "Data Input for Macro" worksheet) and filter the data by that column in the main data population "column A, beginning at cell A4" of the "Provider Listing" worksheet).

I'm getting an Autofilter Method of Range class failed on the italicized line below.

Sub Step1FilterbyRegion()
'
' Step1FilterbyRegion Macro
'
Dim region As Range

With Worksheets("Data Input for Macro")
Set region = .Range("B1")
End With

With Worksheets("Provider Listing")
ActiveSheet.AutoFilterMode = False
With .Range("A4" & .Cells(.Rows.Count, "B").End(xlUp).Row)
.AutoFilter Field:=1, Criteria1:=region
End With
.AutoFilterMode = False
End With

End Sub


Here's an image of my "Provider Listing" worksheet.