save files as PDF for one sheet and create folders based on cell value

[Maklil]

Hello
I search macro to do:
create folder in C:\Users\RM\Desktop\Text\ based on cell G1 and create current month within created folder and save file as pdf in current month.
for instance : in G1="REPORTS"
then will become C:\Users\RM\Desktop\Text\REPORTS \
and the current month is JUNE then will become C:\Users\RM\Desktop\Text\REPORTS \JUNE\
and file name should be based on G6 for instance G6=INV NO A23455656
so will become C:\Users\RM\Desktop\Text\REPORTS \JUNE\INV NO A23455656.pdf
and should replace save file every time within created folders or create new folders if they aren't existed based on G1, current month , but I don't save sheet , just range from A:G
thanks

 

[John_w]

Try this macro:

Public Sub Save_Columns_As_PDF()

    Dim mainFolder As String, subfolder As String, PDFfile As String
    Dim lastRow As Long
    
    mainFolder = "C:\Users\RM\Desktop\Text\"
    
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"
    
    With ActiveWorkbook.ActiveSheet
        subfolder = mainFolder & .Range("G1").Value & "\"
        If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
        subfolder = subfolder & UCase(Format(Date, "mmm")) & "\"
        If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
        PDFfile = subfolder & .Range("G6").Value & ".pdf"
        lastRow = .Range("A:G").Find("*", , , , xlByRows, xlPrevious).Row
        .Range("A1:G" & lastRow).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
    
    MsgBox "Created " & PDFfile, vbInformation
    
End Sub

 

[Maklil]

Hi John

your code seems to work greatly except one thing , I'm not sure why do that .

if I run the code with existed MSG in end of the code it works and is stuck at running the file and I have to close EXCEL application!

and if I disable the MSG line in the end code then the file is ok

seems the procedure continues in running without any finishing when reach message !

 

[John_w]

if I run the code with existed MSG in end of the code it works and is stuck at running the file and I have to close EXCEL application!

You have to switch to the MsgBox window and click OK.

 

[Maklil]

I know that, the problem is not when I run the macro from the first time , the problem is when try run again for new operation then doesn't show MSG and keep running without any stopping .

 

[John_w]

I can't reproduce the problem. Try adding .Range("A1").Select before the lastRow = line.

 

[Maklil]

seems I 've found the problem . I was using page layout from bar in the sheet bottom Which shows many pages instead of one page, I change to normal

seems taking more time to finish from many pages contain logos .
thanks very much for your code, John