Extract user-defined sheets from different workbooks to PDF

Pteroglossus

New Member
Joined
Nov 19, 2020
Messages
3
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Pteroglossus

New Member
Joined
Nov 19, 2020
Messages
3
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top