Posted by TJ on Friday March 4, 2016 @ 11:53 AM
[Tags: Excel, tips, accounting]

If you manage a partnership you might need to send out individual or personalized statements showing account activity or member financial information. If you already have a table in excel with the data one of the easiest ways to make an individual statement is to add a new tab and create the statement and then add a drop down (data validation) on the statement tab to select each persons name.

To add a drop down
1) From the Data Ribbon select Data Validation
2) Select Allow "list"
3) Your source should be the list of names from the data table you are pulling information from.

Now that you have a dropdown on the statement tab you can use the formula "SUMIF" to retrieve only the specific data from your data table for the name you select from the dropdown box.

Now if you have a lot of names you might want to automate selecting each name and printing the individual statement to the pdf. The below VB Code/macro will do this as long as on your statement tab you have the dropdown in cell A1 and the filename and path in cell A2 (ex: c:/folder/)

Sub printstatements()
'  Print individual statements with dropdown in cell A1 and filename in A2
' by tjshome
'
  
    'ActiveSheet.PrintOut
    'With ActiveSheet
          
         ' .ExportAsFixedFormat _
         '       Type:=xlTypePDF, _
         '       
         '       OpenAfterPublish:=False
         
         ' dropdown
            Dim strValidationRange As String
            Dim rngValidation As Range
             Dim rngDepartment As Range
    
            ' Turn off screen updating
            'Application.ScreenUpdating = False
    
            ' Identify the source list of the data validation
	    ' Replace cell A1 with the cell of your datavalidation dropdown with member names
            strValidationRange = Range("A1").Validation.Formula1
            Set rngValidation = Range(strValidationRange)
    
            ' Set the value in the selection cell to each selection in turn
            ' and print the results.
             For Each rngDepartment In rngValidation.Cells
             Range("A1").Value = rngDepartment.Value
        
        'Call Print Macro Here
   
    'print and format
    ' make sure file name and path is in cell A2 and make sure folders exists ex. c:/folder1/

        Dim ThisFile As Variant
        ThisFile = ActiveSheet.Range("A2").Value & ActiveSheet.Range("A1").Value & ".pdf"
       ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
         
   ' dropdown
    Next
    'Application.ScreenUpdating = True
   
End Sub