Macro to Create Folder and Run Script

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

Welcome to the forum!

Please use code tags when posting code. Easy way is to click the # icon in a reply to add them.

First off, do not use command names for variable names. Think of Name as the old Rename command word if you know DOS.

Format() is used to format a date value into a valid string for a subfolder name.

Your code assumes that the parent folders already exist. You can check file and folder existence with a Dir().

Prior to running code, be sure to do a Compile. I add that to my VBE toolbar.

Keep pressing F8 in a Sub to debug line by line. Once execute, hover mouse over variable to see the value.

Anyway, here is a stab at your code.

Please Login or Register  to view this content.

hi all, allow me to see this content. thank you

This worked absolutely perfectly.

Thank you so much for your help, Kenneth.