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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

ranman256

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

Forum statistics

Threads
1,148,194
Messages
5,745,276
Members
423,942
Latest member
excelhelp1423

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