locked
Macro with multiple condition to create Excel data to PDF file RRS feed

  • Question

  • I read everything about "macro to create excel data to pdf file" (Hans Vogelaars posts) but I just can't figure this out on my own. Here is my problem:

    column A is customer name: customer A, customer B…
    column B is contact person: cp1; cp2…
    column C is index number: customer A index number, customer B index number…
    column D is print date: should include date when the bill is printed
    column E – P is header:  header is months name, cell values depend on calculations (column P – column Q). (A1 is customer A and his bills value in January is in cell D1, customer B bills value in cell D2 etc (February is in cell E1/E2 etc))
    column Q is purchases: calculated purchase sum
    column R is compensation: calculated compensation sum
    column S is bill_number: running number for each printed bill, so it should appear when the bill is printed
    By using command button (and dropdown box?), I should be able to collect data based on customer name (column A) and month name (columns E-P), so I could create an invoice (in pdf and excel format) for desired month and for each customer.
    Invoice should include customer name (col A), contact person (col B), index number (col C), print date and bill number (which should appear after printing in columns D and S for selected customers cell), some predefined text, purchase sum, compensation sum and total value.
    There should also be possibility to choose one or two customers at a time, or all at once.
    Print to pdf/excel should go to predefined folder named by customer name, print date and selected months name.

    Part of necessary code is on those posts I mentioned, but at this point I'd rather see the solution in whole...

    Thursday, August 24, 2017 2:11 PM

All replies

  • Hi,

    Could you share your file via cloud storage such as OneDrive, Dropbox, etc.
    It would be time-saving for us who want to help you.

    Regards,

    Ashidacchi

    Thursday, August 24, 2017 10:48 PM
  • Hi Tim2017,

    List box control support multiple selecting, so I would  suggest you use a list box for selecting customer and a list box for selecting month.

    Here is the demonstrate


    Here is the code for initializing these list box,

    Sub initListBox()
    Dim ws As Worksheet
    Set ws = Worksheets("SourceSheet")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ListBox1.Clear
    ListBox1.MultiSelect = fmMultiSelectMulti
    For i = 2 To lastRow
    ListBox1.AddItem ws.Cells(i, 1).Value
    Next i
    
    ListBox2.Clear
    ListBox2.MultiSelect = fmMultiSelectMulti
    For i = 5 To 16
    ListBox2.AddItem ws.Cells(1, i).Value
    Next i
    End Sub

    Here is the code for button click. You could to adjust them for your indeed.

    Private Sub CommandButton1_Click()
    Dim sourceSheet As Worksheet
    Dim printSheet As Worksheet
    Set sourceSheet = Worksheets("SourceSheet")
    Set printSheet = Worksheets("PrintSheet")
    printSheet.Cells.Clear
    Dim RowIndex, ColIndex As Integer
    Application.ScreenUpdating = False
    Dim nameArr() As Integer
    Dim customCount As Integer
    For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
    RowIndex = WorksheetFunction.Match(ListBox1.List(i), sourceSheet.Columns(1), 0)
    customCount = customCount + 1
    ReDim Preserve nameArr(1 To customCount) As Integer
    nameArr(customCount) = RowIndex
    End If
    Next i
    Dim monthArr() As Integer
    Dim monthCount As Integer
    For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i) Then
    ColIndex = WorksheetFunction.Match(ListBox2.List(i), sourceSheet.Rows(1), 0)
    monthCount = monthCount + 1
    ReDim Preserve monthArr(1 To monthCount) As Integer
    monthArr(monthCount) = ColIndex
    End If
    Next i
    'set title
    printSheet.Range("A1") = sourceSheet.Range("A1")
    printSheet.Range("B1") = sourceSheet.Range("B1")
    printSheet.Range("C1") = sourceSheet.Range("C1")
    For i = LBound(monthArr) To UBound(monthArr)
    printSheet.Cells(1, 3 + i) = sourceSheet.Cells(1, monthArr(i))
    Next i
    printSheet.Cells(1, 4 + UBound(monthArr)) = sourceSheet.Range("D1") 'Date
    printSheet.Cells(1, 5 + UBound(monthArr)) = sourceSheet.Range("Q1") 'purchases
    printSheet.Cells(1, 6 + UBound(monthArr)) = sourceSheet.Range("S1") 'purchases
    With printSheet
    For i = LBound(nameArr) To UBound(nameArr)
    sourceRowIndex = nameArr(i)
    .Cells(i + 1, 1) = sourceSheet.Range("A" & sourceRowIndex)
    .Cells(i + 1, 2) = sourceSheet.Range("B" & sourceRowIndex)
    .Cells(i + 1, 3) = sourceSheet.Range("C" & sourceRowIndex)
              For j = LBound(monthArr) To UBound(monthArr)
               .Cells(i + 1, 3 + j) = sourceSheet.Cells(sourceRowIndex, monthArr(j))
              Next j
    .Cells(i + 1, 4 + UBound(monthArr)) = sourceSheet.Cells(sourceRowIndex, 4)
    sourceSheet.Cells(sourceRowIndex, 4).Copy .Cells(i + 1, 4 + UBound(monthArr))
    .Cells(i + 1, 5 + UBound(monthArr)) = WorksheetFunction.Sum(.Range(.Cells(i + 1, 4), .Cells(i + 1, 3 + UBound(monthArr))))
    .Cells(i + 1, 6 + UBound(monthArr)) = sourceSheet.Cells(sourceRowIndex, 19)
    Next i
    .Columns.AutoFit
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Desktop\ExcelFile\" & "TestPDF", openafterpublish:=True
    End With
    Application.ScreenUpdating = True
    End Sub

    Here is my test file.

    https://1drv.ms/x/s!ArC0gnwxLv5qhinE9W9tVORb-yJQ 

    Best Regards,

    Terry

    Friday, August 25, 2017 8:49 AM
  • Awesome Terry! Many thanks, this works great. LBound, UBound were new to me...

    But what if calculated compensation sum alters every month, in that case I'd use columns T-AE to assign each months compensation there. How can I acquire this data on a printSheet (and pdf file)?

    And if I want data to be positioned so it would not be in one row, but for example this way: customer name in cell A10, contact person, index number, print date and bill number in cells F2-5, purchase sum, compensation sum and total value in cells F15-18.

    Saturday, August 26, 2017 3:46 PM
  • Hi Tim2017,

    It seems that you don't need show selected month on the PrintSheet and want to print details of one custom once time, according to this, I did some change.

    This code will print pdf for each customer you selected. You could calculate purchase Sum and compensation Sum when looping month Array.

    Private Sub CommandButton1_Click()
    
    Dim sourceSheet As Worksheet
    
    Dim printSheet As Worksheet
    
    Set sourceSheet = Worksheets("SourceSheet")
    
    Set printSheet = Worksheets("PrintSheet")
    
    printSheet.Cells.Clear
    
    Dim RowIndex, ColIndex As Integer
    
    Application.ScreenUpdating = False
    
    Dim nameArr() As Integer
    
    Dim customCount As Integer
    
    For i = 0 To ListBox1.ListCount - 1
    
    If ListBox1.Selected(i) Then
    
    RowIndex = WorksheetFunction.Match(ListBox1.List(i), sourceSheet.Columns(1), 0)
    
    customCount = customCount + 1
    
    ReDim Preserve nameArr(1 To customCount) As Integer
    
    nameArr(customCount) = RowIndex
    
    End If
    
    Next i
    
    Dim monthArr() As Integer
    
    Dim monthCount As Integer
    
    For i = 0 To ListBox2.ListCount - 1
    
    If ListBox2.Selected(i) Then
    
    ColIndex = WorksheetFunction.Match(ListBox2.List(i), sourceSheet.Rows(1), 0)
    
    monthCount = monthCount + 1
    
    ReDim Preserve monthArr(1 To monthCount) As Integer
    
    monthArr(monthCount) = ColIndex
    
    End If
    
    Next i
    
    For i = LBound(nameArr) To UBound(nameArr)
    
    With printSheet
    
    sourceRowIndex = nameArr(i)
    
    .Cells(10, 1) = sourceSheet.Range("A" & sourceRowIndex) 'A10 name
    
    .Cells(2, 6) = sourceSheet.Range("B" & sourceRowIndex)   'F2 contact
    
    .Cells(3, 6) = sourceSheet.Range("C" & sourceRowIndex)    'F3 index
    
    sourceSheet.Cells(sourceRowIndex, 4).Copy .Cells(4, 6)      'F4 date,copy could keep date format
    
    .Cells(5, 6) = sourceSheet.Cells(sourceRowIndex, 19)             'F5
    
    purchaseSum = 0
    
    compensationSum = 0
    
              For j = LBound(monthArr) To UBound(monthArr)
    
               purchaseSum = purchaseSum + sourceSheet.Cells(sourceRowIndex, monthArr(j))
    
               compensationSum = compensationSum + sourceSheet.Cells(sourceRowIndex, monthArr(j) + 15)
    
              Next j
    
    .Cells(15, 6) = purchaseSum                                                                  'F15
    
    .Cells(16, 6) = compensationSum                                                       'F16
    
    .Cells(17, 6) = purchaseSum - compensationSum                      'F17
    
    .Columns(6).HorizontalAlignment = xlRight
    
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    
    "C:\Users\Desktop\ExcelFile\" & .Cells(10, 1), openafterpublish:=True
    
    End With
    
    Next i
    
    Application.ScreenUpdating = True
    
    End Sub

    Best Regards,

    Terry

    Monday, August 28, 2017 10:07 AM