Adapting Old VBA Code for PDF Printing

T

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
NumberStreetCode Monthly AMOUNT DUERent Collected to datePrint Late or EvictLease Start DateLate feeHelper for Late notices
Test 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
L100 Clinch Dr Columbia, TN 38401
Clinch Dr102CD NO PAY EVICT REPAIRS 102 Clinch Dr Columbia, TN 38401
Clinch Dr104CD104 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>
[TD="align: center"]1[/TD]

[TD="align: right"]January-18[/TD]
[TD="align: right"]February-18[/TD]
[TD="align: right"]March-18[/TD]
[TD="align: right"]April-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]June-18[/TD]
[TD="align: right"]July-18[/TD]
[TD="align: right"]August-18[/TD]
[TD="align: right"]September-18[/TD]
[TD="align: right"]October-18[/TD]
[TD="align: right"]November-18[/TD]
[TD="align: right"]December-18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]Clinch Dr[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]100CD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 8,800.00 [/TD]

[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]102[/TD]

[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 750.00 [/TD]
[TD="align: right"] $ 750.00 [/TD]
[TD="align: right"] $ 750.00 [/TD]
[TD="align: right"] $ 750.00 [/TD]
[TD="align: right"] $ 750.00 [/TD]

[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 6,150.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]104[/TD]

[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ 8,800.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Rent Rolls​



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

Excel 2010
ABCDEF
Date: December 6, 2018Code100CD
Rental PeriodRent Due
Late Fees
Total Rent Due

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]

[TD="align: right"]########[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]The premises referred to are commonly known as:[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]And 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:
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]Rent Paid[/TD]
[TD="align: center"][/TD]
[TD="align: center"] Balance [/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]January-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]February-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]March-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]April-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]June-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]July-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]August-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]September-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]October-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]November-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]December-18[/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ - [/TD]
[TD="align: center"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 800.00 [/TD]
[TD="align: center"][/TD]

</tbody>
Late Notice​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=B4+7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A7[/TH]
[TD="align: left"]=VLOOKUP(F4,'Rent Rolls'!C1:AA89,24,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A18[/TH]
[TD="align: left"]='Rent Rolls'!E1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B18[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C18[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,3,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A19[/TH]
[TD="align: left"]='Rent Rolls'!F1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B19[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C19[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,4,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A20[/TH]
[TD="align: left"]='Rent Rolls'!G1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B20[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C20[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,5,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A21[/TH]
[TD="align: left"]='Rent Rolls'!H1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B21[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C21[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,6,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A22[/TH]
[TD="align: left"]='Rent Rolls'!I1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B22[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C22[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,7,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A23[/TH]
[TD="align: left"]='Rent Rolls'!J1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B23[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C23[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,8,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A24[/TH]
[TD="align: left"]='Rent Rolls'!K1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B24[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C24[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,9,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A25[/TH]
[TD="align: left"]='Rent Rolls'!L1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B25[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C25[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,10,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A26[/TH]
[TD="align: left"]='Rent Rolls'!M1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B26[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C26[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,11,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A27[/TH]
[TD="align: left"]='Rent Rolls'!N1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B27[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C27[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,12,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A28[/TH]
[TD="align: left"]='Rent Rolls'!O1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B28[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C28[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,13,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A29[/TH]
[TD="align: left"]='Rent Rolls'!P1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B29[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C29[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:P,14,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E18[/TH]
[TD="align: left"]=B18-C18[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E19[/TH]
[TD="align: left"]=B19-C19[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E20[/TH]
[TD="align: left"]=B20-C20[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E21[/TH]
[TD="align: left"]=B21-C21[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E22[/TH]
[TD="align: left"]=B22-C22[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E23[/TH]
[TD="align: left"]=B23-C23[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E24[/TH]
[TD="align: left"]=B24-C24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E25[/TH]
[TD="align: left"]=B25-C25[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E26[/TH]
[TD="align: left"]=B26-C26[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E27[/TH]
[TD="align: left"]=B27-C27[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E28[/TH]
[TD="align: left"]=B28-C28[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E29[/TH]
[TD="align: left"]=B29-C29[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E30[/TH]
[TD="align: left"]=VLOOKUP($F$4,'Rent Rolls'!C:AA,18,FALSE)*(COUNTIF(E18:F29,">0"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E32[/TH]
[TD="align: left"]=SUM(E18:F30)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You must log in or register to reply here.

Similar threads

Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13
T
  • Question Question
Conditional Formating Multiple Variable
Replies
2
Views
705
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top