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]

this will get your fullname

Dim fullName As String
fullName = rngListStart.Rows(i).Offset(, 1).Value

 

[johns99]

This isn't working unfortunately

I'm getting the following error

 

[johns99]

If I'm only doing the last name and the list starts in R1 is the below correct?

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 = rngListStart.Rows(i).Offset(, 1).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

 

[skillilea]

Yes, that Looks correct. You need a
Pubic Sub CreateReport to start the sub procedure

 

[johns99]

I'm getting this error now:

 

[johns99]

Disregard my last, I removed the old macro and replaced with the above and all is good, thank you so much! This was excellent!

 

[skillilea]

Awesome! Glad it worked.

 

[skillilea]

Hit me up if you need additional support