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
<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>
Here is the Sheet I am Calling to, call "Late Notice"
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><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:
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.
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
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Number | Street | Code | Monthly | January-18 | February-18 | March-18 | April-18 | May-18 | June-18 | July-18 | August-18 | September-18 | October-18 | November-18 | December-18 | AMOUNT DUE | Rent Collected to date | Print Late or Evict | Lease Start Date | Late fee | Helper for Late notices | |||||
| 2 | Test Num | Test St | TEST | Test Month | test Jan | Test feb | test mar | test april | test may | test june | test july | test aug | test sept | test oct | test nov | test dec | test amt due | test rent coll | L | Test Num Test St Columbia, TN 38401 | |||||||
| 3 | 100 | Clinch Dr | 100CD | $ 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 | L | 25 | 100 Clinch Dr Columbia, TN 38401 | ||||||
| 4 | 102 | Clinch Dr | 102CD | $ 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 | ||||||||
| 5 | 104 | Clinch Dr | 104CD | $ 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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 4 | Date: | December 6, 2018 | ######## | Code | 100CD | |
| 5 | ||||||
| 6 | The premises referred to are commonly known as: | |||||
| 7 | 0 | |||||
| 8 | ||||||
| 9 | 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: | |||||
| 10 | ||||||
| 11 | ||||||
| 12 | ||||||
| 13 | ||||||
| 14 | ||||||
| 15 | ||||||
| 16 | ||||||
| 17 | Rental Period | Rent Due | Rent Paid | Balance | ||
| 18 | January-18 | $ 800.00 | $ 800.00 | $ - | ||
| 19 | February-18 | $ 800.00 | $ 800.00 | $ - | ||
| 20 | March-18 | $ 800.00 | $ 800.00 | $ - | ||
| 21 | April-18 | $ 800.00 | $ 800.00 | $ - | ||
| 22 | May-18 | $ 800.00 | $ 800.00 | $ - | ||
| 23 | June-18 | $ 800.00 | $ 800.00 | $ - | ||
| 24 | July-18 | $ 800.00 | $ 800.00 | $ - | ||
| 25 | August-18 | $ 800.00 | $ 800.00 | $ - | ||
| 26 | September-18 | $ 800.00 | $ 800.00 | $ - | ||
| 27 | October-18 | $ 800.00 | $ 800.00 | $ - | ||
| 28 | November-18 | $ 800.00 | $ 800.00 | $ - | ||
| 29 | December-18 | $ 800.00 | $ - | $ 800.00 | ||
| 30 | Late Fees | $ - | ||||
| 31 | ||||||
| 32 | Total Rent Due | $ 800.00 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Late Notice
Worksheet Formulas
<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 SubI 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:
