save file and create folders and sub folders based on condition

[Hasson]

hello
firs I need create folder based on current year with word calls sales 2021 and the sub folders names from sale1 up to 12
I want when save the file as PDF( to subfolder sale1 when reaches up to 60 files then move to next subfolder sale2 ... and so on
so each subfolder is limited when save files only 60 files and when enter a new year then create the folder call sales 2022 and create subfolders and so on every a new year
note: save as pdf based on range b2 =INV-BVN-1000 and the range from A1 : G is dynamically

 

[Hasson]

helps?

 

[John_w]

I suspect no one has replied because your request isn't very clear. I don't understand all of it, so my interpretation and macro below might be wrong.

In which parent folder should the "Calls Sales 2021" subfolder be created? This folder is defined by the string parentFolder - edit the code as required.

note: save as pdf based on range b2 =INV-BVN-1000

I don't understand that. What should the file name of the PDF file be? Is it the contents of cell B2 (on the active sheet)? The file name must be unique because you can't have files with the same file name in a folder (the sale1, sale2, ... sale12 subfolders). The macro therefore names the PDF after cell B2 and the current date and time in YYYYMMDD HHMMSS format.

the range from A1 : G is dynamically

Do you want to save the range A1 to the last row in column G (on the active sheet) in the PDF?

What should happen when the sale12 subfolder is full? In that case the macro doesn't create a PDF and displays a warning.

Try this macro and see if it's near what you want. Change the Const values to lower numbers to test that the macro correctly creates the next sale1, sale2, etc. subfolder when the maximum number of PDFs has been reached.

Public Sub Create_PDF_In_Year_Sales_Folder()

    Dim FSO As Object
    Dim FSfolder As Object
    Dim parentFolder As String
    Dim yearFolder As String, saleSubfolder As String
    Dim saleNumber As Long
    Dim PDFfile As String
    Dim foundsaleSubfolder As Boolean
    Dim lastRowG As Long
   
    Const saleSubfolderMaxFiles = 60                     'Maximum number of PDFs in each sale<n> subfolder
    Const saleMaxNumber = 12                             'Maximum number of sale<n> subfolders
   
    parentFolder = "C:\path\to\parent folder\"           'Parent folder of each "Calls Sales <current year>" subfolder
   
    If Right(parentFolder, 1) <> "\" Then parentFolder = parentFolder & "\"
    yearFolder = parentFolder & "Calls Sales " & Year(Date) & "\"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    If Not FSO.FolderExists(yearFolder) Then
        FSO.CreateFolder yearFolder
    End If
   
    foundsaleSubfolder = False
    saleNumber = 1
    Do
        saleSubfolder = yearFolder & "sale" & saleNumber & "\"
        Debug.Print saleSubfolder
        If FSO.FolderExists(saleSubfolder) Then
            Set FSfolder = FSO.GetFolder(saleSubfolder)
            If FSfolder.Files.Count = saleSubfolderMaxFiles Then
                saleNumber = saleNumber + 1
            Else
                foundsaleSubfolder = True
            End If
        Else
            FSO.CreateFolder saleSubfolder
            foundsaleSubfolder = True
        End If
    Loop Until foundsaleSubfolder Or saleNumber > saleMaxNumber
   
    If saleNumber <= saleMaxNumber Then
        With ActiveWorkbook.ActiveSheet
            PDFfile = saleSubfolder & .Range("B2").Value & " " & Format(Now, "yyyymmdd hhmmss") & ".pdf"
            lastRowG = .Cells(.Rows.Count, "G").End(xlUp).Row
            .Range("A1:G" & lastRowG).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            MsgBox "Created " & PDFfile, vbInformation
        End With
    Else
        MsgBox "PDF not created because there are " & saleSubfolderMaxFiles & " files in all " & saleMaxNumber & " sale subfolders in " & yearFolder, vbExclamation
    End If
   
End Sub

 

[Hasson]

thanks so much I 'm wondered how isn't clear but you achieved what exactly what I want
well done ! just I want making clear about the value in b2 b2 =INV-BVN-1000 should increment INV-BVN-1001 , INV-BVN-1002 and so on