Hi,
I have a worksheet with an input sheet and an output sheet. On the output worksheet there is a list box displaying workers' names (e.g 100 names). By clicking on a different name it will display the info of that worker in a report format.
Currently, when I hi-light all the names in that listbox and hit the print button, it will automatically refresh the report one employee at a time and print out all 100 individual report to the printer.
But due to sensitivity of the individual reports, I now need save it as a PDF file and not send it to a printer.
In C15 on the output worksheet that is where the employee name is, say John Doe. How do I make macro to capture the info on this cell C15 and add a string of text behind it and save as pdf?
That means each pdf file would be saved as "John Doe - Performance Report April 2012.pdf" and the next person "Mary Doe - Performance Report April 2012.pdf", and the next one "David Copperfield - Performance Report April 2012.pdf", etc. There are 100 names and manually save each file and type in their name over and over again with a fixed text string should be
How do I use macro to capture the file names using macro?
SirJB7 was nice enough to help me started this with 3 macros and I have expanded the last one somewhat.
Private Sub lstPrinting_LostFocus()
Dim I As Integer, J As Integer
J = 0
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then J = J + 1
Next I
End With
Range("H3").Value = J
End Sub
Private Sub cmdPrintSelected_Click()
Dim I As Integer, J As Integer
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then
Range("C3").Value = I + 1
ActiveSheet.PrintOut
End If
Next I
End With
End Sub
Private Sub cmdUpdateZSelected_Click()
Dim I As Integer, J As Integer
With lstPrinting
' Quarter 2 Goal
For I = 0 To .ListCount - 1
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 13).Value = Range("O3").Value
End If
' Quarter 3 Goal
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 21).Value = Range("P3").Value
End If
' Quarter 4 Goal
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 29).Value = Range("Q3").Value
End If
' Quarter 1 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 7).Value = Range("N4").Value
End If
' Quarter 2 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 15).Value = Range("O4").Value
End If
' Quarter 3 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 23).Value = Range("P4").Value
End If
' Quarter 4 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 31).Value = Range("Q4").Value
End If
Next I
End With
End Sub
My guess is that I need to expand the last macro so that as the "summary" worksheet is being refreshed a pdf file can be saved as well.
Thanks for the help.
I have a worksheet with an input sheet and an output sheet. On the output worksheet there is a list box displaying workers' names (e.g 100 names). By clicking on a different name it will display the info of that worker in a report format.
Currently, when I hi-light all the names in that listbox and hit the print button, it will automatically refresh the report one employee at a time and print out all 100 individual report to the printer.
But due to sensitivity of the individual reports, I now need save it as a PDF file and not send it to a printer.
In C15 on the output worksheet that is where the employee name is, say John Doe. How do I make macro to capture the info on this cell C15 and add a string of text behind it and save as pdf?
That means each pdf file would be saved as "John Doe - Performance Report April 2012.pdf" and the next person "Mary Doe - Performance Report April 2012.pdf", and the next one "David Copperfield - Performance Report April 2012.pdf", etc. There are 100 names and manually save each file and type in their name over and over again with a fixed text string should be
How do I use macro to capture the file names using macro?
SirJB7 was nice enough to help me started this with 3 macros and I have expanded the last one somewhat.
Private Sub lstPrinting_LostFocus()
Dim I As Integer, J As Integer
J = 0
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then J = J + 1
Next I
End With
Range("H3").Value = J
End Sub
Private Sub cmdPrintSelected_Click()
Dim I As Integer, J As Integer
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then
Range("C3").Value = I + 1
ActiveSheet.PrintOut
End If
Next I
End With
End Sub
Private Sub cmdUpdateZSelected_Click()
Dim I As Integer, J As Integer
With lstPrinting
' Quarter 2 Goal
For I = 0 To .ListCount - 1
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 13).Value = Range("O3").Value
End If
' Quarter 3 Goal
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 21).Value = Range("P3").Value
End If
' Quarter 4 Goal
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 29).Value = Range("Q3").Value
End If
' Quarter 1 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 7).Value = Range("N4").Value
End If
' Quarter 2 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 15).Value = Range("O4").Value
End If
' Quarter 3 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 23).Value = Range("P4").Value
End If
' Quarter 4 Net Payout
If .Selected(I) = True Then
Range("C3").Value = I + 1
Worksheets("Summary").Cells(I + 6, 31).Value = Range("Q4").Value
End If
Next I
End With
End Sub
My guess is that I need to expand the last macro so that as the "summary" worksheet is being refreshed a pdf file can be saved as well.
Thanks for the help.