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

BillGinSC

New Member
Joined
Mar 30, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,977
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

New Member
Joined
Mar 30, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
1617138973547.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,614
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top