export sheets as in pdf for each asperated folder based on cell value

[Ali M]

Hello
I search for macro
the directory should be "C:\Users\RAMDANI\Desktop\INVOICES"
the INVOICES folder should contains folders names (SELLING , PURCHASING, RETURNS). theses folders names should contains folders months names JAN,FEB.... for each folder .
if the folders names (SELLING , PURCHASING, RETURNS) & folders months name JAN,FEB.... are not existed , then should create automatically
the months folders name depend on current month in pc .
when create folders or have already existed should match with sheets names (SELLING , PURCHASING, RETURNS) . so the main folders names will be the same sheets name when matching and export sheets as in PDF
each sheet contains the same range A: H start from row2 but should ignore empty rows even the range contains borders but is empty.
and when run the macro should replace files have already existed
note: when export the sheet depends on cell value E2 into REPORT sheet . the cell E2 will be sheet name and should match with sheet name should export it.
thanks

 

[John_w]

If I've understood you correctly, try this macro, athough you haven't said what the file name of the PDF should be, so the code creates SELLING.pdf, PURCHASING.pdf or RETURNS.pdf.

Public Sub Export_Sheet_As_PDF()

    Dim mainFolder As String, subfolder As String
    Dim Wsh As Object
    Dim exportSheet As Worksheet
    Dim PDFrange As Range, i As Long
    
    mainFolder = "C:\Users\RAMDANI\Desktop\INVOICES\"
    
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"
    
    Set Wsh = CreateObject("WScript.Shell")
    
    With ActiveWorkbook
        Set exportSheet = .Worksheets(.Worksheets("REPORT").Range("E2").Value)
        With exportSheet
            subfolder = mainFolder & .Name & "\" & UCase(Format(Date, "mmm")) & "\"
            Wsh.Run "cmd /c MKDIR " & Chr(34) & subfolder & Chr(34), 0, True
            Set PDFrange = .Range("A2:H2").Resize(.UsedRange.Rows.Count - 1)
            'Hide blank rows
            For i = PDFrange.Rows.Count To 1 Step -1
                If WorksheetFunction.CountA(PDFrange.Rows(i)) = 0 Then PDFrange.Rows(i).Hidden = True
            Next
            .Range("A2:H2").Resize(.UsedRange.Rows.Count - 1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=subfolder & .Name & ".pdf", _
                    Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            .Rows.Hidden = False
            MsgBox "Exported " & .Name & " to " & subfolder & .Name & ".pdf"
        End With
    
    End With
    
End Sub

 

[Ali M]

first thanks for the code , but when I test it will freeze the file and run after long time . I no know if the problem is from my laptop.
did you test it , doesn't happen for you what happen for me?
second I'm really sorry about this

athough you haven't said what the file name of the PDF should be

yes you're right .
In addition to the name of the sheet I would like to add the content of the cell g4=INV-A233
so for instance : instead of to be SELLING.pdf , I want to be like this SELLING_INV-A233
last thing as I said

but should ignore empty rows even the range contains borders but is empty.

I don't want any empty rows for the rows contains borders & formatting when export sheet

 

[John_w]

Yes, I tested the code and it works perfectly for me. The problem could be that you don't have permission to create the "WScript.Shell" object or run the DOS MKDIR command which creates any missing subfolders under the main folder. The macro below uses native VBA commands to create the subfolders, so this problem shouldn't occur. It also includes cell G4 value from the REPORTS sheet in the PDF file name.

I don't want any empty rows for the rows contains borders & formatting when export sheet

I don't really understand exactly what you mean, but the macro temporarily hides empty rows (a row is considered empty if all the cells in A:H are empty). If the created PDF is not exactly what you want then please explain a bit more about empty rows, with screenshots showing an example sheet and what the PDF should look like.

Public Sub Export_Sheet_As_PDF2()

    Dim mainFolder As String, subfolder As String, PDFfile As String
    Dim exportSheet As Worksheet
    Dim PDFrange As Range, i As Long
    
    mainFolder = "C:\Users\RAMDANI\Desktop\INVOICES\"
    
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"
    
    With ActiveWorkbook
        Set exportSheet = .Worksheets(.Worksheets("REPORT").Range("E2").Value)
        PDFfile = .Worksheets("REPORT").Range("E2").Value & "_" & .Worksheets("REPORT").Range("G4").Value & ".pdf"
        With exportSheet
            subfolder = mainFolder & .Name & "\"
            If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
            subfolder = subfolder & UCase(Format(Date, "mmm")) & "\"
            If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
            PDFfile = subfolder & PDFfile
            Set PDFrange = .Range("A2:H2").Resize(.UsedRange.Rows.Count - 1)
            'Hide blank rows
            For i = PDFrange.Rows.Count To 1 Step -1
                If WorksheetFunction.CountA(PDFrange.Rows(i)) = 0 Then PDFrange.Rows(i).Hidden = True
            Next
            .Range("A2:H2").Resize(.UsedRange.Rows.Count - 1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                    Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            .Rows.Hidden = False
            MsgBox "Exported " & .Name & " to " & PDFfile
        End With
    
    End With
    
End Sub

 

[Ali M]

Yes, I tested the code and it works perfectly for me.

that's correct in one case if the empty rows are a little . for instance if there are 4 rows are filled and 3 rows are empty then will run without any problem , but if I have about 500 empty rows will cause this problem . the problem is how many empty rows will effect running the macro .

I don't really understand exactly what you mean, but the macro temporarily hides empty rows (a row is considered empty if all the cells in A:H are empty).

I think size of empty rows are relating with this problem .