Excel VBA pdf code

Question

Hi all

A bit new to VBA. I have a macro that will produce a PDF for a excel dashboard but this means changing a name in a list and then pressing the macro 300 time!

The coding i have used that works is below. I know there is way to automate/loop this so that it will work through full list but I'm struggling to get my head round the coding.

Any help greatly received.

Thanks

Fox

Sub CreatePDF()

Dim ID As String

ID = Range("J6").Text

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:="\persian2\staffmydocs$\FowkesCr\Documents\Test" + ID + ".pdf", _

IgnorePrintAreas:=False, _

OpenAfterPublish:=False

End Sub

Sub Save_Excel_As_PDF_1()

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF

End Sub

Answer 1

Ok, looping through a validation list is quite a bit more complex, but still I have lifted an example from another website and slotted your code into this for you:

Sub CreatePDF()

Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer
Dim rows As Integer

'Set the cell which contains the Data Validation list
Set rng = Range("K7")

'If Data Validation list is not a range, ignore errors
On Error Resume Next

'Create an array from the Data Validation formula, without creating
'a multi-dimensional array from the range
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)

For i = 1 To rows
    dataValidationArray(i) = _
        Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i

'If not a range, then try splitting a string
If Err.Number <> 0 Then
    Err.Clear
    dataValidationArray = Split(rng.Validation.Formula1, ",")
End If

'Some other error has occured so exit sub
If Err.Number <> 0 Then Exit Sub

'Reinstate error checking
On Error GoTo 0

'Loop through all the values in the Data Validation Array
For i = LBound(dataValidationArray) To UBound(dataValidationArray)

    'Change the value in the data validation cell
    rng.Value = dataValidationArray(i)
    
    'Force the sheet to recalculate
    Application.Calculate
    
 Dim ID As String
ID = rng.Text
    ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="J:\All Staff\Student Data\2020 2021\Maths Sheets\" + ID + ".pdf", _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
          
Next i

End Sub

Answer 2

Hi James

I can not see your reply?

Thanks

Craig