Need help tracking and deleting ranges copied to another sheet in different locations

I have many grouped lists of Equipment in a worksheet that I would like to copy over to another worksheet to be sent out to a client. I have a macro set up such that once a check box next to the specific list is checked it will copy that list and insert it at the bottom of the worksheet to be presented to the client. I need the macro to also delete that list from the client's worksheet once the check box is unchecked. The issue I'm having is that by placing a list underneath the last row of data on the sheet, I then need to track it and delete it instead of just deleting the same range of cells each time.

Here's my code thus far. I would be happy to give more details if you need them.

Please Login or Register  to view this content.

Thanks in advance for any help you can provide.

PHP Code: 

Option Explicit
Sub ACI_Silo()
 Dim lRealLastRow As Long, lRealLastColumn As Long
 Dim Sht As Worksheet, Sh As Worksheet          '<=|'
 
'So you don"t see every step of the macro'
 Application.ScreenUpdating = False
'Copy and Paste function'
 Set Sht = ActiveSheet
 'Checks the cell the checkbox outputs a boolean to'
 Set Sh = Sheets("Sheet1")
 lRealLastRow = Sh.Cells.Find("*", Sh.[A], xlFormulas, , xlByRows, xlPrevious).Row
 lRealLastColumn = Sh.Cells.Find("*", Sh.[A1], xlFormulas, , xlByColumns, xlPrevious).Column
 
 If Range("J4").Value = True Then
    'Selects and copies the list from the original worksheet'
    Sheets("Packing List").Select
    Range("ACI_Silo").Select
    Selection.Copy Destination:=Sh.Cells(lRealLastRow + 1, 1)
    Sh.Select
'Deletes the list from the client sheet when the check box is unchecked... just a range now >.< '
 Else
    Sh.Range("A4:L18").Delete Shift:=xlUp
 End If
 
 Application.ScreenUpdating = True
 'Returns to the original sheet instead of where the list was pasted everytime
 Sht.Select
 Set Sht = Nothing
End Sub 


Hi there.

Perhaps too obvious (i.e. I'm probably missing something) - Is it possible to delete all lists from the Client's worksheet and then rebuild it from those lists that are still checked?

Cheers, Rob.

Thank you very much Rob!

It always helps to get a fresh set of eyes on something like this.

I have it doing just what you suggested. It deletes the sheet each instance and rebuilds it from only the lists selected. It's nice and clean now. Thank you very very much.

Here's the code if you want to look at what I did exactly.

Please Login or Register  to view this content.

No probs.

I agree - always good to have that second set of eyes, bounce a few ideas around, etc.

Cheers, Rob.