Excel VBA: dynamically saving excel sheets into one PDF based on certain criteria in the cell (1 number = 1 sheet)

Question

I got WB with different number of sheets and with the same structure and so on. There is one field let's say D8 as an example where is written number 3 (CH) and based on that number in that cell, I would like to export exactly that number of sheets into one PDF. So, if it is written five, then five pages into one PDF...or if it is one then one page in PDF. That cell in D8 will be always on the same position, but the number might differ. Can this be somehow written into the code to look on this number and to export that many sheets into one PDF?

And I would like to have an option where to save every new PDF, not like now that is automatically created, firstly folder and then file.

Here is SS of my WB:

and this is piece of code what I was using but just to save sheets into single PDF, I am not so good in VBA so any help will be great!

Sub ExportAsPDF()

  Dim FolderPath As String

  FolderPath = "C:UsersXYZDesktopPDFs"
  MkDir FolderPath
  
  Sheets(Array("CH1", "CH2", "CH3")).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "PDf", _
  openafterpublish:=False, ignoreprintareas:=False

  MsgBox "All PDF's have been exported!"
  End Sub

question from:https://stackoverflow.com/questions/65872338/excel-vba-dynamically-saving-excel-sheets-into-one-pdf-based-on-certain-criteri

Answer 1

When you ask a question, it is recommended to frequently check it and try clarifying the comments asking for clarifications...

If I understood well your question, please test the next code. It assumes that the answer to my suppositions in the comment is yes. The code offers a browse window to select the folder where to export the chosen (sheetsNo) number of sheets:

Sub ExportAsPDF()
  Dim FolderPath As String, sheetsNo As Long, sh As Worksheet, arrSheets
  Dim fldr As FileDialog, sItem As String, fileName As String, i As Long
 
  sheetsNo = ActiveCell.value 'use there the sheet you need

  ReDim arrSheets(sheetsNo - 1) 'redim the array to keep the sheets
  
  'use a dialog to select the folder to export
  Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
  With fldr
    .Title = "Select a Folder where to export the pdf file"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
 End With
NextCode:
 If sItem = "" Then Exit Sub 'if no folder selected the code stops
 
 FolderPath = sItem
  
  fileName = Replace(ThisWorkbook.Name, ".xlsm", ".pdf") 'use the workbook name, but changing its extension
  For i = 1 To sheetsNo
    arrSheets(i - 1) = Worksheets(i).Name 'put the sheets in an array
  Next

  Sheets(arrSheets).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=FolderPath & "/" & fileName, _
  openafterpublish:=False, ignoreprintareas:=False

  MsgBox "All PDF's have been exported!"
End Sub

Please, test it and send some feedback.