Extract user-defined sheets from different workbooks to PDF

[Pteroglossus]

Hi everyone,

For my first two VBA projects, I was able to find answers on this forum in threads closely related to what I wanted to do. This time I'm at a loss!

At work, we're required to send monthly reports of hours worked by each of our 25 team members.

Each agent keeps track of their worked hours in a workbook, composed of monthly sheets named 'January 20', 'February 20', all stored in the same folder.

I'd like to build a macro that would cycle through all the workbooks and export, for instance, the 25 individual 'November 2020' sheets into a single PDF using "ExportAsFixedFormat".

Ideally, as the agent in charge of doing that is not computer-savvy, a simple button that would open a dialog box with a drop-down menu of the selectable /months sheets would be great.


I think I could use the code described here for the "loop" part of the macro, but how would I go about including the dialog box and drop-down menu listing all the sheets' names?

Thanks for any assistance you might provide!
Kevin

 

[Pteroglossus]

Based on the code from the above-mentioned link, I'd need to replace the "If" function in the middle with my own way of selecting the sheets I'd like to export.

Sub alkaline55()
Dim Fso As Object, Fldr, myPath As String, f As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
myPath = "C:\Users\Joe\Documents\Test\"  'Change path to the path for the folder whose files you want to loop through
Set Fldr = Fso.getfolder(myPath).Files
Application.ScreenUpdating = False
For Each f In Fldr
    If f.Name Like "*.xlsx" Then
        Workbooks.Open f.Name
        With Workbooks(f.Name)

                If .Sheets.Count >= 6 Then
                For i = 1 To 6
                    .Sheets(i).Select Replace:=False
                Next i
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                myPath & Replace(f.Name, ".xlsx", ".pdf"), Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                False
                Workbooks(f.Name).Close savechanges:=False
            Else
                MsgBox "File: " & f.Name & " has fewer than 6 sheets and will be skipped
            End If

        End With
    End If
Next f
Application.ScreenUpdating = True
End Sub

I did some research, and it seems I'd need to resort to a Selection Box, as shown here. Two questions then:

1. Can I dynamically populate the Selection Box with the names of the sheets in my workbooks?
2. How should I assemble those two pieces of code. the "loop" part and the "selection" part?

Looking forward to reading your ideas!
Kevin