Adapting Old VBA Code for PDF Printing

treaves04

Board Regular
Joined
Jul 2, 2012
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Many years ago this forum helped me develop a macro / VBA that would run through a list and print the selected data into a form. I am trying to adapt this code to a new purpose and realizing I have forgot ALOT. This will be a long post but I hope it serves to provide all the data needed to help answer my question.

What I am wanting to do is to use a single "code" that calls all the data from one sheet to another. I have it working manually but adapting the VBA code to automate it is not coming along so well.

Here is the Data I am calling From called "Rent Rolls:

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1NumberStreetCode Monthly January-18February-18March-18April-18May-18June-18July-18August-18September-18October-18November-18December-18AMOUNT DUERent Collected to datePrint Late or EvictLease Start DateLate feeHelper for Late notices
2Test NumTest StTEST Test Month test JanTest febtest martest apriltest maytest junetest julytest augtest septtest octtest novtest dectest amt duetest rent collLTest Num Test St Columbia, TN 38401
3100Clinch Dr100CD $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ - $ 800.00 $ 8,800.00 L25100 Clinch Dr Columbia, TN 38401
4102Clinch Dr102CD $ 800.00 $ 750.00 $ 750.00 $ 750.00 $ 750.00 $ 750.00 NO PAY EVICT REPAIRS $ 800.00 $ 800.00 $ 800.00 $ - $ 800.00 $ 6,150.00 102 Clinch Dr Columbia, TN 38401
5104Clinch Dr104CD $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ 800.00 $ - $ 800.00 $ 8,800.00 104 Clinch Dr Columbia, TN 38401

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Rent Rolls



Here is the Sheet I am Calling to, call "Late Notice"

Excel 2010
ABCDEF
4Date: December 6, 2018########Code100CD
5
6The premises referred to are commonly known as:
70
8
9And all other tenants in possession of the hereinafter described premises:
PLEASE TAKE NOTICE that the rent is past due on said premises which you currently hold and occupy. Your rental account is delinquent in the amount itemized as follows:
10
11
12
13
14
15
16
17Rental PeriodRent DueRent Paid Balance
18January-18 $ 800.00 $ 800.00 $ -
19February-18 $ 800.00 $ 800.00 $ -
20March-18 $ 800.00 $ 800.00 $ -
21April-18 $ 800.00 $ 800.00 $ -
22May-18 $ 800.00 $ 800.00 $ -
23June-18 $ 800.00 $ 800.00 $ -
24July-18 $ 800.00 $ 800.00 $ -
25August-18 $ 800.00 $ 800.00 $ -
26September-18 $ 800.00 $ 800.00 $ -
27October-18 $ 800.00 $ 800.00 $ -
28November-18 $ 800.00 $ 800.00 $ -
29December-18 $ 800.00 $ - $ 800.00
30Late Fees $ -
31
32Total Rent Due $ 800.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Late Notice

Worksheet Formulas
CellFormula
B4=TODAY()
C4=B4+7
A7=VLOOKUP(F4,'Rent Rolls'!C1:AA89,24,FALSE)
A18='Rent Rolls'!E1
B18=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C18=VLOOKUP($F$4,'Rent Rolls'!C:P,3,FALSE)
A19='Rent Rolls'!F1
B19=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C19=VLOOKUP($F$4,'Rent Rolls'!C:P,4,FALSE)
A20='Rent Rolls'!G1
B20=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C20=VLOOKUP($F$4,'Rent Rolls'!C:P,5,FALSE)
A21='Rent Rolls'!H1
B21=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C21=VLOOKUP($F$4,'Rent Rolls'!C:P,6,FALSE)
A22='Rent Rolls'!I1
B22=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C22=VLOOKUP($F$4,'Rent Rolls'!C:P,7,FALSE)
A23='Rent Rolls'!J1
B23=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C23=VLOOKUP($F$4,'Rent Rolls'!C:P,8,FALSE)
A24='Rent Rolls'!K1
B24=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C24=VLOOKUP($F$4,'Rent Rolls'!C:P,9,FALSE)
A25='Rent Rolls'!L1
B25=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C25=VLOOKUP($F$4,'Rent Rolls'!C:P,10,FALSE)
A26='Rent Rolls'!M1
B26=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C26=VLOOKUP($F$4,'Rent Rolls'!C:P,11,FALSE)
A27='Rent Rolls'!N1
B27=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C27=VLOOKUP($F$4,'Rent Rolls'!C:P,12,FALSE)
A28='Rent Rolls'!O1
B28=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C28=VLOOKUP($F$4,'Rent Rolls'!C:P,13,FALSE)
A29='Rent Rolls'!P1
B29=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)
C29=VLOOKUP($F$4,'Rent Rolls'!C:P,14,FALSE)
E18=B18-C18
E19=B19-C19
E20=B20-C20
E21=B21-C21
E22=B22-C22
E23=B23-C23
E24=B24-C24
E25=B25-C25
E26=B26-C26
E27=B27-C27
E28=B28-C28
E29=B29-C29
E30=VLOOKUP($F$4,'Rent Rolls'!C:AA,18,FALSE)*(COUNTIF(E18:F29,">0"))
E32=SUM(E18:F30)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



And Finally, This is the Code I am trying to adapt: Note that i am not opposed to scraping this all together if there is a better way:

Code:
Sub Late_Notice_Print()


Dim EmptyRow&, cell As Range
Dim Rng As Range
Dim nRng As Range
Dim Txt As String
Dim Dn As Range
Dim Rw As Range
Dim FdSt As String
Dim SeSt As String
Set Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp)).Resize(, 8)
    FdSt = Join(Application.Index(Rng(Rng.Count - 7).Resize(, 8), 1, Array(2, 3, 6)))
For Each Dn In Rng.Rows
    For Each Rw In Dn.Rows
        SeSt = Join(Application.Index(Rw, 1, Array(2, 3, 6)))
        If FdSt = SeSt And Not Rw.Address = Rng(Rng.Count - 7).Resize(, 8).Address Then
            If nRng Is Nothing Then
                Set nRng = Union(Rng(Rng.Count - 7).Resize(, 8), Rw)
           Else
                Set nRng = Union(nRng, Rw)
            End If
        End If
   Next Rw
Next Dn
'Duplicate Check Function No needed For Late Notices
'If Not nRng Is Nothing Then
'    Txt = "Duplicates" & Chr(10)
'    For Each Dn In nRng.Areas
'        For Each Rw In Dn.Rows
'            Txt = Txt & "Row " & Rw.Row & Chr(10) & Join(Application.Transpose(Application.Transpose(Rw.Value))) & Chr(10)
'        Next Rw
'    Next Dn
'MsgBox Txt
'Else
'  MsgBox "No Duplicates Found"
'End If
    
    Dim rngLoopRange As Range
'Add Name of Sheet to look for Yes or no to Print
    With Sheets("Rent Rolls")
'Name the Column to look in
        For Each rngLoopRange In .Range("S2:S" & .Range("S" & Rows.Count).End(xlUp).Row)
'Letter to lookfor to trigger printing
            If rngLoopRange = "L" Then
'Where to put the code that call the data to sheet to print
                Sheets("Late Notice").Range("F4") = rngLoopRange.Offset(0, 2)
'What to print to
'                Application.Run "Adobe PDF"
               ActiveSheet.ExportAsFixedFormat _
                    Type:=xlTypePDF
                rngLoopRange = Date
            
            End If


        Next rngLoopRange
        
    End With


'With Sheets("Rent Rolls")
'
'        For Each rngLoopRange In .Range("S2:S" & .Range("S" & Rows.Count).End(xlUp).Row)
'
'            If rngLoopRange = "E" Then
'
'                Sheets("Eviction").Range("F4") = rngLoopRange.Offset(0, 2)
'
'                Application.Run "pdfsave"
'
'                rngLoopRange = Date
'
'            End If
'
'        Next rngLoopRange
        
'    End With
'EmptyRow = WorksheetFunction.CountA(Range("C:C")) + 1
'Set cell = ActiveSheet.Cells(EmptyRow, 3)
'cell.Value = cell.Offset(-1, 0).Value + 1   ' new PO number




End Sub

I have tried to 'comment' out what I believe is no longer relevant or needed and add notes where I think I understand what I am doing, but after too many years away from the keyboard, I may be entirely wrong. Ultimately, I want the macro to look at Rent Rolls: Column S for a "L" (eventually I would like to add an "E" for another sheet for evictions) place the given "Code" from Rent Rolls: Column C to sheet Late Notice F4. That will trigger everything to be called to sheet that I want on there. I then want the late notice Created as a PDF. I can manually name them right now if needed but eventually I will make the code name it (done it before, just going to take some time to remember all this). Thank you for all the help, this is truely the best excel forum in the world.
 
Last edited by a moderator:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,147,748
Messages
5,742,976
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top