• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create multiple separate pdf from cell values having sheet names

Status
Not open for further replies.
jb

jb

Member
Hello helpers,
I have an excel files with almost 30 sheets.
My first sheet is named menu.
On menu sheet, I have multiple sheet names from a5 to a15 (11 names are written) . for e.g. task1, task5, task7, task11 and so on.
My file contains sheets with these names.

I want vba code to be attached with button which can perform following tasks:
1. when i click on the button, my code should allow me to choose save as folder.
2. It should generate multiple separate pdf with sheet names written in cells a5 to a15
3. It should not open pdf files after creation.

I have found following code but it is not working as per my requirement.

>>> use - code <<<
Code: 
Dim FName As Variant
    FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("a5").Value , _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")
    If FName <> False Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
    End If
 
Last edited by a moderator:
Try this should work as per your requirement.

Code: 
Sub ExportSheetsToPDF()
    Dim folderPath As String
    Dim sheetName As String
    Dim wsMenu As Worksheet
    Dim i As Integer
    
    ' Set the menu sheet
    Set wsMenu = ThisWorkbook.Sheets("menu")
    
    ' Choose folder for saving PDFs
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder to Save PDFs"
        .Show
        If .SelectedItems.Count > 0 Then
            folderPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    ' Loop through cells A5 to A15 in the menu sheet
    For i = 5 To 15
        sheetName = wsMenu.Range("A" & i).Value
        
        ' Check if the sheet exists
        If SheetExists(sheetName) Then
            ' Export the sheet to PDF
            ActiveWorkbook.Sheets(sheetName).ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=folderPath & sheetName & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        End If
    Next i
End Sub


Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function
 
  • Like
Reactions: jb
Sorry for bothering you once again but I need your help in my another thread as well. There also you have sent me vba code but it is not working or else I am doing something wrong somewhere. Please help.
 
I appreciate your feedback. Could you please specify which second question you are referring to and provide details to support your Query?
 
Monty said:
I appreciate your feedback. Could you please specify which second question you are referring to and provide details to support your Query?
Click to expand...

Generate consolidate list dynamically from multiple sheets

Hello Helpers, Trying to explain my problem. I have an excel file for maintaining list of students participating in various events of an institute. I have one sample excel which contains 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet...
chandoo.org
 
jb said:

Generate consolidate list dynamically from multiple sheets

Hello Helpers, Trying to explain my problem. I have an excel file for maintaining list of students participating in various events of an institute. I have one sample excel which contains 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet...
chandoo.org
Click to expand...
Hello Monty sir I was talking about below problem,

Trying to explain my problem. I have an excel file for maintaining list of students participating in various events of an institute.

I have one sample excel which contains 4 sheets right now.
  1. First sheet "event_list" has list of sheet names under column title "Event Sheet Title". Right now there 2 names in cell b4 and b5 - tennis and cricket respectively. Tennis and cricket are 2 activities arranged for students. Note: There can be new sheets of new sports events can be added in future. Also, in one sheet, there can be multiple entry of one student for various time period.
  2. Now in sheet tennis and cricket, I have list of students from various classes who have participated in tennis and cricket event.
  3. I want to generate consolidated list of students in the last consolidated sheet. In this sheet, I want to provide drop down list from which user can select class name. Here, it should display list of all the students of respective class who have participated in all the events in sorted order of Enrollment number.

This data is required to calculate leaves of students. Sample calculation given in attached file. I have calculated consolidated sheet manually to explain the type of report required to be generated. This list must be dynamically generated from the list of events entered on first sheet. When event is added, consolidated report must be automatically updated.

Please provide me a solution with formula or vba code. Power query is not suitable for us.
 

Attachments

  • testing_date.xlsx
    12.4 KB · Views: 0
Hey Jb...Try this, as per your requirements !

Code: 
Option Explicit

Sub GenerateConsolidatedList()
    Dim wsEventList As Worksheet
    Dim wsConsolidated As Worksheet
    Dim lastRow As Long
    Dim eventCell As Range

    ' Set references to worksheets
    Set wsEventList = ThisWorkbook.Sheets("event_list")
    Set wsConsolidated = ThisWorkbook.Sheets("consolidated")

    ' Clear existing data in consolidated sheet
    wsConsolidated.Cells.Clear

    ' Loop through event names in "event_list" sheet
    For Each eventCell In wsEventList.Range("B4:B" & wsEventList.Cells(wsEventList.Rows.Count, "B").End(xlUp).Row)
        ' Add event name as a sheet
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = eventCell.Value

        ' Copy data from event sheet to consolidated sheet
        wsConsolidated.Cells(1, wsConsolidated.Cells(1, Columns.Count).End(xlToLeft).Column + 1).Value = eventCell.Value
        lastRow = Sheets(eventCell.Value).Cells(Sheets(eventCell.Value).Rows.Count, "A").End(xlUp).Row
        Sheets(eventCell.Value).Range("A2:D" & lastRow).Copy wsConsolidated.Cells(2, wsConsolidated.Cells(1, Columns.Count).End(xlToLeft).Column + 1)

        ' Delete the temporary event sheet
        Application.DisplayAlerts = False
        Sheets(eventCell.Value).Delete
        Application.DisplayAlerts = True
    Next eventCell
End Sub
 

jb​

As You've noted ... continue with Your original thread:

Generate list from multiple sheets based on criteria

Hello Helpers, I have for example 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet Title". Right now there 2 names in cell b4 and b5 - tennis and cricket respectively. Note: There can be new sheets of new sports events can be added in future...
chandoo.org
This thread is closed now.
 
Status
Not open for further replies.
Back
Top