Hello,

I am attempting to create a macro that will do a few things.

1. I would like it to create a folder based on the cell "A40", which is just a cell with the date (New folder would be 12/29/2014)
2. I would then like it to run the script to fill in values on the sheet (Letters) from another worksheet (Information) and export them individually as PDF's
3. Save the PDF's in the new folder (Destination would become P:\Early Retirees\2015\12-29-2014\name Letter.pdf)

I had the fill-in macro working just fine, but for some reason, I am unable to figure out exactly where the folder creation should come into play in the macro scripting. This is what I have and it is currently not giving me an error, however, it's not creating the file or the folder. Could someone possibly help me?

Thank you,

Brian

Sub Letter()
Dim fileName As String
With Sheets("Letter")
If (.Range("A40") = vbNullString) Or (.Range("A40") = vbNullString) Then Exit Sub

On Error Resume Next
MkDir "P:\Early Retirees\2015\" & .Range("A40")
ChDir "P:\Early Retirees\2015\" & .Range("A40")
On Error GoTo 0

Application.ScreenUpdating = False
For Count = 8 To Worksheets("Information").Range("A65536").End(xlUp).Row Step 1

'Name
Worksheets("Letter").Range("A10").Value = Worksheets("Information").Cells(Count, 17).Value
'Medical
Worksheets("Letter").Range("C13").Value = Worksheets("Information").Cells(Count, 12).Value
'Dental
Worksheets("Letter").Range("C14").Value = Worksheets("Information").Cells(Count, 13).Value
'Vision
Worksheets("Letter").Range("C15").Value = Worksheets("Information").Cells(Count, 14).Value

Name = Worksheets("Information").Cells(Count, 16).Text
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:="P:\Early Retirees\2015\Merged Files\" & Name & " " & "Letter" & ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next Count
End Sub