VBA to Navigate Drop Down List and Save page as PDF

[Ramadan]

I have a worksheet with a drop down list in "B2" to filter the data and I have a code to save the viewed information in the sheet as PDF to external forlder "D:\Desktop\Daily Tasks" and to name the file based on cell value in "C2"

My drop down list has only 5 items (NH01,NH03,NH04,NH05,NH06) and I'm looking for a code to perform both of navigating the drop down list in sequance and save every sheet as PDF to this folder "D:\Desktop\Daily Tasks"

this is my code

[Sub Save_pdf()

ReportID = Range("B2")
ReportName = Range("C2")
Path = "D:\Desktop\Daily Tasks\"
fname = ReportName
MsgBox "Ok Done"
ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, ignoreprintareas:=False, Filename:=Path & fname


End Sub]

 

[Ramadan]

I have found a code similar to what I need, I have tried to edit some lines accrording my data but it's not working (Run time error - Document not saved) and depug stop on the last part of the cose . I think it's ok but need to change the file name to refer to cell "C2" not the same cell that have the drop down list "B2" and to ignore the columns ("A:P100") and to refer to the whole sheet

any Suggestions please?

here is the code I found

[Sub savefilepdf()


Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    
    destinationFolder = "D:\Desktop\Daily Tasks\"    'Same folder as workbook containing this macro
    'destinationFolder = "C:\path\to\folder\"  'Or specific folder
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = "D:\Desktop\Daily Tasks\"
         
    'Cell containing data validation in-cell dropdown
    
    Set dataValidationCell = Worksheets("Print").Range("B2")
     
    'Source of data validation list
    
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
     
    'Create PDF for each data validation value
    
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With dataValidationCell.Worksheet.Range("A1:P100")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
        
End Sub]

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach the desired selected range (not a picture) of your data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Ramadan]

@mumps here is a copy from the file to download

Download Test copy.xlsm | LimeWire

Download Test copy.xlsm on LimeWire

limewire.com

 

[Ramadan]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach the desired selected range (not a picture) of your data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I have found a code to do that and it works good - just need to change the rule to save file as PDF not excel file and to make the saved files name = ("C2") value + the current date

I think also that there are some parts in the code to copy and paste the sheet which I think it's not improtand for my needs so if it can be removed will be good

here is the code

[Sub myFiles()

Dim wb As Workbook
Dim ws As Worksheet
Dim nwb As Workbook
Dim nws As Worksheet
Dim rng As Range
Dim Path As String
Dim myDate As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Print")
Set rng = ws.Range("B2")
Path = "D:\Desktop\Docs\"
myDate = Format(Now(), "MM-DD-YYYY")

For i = 1 To 5
rng = ws.Range("A" & i)

ws.Copy

Set nwb = ActiveWorkbook
Set nws = nwb.Worksheets("Print")

With nws
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
End With

Application.DisplayAlerts = False
nwb.SaveAs filename:=Path & rng & " " & myDate & ".xlsx", FileFormat:=xlWorkbookDefault
nwb.Close
Application.DisplayAlerts = True

Next i



End Sub]

 

[Peter_SSs]

@Ramadan
Please stop adding these extra characters in your vba codes.

 

[Ramadan]

@Ramadan
Please stop adding these extra characters in your vba codes.

View attachment 121962

View attachment 121963

Dear Mr. Peter - actually I don't add it they just appear when I click to add vba code and I paste the code in between - if not important will not do that - thanks anyway

 

[Peter_SSs]

they just appear when I click to add vba code and I paste the code in between

That doesn't happen for anybody else (take a look at the vba code in posts by other people)

vba code tags by themselves look like this
[CODE=vba][/CODE]

When I look at the detail of your posts I see this at the start

and this at the end

That is, there is an extra pair of brackets.

 

[Ramadan]

That doesn't happen for anybody else (take a look at the vba code in posts by other people)

vba code tags by themselves look like this
[CODE=vba][/CODE]

When I look at the detail of your posts I see this at the start
View attachment 121966

and this at the end

View attachment 121967

That is, there is an extra pair of brackets.

thanks for clarfication