VBA Help

[johns99]

Hello, I'm currently using the following VBA to save files from a list of employee IDs to PDF files. Currently, the coding is saving the files with the employee IDs, but I'd like to change this to the employee names. Does anyone know what I need to do to update the below?


'Declare variables
Dim ws As Worksheet
Dim rngID As Range
Dim rngListStart As Range
Dim rowsCount As Long
Dim i As Long
Dim pdffilepath As String
Dim tempPdfFilePath As String

'Stop screen updating during running
Application.ScreenUpdating = False


'Reference the Report Sheet
Set ws = ActiveWorkbook.Sheets("Sample Statement")

'Reference Employee ID cell
Set rngID = ws.Range("G5")


'Reference the start of the employee list
Set rngListStart = ws.Range("Q10")

'Refeence the pdfFilePath
pdffilepath = "W:\Long Term Incentive Awards\PAYOUTS\2013 OICP\2024 January RSU Vestings\US Employees\STATEMENTS\LTI Vesting Statement - [ID].pdf"

'Count the number of employee ID
rowsCount = rngListStart.CurrentRegion.Rows.Count - 1

For i = 1 To rowsCount


'Change the current employee ID
rngID.Value = rngListStart.Offset(i - 1, 0).Value

'Replace [ID] with the current employee ID
tempPdfFilePath = Replace(pdffilepath, "[ID]", rngID.Value)

'Create the PDFs
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=tempPdfFilePath

Next i

'Restart screen updating during running
Application.ScreenUpdating = True


End Sub

 

[skillilea]

I'm assuming you have a list of employees with fields Id, FirstName, LastName or something like that.

This line is where you would get the First and Last Name instead of the current Id.

'Change the current employee ID
rngID.Value = rngListStart.Offset(i - 1, 0).Value

maybe:
Dim firstLastName as string

firstLastName = rngListStart.Offset(i - 1, 1).Value & " " & rngListStart.Offset(i - 2, 0).Value

 

[johns99]

Thank you, would this compromise the ID being changed on the lookup?

 

[skillilea]

I'm assuming you want this: LTI Vesting Statement - [ID].pdf" to become LTI Vesting Statement - John Smith.pdf"

Correct?

 

[johns99]

Yes, that's correct. However, isn't rngID.Value = rngListStart.Offset(i - 1, 0).Value what I need in the coding to replace the employee ID for the lookup? Currently, it's copying the IDs from the list on Q10 and replacing in G5.

 

[skillilea]

Can you share your worksheet?

 

[johns99]

 

[johns99]

So to explain, we're creating statements for employees, 48 total, everything looksup to cell G5. The coding copies the next eeid from the list and pastes in G5, then saves as PDF.

Hope this is more clear, thansk.

 

[skillilea]

Assuming your first and last names are somewhere around "C1", just change fullName to correct cell reference.

Public Sub PrintStockGrants()

Dim ws As Worksheet
Dim rngID As Range
Dim rngListStart As Range
Dim rowsCount As Long
Dim i As Long
Dim pdffilepath As String
Dim tempPdfFilePath As String

'Stop screen updating during running
Application.ScreenUpdating = False


'Reference the Report Sheet
Set ws = ActiveWorkbook.Sheets("Sheet1")

'Reference Employee ID cell
Set rngID = ws.Range("G5")

'Reference the start of the employee list
Set rngListStart = ws.Range("Q10")

'Refeence the pdfFilePath
pdffilepath = "W:\Long Term Incentive Awards\PAYOUTS\2013 OICP\2024 January RSU Vestings\US Employees\STATEMENTS\LTI Vesting Statement - [ID].pdf"

'Count the number of employee ID
rowsCount = rngListStart.CurrentRegion.Rows.Count - 1

For i = 1 To rowsCount

'Change the current employee ID
rngID.Value = rngListStart.Offset(i - 1, 0).Value

'Replace [ID] with the current employee ID
' get the first/last name
Dim fullName As String

fullName = ws.Range("c1").Value & " " & ws.Range("d1").Value
tempPdfFilePath = Replace(pdffilepath, "[ID]", fullName)

'Create the PDFs
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=tempPdfFilePath

Next i

'Restart screen updating during running
Application.ScreenUpdating = True
End Sub

 

[johns99]

Sorry forgot to include the names, see screenshot