Please help to modify this code to include the following command, if column H:H in ws.Neonpay is equal to "B" print to PDF a list of the lines in column A:A in wsNeonpay that correspond to the cells in column H:H that are equal to "B" for each of the merchants in the merchant list and if column H:H is equal to "A" then skip. The PDF files printed for each merchant should be saved in the Settlements folder path.
VBA Code:
Sub Button1_Click()
Dim ws As Worksheet
Dim wsNeonpay As Worksheet
Dim wsCandlestick As Worksheet
Dim wsData As Worksheet
Dim pdfName As String
Dim pdfPath As String
Dim rng As Range
Dim merchantCell As Range
Dim merchantList As Range
Dim startDate As Date
Dim endDate As Date
Dim active As Date
' Define the Settlements folder path
Dim settlementsFolderPath As String
settlementsFolderPath = ThisWorkbook.Path & "/Settlements"
' Check if the Settlements folder exists, and create it if it doesn't
If Len(Dir(settlementsFolderPath, vbDirectory)) = 0 Then
On Error Resume Next
MkDir settlementsFolderPath
If Err.Number <> 0 Then
MsgBox "Error creating folder: " & Err.Description
' Handle error or exit
End If
On Error GoTo 0
End If
' Set references to your worksheets
Set ws = ThisWorkbook.Sheets("Settlement Template") ' Modify with actual sheet name
Set wsData = ThisWorkbook.Sheets("Fees") ' Sheet with merchant data
Set wsNeonpay = ThisWorkbook.Sheets("Neonpay (2)")
On Error Resume Next
Set wsCandlestick = ThisWorkbook.Sheets("candlestick")
On Error GoTo 0
If wsCandlestick Is Nothing Then
MsgBox "The sheet 'candlestick' was not found."
Exit Sub
End If
' Clear any existing filters on Neonpay (2)
If wsNeonpay.AutoFilterMode Then wsNeonpay.AutoFilterMode = False
wsNeonpay.Range("S1:S" & wsNeonpay.Cells(wsNeonpay.Rows.Count, "S").End(xlUp).Row).AutoFilter Field:=1
' Apply AutoFilter to the entire Date column, without specific criteria
Dim dateColumn As Long
dateColumn = 19 ' Assuming the date is in Column S(19)
Dim neonpayLastRow As Long
neonpayLastRow = wsNeonpay.Cells(wsNeonpay.Rows.Count, dateColumn).End(xlUp).Row
wsNeonpay.Range("S1:S" & neonpayLastRow).AutoFilter Field:=1
active = ws.Cells(20, 19).Value
' Define the print area (modify if necessary)
Set rng = ws.Range("A1:I33")
ws.PageSetup.PrintArea = rng.Address
' Define the range with the list of merchants
Set merchantList = wsData.Range("A2:A350") ' Modify to your range
' Reference the workbook and worksheet for the settlement summary
Dim wbSummary As Workbook
Dim wsSummary As Worksheet
On Error Resume Next
Set wbSummary = Workbooks("SettlementSummary")
On Error GoTo 0
Dim wasWorkbookOpened As Boolean
If wbSummary Is Nothing Then
' Open the workbook
Set wbSummary = Workbooks.Open(FileName:=ThisWorkbook.Path & "/SettlementSummary.xlsx")
wasWorkbookOpened = True
End If
Set wsSummary = wbSummary.Sheets("Settlement Summary")
SortCandlestickData
Dim nextRow As Long
' Loop through each merchant
For Each merchantCell In merchantList
wsNeonpay.Range("A2:S" & wsNeonpay.Cells(wsNeonpay.Rows.Count, "S").End(xlUp).Row).AutoFilter Field:=19, Criteria1:="<>"
' Check if the cell is not empty
If Not IsEmpty(merchantCell.Value) Then
ws.Range("B5").Value = merchantCell.Value
ws.Range("P17").Value = wsData.Cells(merchantCell.Row, 16).Value
SortCandlestickData
If ws.Range("I30").Value <> 0 Then
startDate = active - (6 + wsData.Cells(merchantCell.Row, 16).Value)
endDate = active - wsData.Cells(merchantCell.Row, 16).Value
' Apply specific date filters for this merchant
Dim fullRange As Range
Dim lastRow As Long
lastRow = wsNeonpay.Cells(wsNeonpay.Rows.Count, "R").End(xlUp).Row
Set filterRange = wsNeonpay.Range("A2:S" & lastRow)
filterRange.AutoFilter Field:=19, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate
Dim cleanedMerchantValue As String
cleanedMerchantValue = Trim(Application.Clean(merchantCell.Value))
Dim merchantRow As Long
merchantRow = Application.WorksheetFunction.Match(cleanedMerchantValue, wsSummary.Columns("A:A"), 0)
' Populate values from 'Settlement Template' to the corresponding row
wsSummary.Cells(merchantRow, "B").Value = ws.Range("I13").Value ' Populate column B
wsSummary.Cells(merchantRow, "D").Value = ws.Range("I30").Value ' Populate column D
' Define PDF name and path (possibly unique for each merchant)
pdfName = ws.Range("B5").Value & "_SettlementReport_" & ws.Range("B6").Value & "(SettlementDate_" & ws.Range("B7").Value & ")" & ".pdf"
pdfPath = settlementsFolderPath & "/" & pdfName
' Export as PDF
ws.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfPath, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
wsNeonpay.AutoFilterMode = False
wsNeonpay.Range("A2:S" & wsNeonpay.Cells(wsNeonpay.Rows.Count, "S").End(xlUp).Row).AutoFilter Field:=19, Criteria1:="<>"
End If
Next merchantCell
End Sub
