• 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.

help to print all dropdown list in pdf with separate name

T

tanwarrahul1988

Member
Hi users

I dont how to create macro to print all drop down list with there order no in single pdf. Every time i had to select drop down then i had to print one by one. is there any way that when i click print pdf button its print all drop down till it get 0. Same i attached the xls file.
pls help


Post moved by moderator
 

Attachments

  • TRPL Invoice - Copy.xlsm
    71.2 KB · Views: 11
Last edited by a moderator:
Hi @tanwarrahul1988

Welcome to the forum :)

Please se attached
Added some code to the PDF button

Hope this is what you were looking for
Just a small tip... you may want to add some code for when there is already a file with the same name in the destination folder. As it is it will probably return some error.

Let me know if I can help with anything else.
 

Attachments

  • TRPL Invoice - Copy.xlsm
    70.6 KB · Views: 20
tanwarrahul1988 said:
Hi PCosta

Is this possible in this same file, that all data print in single pdf file with name of date.
Click to expand...
Hi again :)

Yes it is... replace code with
Code: 
Sub GeneratePDF()

    Application.ScreenUpdating = False

    Dim c As Range
    Dim lastrow, i As Integer
    Dim Shtnames As Variant
    ReDim Shtnames(0)
   
    lastrow = Sheets("RAW").Columns("B").Cells(Rows.Count).End(xlUp).Row

    For Each c In Sheets("RAW").Range("B2:B" & lastrow)
        If c.Value <> 0 Then
            Sheets("Invoice").Range("A10").Value = c.Value
            Sheets("Invoice").Copy After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = c.Value
            Shtnames(UBound(Shtnames)) = c.Value
            ReDim Preserve Shtnames(UBound(Shtnames) + 1)
        End If
    Next c
   
    ReDim Preserve Shtnames(UBound(Shtnames) - 1)
    Sheets(Shtnames).Select
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\WYS\Documents\TRPL Invoices_" & Format(Date, "yyyymmdd") & ".pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True
   
    Application.DisplayAlerts = False
    Sheets(Shtnames).Delete
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True

End Sub

Please see attached
 

Attachments

  • TRPL Invoice - Copy.xlsm
    70.1 KB · Views: 14
Hi PCosta

I m using your provided code in this file but its showing error run time error 9. and not creating the pdf in single file. i same attach the file pls check and help me.

i m geting error on this code line

Sheets(Shtnames).Select

And

Sheets(Shtnames).Delete
 

Attachments

  • COURIER INSURANCE NEW.xlsm
    41.5 KB · Views: 3
Couple of things.

1. Below line...
Code: 
Shtnames(UBound(Shtnames)) = c.Value

Change to:
Code: 
Shtnames(UBound(Shtnames)) = CStr(c.Value)

2. Below portion in ExportAsFixedFormat part.
Code: 
Sheets("Invoice").Range("D4").Value
Will cause issue as date format contains illegal character for file name.

Change to:
Code: 
Format(Sheets("Invoice").Range("D4").Value, "mm-dd-yyyy")
 
Hi PCosta

i move this file in shared folder but when i click on print it showing error. pls check i write the path name in file.
 

Attachments

  • TRPL Invoice.xlsm
    73.1 KB · Views: 7
Hi
when i m click on button it create pdf very well but now i want to modify the path. like now i want export file in pdf but it has to save in dropbox folder so everybody can use it. So when i change the path to dropbox its malfunction with export.
 
i m using this path name

in this its showing my system and user name C:\Users\admin\.

But i want to create it so everybody can use it.

C:\Users\admin\Dropbox\ISHWAR\Daily insurance\TRPL Invoice\
 
tanwarrahul1988 said:
i m using this path name

in this its showing my system and user name C:\Users\admin\.

But i want to create it so everybody can use it.

C:\Users\admin\Dropbox\ISHWAR\Daily insurance\TRPL Invoice\
Click to expand...
I don't have dropbox on my desktop so I can't test it but it should work as with any other path, as long as you have write privileges to that folder.
Can't see why it's throwing you an error but upload the file with the new path and I will gladly take a look at it
 
No Prob PCosta

I settle it with changes of some code. And it works fine now.

Now if you can just help me if in this same file i want to print all sheet which i exporting in pdf. Same works with printer which i have connected. If its possible.
 
tanwarrahul1988 said:
No Prob PCosta

I settle it with changes of some code. And it works fine now.

Now if you can just help me if in this same file i want to print all sheet which i exporting in pdf. Same works with printer which i have connected. If its possible.
Click to expand...

Hi,

Sorry for the late response

You can use the same code by changing to "Printout":
Code: 
Sub PrintInvoice()

    Dim c As Range
    Dim lastrow, i As Integer
    Dim Shtnames As Variant
    ReDim Shtnames(0)
   
    lastrow = Sheets("RAW").Columns("B").Cells(Rows.Count).End(xlUp).Row

    For Each c In Sheets("RAW").Range("B2:B" & lastrow)
        If c.Value <> 0 Then
            Sheets("Invoice").Range("A10").Value = c.Value
            Sheets("Invoice").Copy After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = c.Value
            Shtnames(UBound(Shtnames)) = c.Value
            ReDim Preserve Shtnames(UBound(Shtnames) + 1)
        End If
    Next c
   
    ReDim Preserve Shtnames(UBound(Shtnames) - 1)
    Sheets(Shtnames).Select
   
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
   
    Application.DisplayAlerts = False
    Sheets(Shtnames).Delete
    Application.DisplayAlerts = True

End Sub

... please see attached, print button should now print to last selected printer
 

Attachments

  • TRPL Invoice.xlsm
    72.8 KB · Views: 12
Hi Pcosta87 i need your help to solve a issue in attach file. i create it but i m getting error in c.value. dont know where i m getting value pls sir help me again.
 

Attachments

  • vendors slips.xlsm
    308 KB · Views: 5
tanwarrahul1988 said:
Hi Pcosta87 i need your help to solve a issue in attach file. i create it but i m getting error in c.value. dont know where i m getting value pls sir help me again.
Click to expand...
Hi,

Sorry for the late response but I was away on vacation :(

The problem is that in the first example you were using invoice number which was unique for each invoice thus allowing the code to be written around it. Now you switched to "Settlement Ref. No." which is not unique and because of that, the macro, as it is, will not work :(
For the macro to work you will need to use a column with unique values only.
 
You must log in or register to reply here.
Back
Top