VBA N00b -- Need Help

KSLaborRelations

New Member
Joined
Jan 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

Hopefully I have come to the right location to obtain some excel assistance. I am completely new to VBA and macros. I have tried, without success, to establish a macro. I am trying to setup a macro that runs by looking at each row under column "I" to see if the date listed in the cell is older or past the current date of the macro being ran. If the date is past the current I want the macro to copy the entire row, paste it into a different sheet (same workbook) and then delete the row from the active worksheet.

My workbook is titled "Recall Database.xlsm" and it has 2 sheets. The first sheet where we keep the active data is titled "Active" and the sheet where I want the macro to paste too is titled "Purged". The title of the macro will be "RecallPurge"

Below is the top portion of the Active sheet, row 1 is headers. I want the macro, when ran, to look only look at the date listed in column "I" in all rows. Only this date, not others. If the date that is listed in the column I of that row is older or past the current date of when the macro is being ran, then I want the macro to copy the row, paste the row in the sheet titled "Purged" and then delete the row from the "Active" sheet.

Capture.PNG


Below is an example of a row (row 217) that would be copied, pasted into "Purged" and then removed/deleted from "Active"

Capture2.PNG


Can anyone assist me? I truly appreciate anyone taking the time to read this and will be forever grateful if anyone can assist me.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    26.5 KB · Views: 9

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hello KSLR,

Using the Autofilter should make it a pretty quick option for you:-

VBA Code:
Option Explicit
Sub Test()

        Dim wsA As Worksheet: Set wsA = Sheets("Active")
        Dim wsP As Worksheet: Set wsP = Sheets("Purged")

Application.ScreenUpdating = False

        With wsA.[A1].CurrentRegion
                .AutoFilter 9, "<" & [Today()]
                .Offset(1).EntireRow.Copy wsP.Range("A" & Rows.Count).End(3)(2)
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

The code will filter Column I for dates less than "today" and transfer the relevant rows of data to the Purged sheet then delete the relevant row from the Active sheet.

Assign the code to a button but please ensure that you test it in a copy of your actual workbook first.

I'm not too sure what you actually mean by this comment:-

Rich (BB code):
if the date listed in the cell is older or past the current date

This would suggest that all the rows would be deleted.

I hope that this helps.

Cheerio,
vcoolio.
 
Last edited:
Upvote 0
Try this:
When you say:
The first sheet where we keep the active data is titled "Active"
I assume you mean the sheet is named "Active"
And I assume you mean Date is less then the date the script is run.
VBA Code:
Sub Filter_Me_Please()
'Modified  1/2/2021  5:40:14 AM  EST
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
Dim s As Variant
c = 9 ' Column Number Modify this to your need
s = "<" & Date
lastrow = Sheets("Active").Cells(Rows.Count, c).End(xlUp).Row

With Sheets("Active").Cells(1, c).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Purge").Cells(2, 1)
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you both so much!! Last question, I forgot to mention each of the 2 sheets, Active & Purged, have a password for each sheet. What is the VBA code to slide in for each of the above and where?
 
Upvote 0
You said:
What is the VBA code to slide in for each of the above and where?
Slide in ??
What does Slide in mean?
You will have to unprotect the sheets first.
 
Upvote 0
Use
VBA Code:
Sub Test()

        Dim wsA As Worksheet: Set wsA = Sheets("Active")
        Dim wsP As Worksheet: Set wsP = Sheets("Purged")
wsA.Unprotect Password:="your password"
wsP.Unprotect Password:="your password"
Application.ScreenUpdating = False

        With wsA.[A1].CurrentRegion
                .AutoFilter 9, "<" & [Today()]
                .Offset(1).EntireRow.Copy wsP.Range("A" & Rows.Count).End(3)(2)
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

Application.ScreenUpdating = True
wsA.Protect Password:="your password"
wsP.Protect Password:="your password"

End Sub
 
Upvote 0
Putting your password into the script does compromise your password.
If a user that does not have access to the Password was able to read your script would now know the password. But:
That is a good point. There is only 1 user that will be accessing this and she can have the pw
 
Upvote 0
Either that or Password protect the VBA !
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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