Transfer rows between worksheets when condition is met.

[Thomas McLernon]

I am not a competent user of VBA, but I found this code that sort of matches what I want to achieve. I need to remove records from an employee scheduling sheet (when a before year-end "end-date" is inserted in a cell) Leaving only current work schedules on the scheduling sheet, and archiving past schedules to a different sheet. When I messed with the original code, on one attempt it transferred the sheet layout over, but not any data.

Thanks,
Tom McLernon

Option Explicit


Sub Copy_n_Paste()
On Error Resume Next


Dim srchtrm As String
Dim rng As Range, destRow As Long
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim c As Range
Dim i As Integer
Dim Today As Date

With Application
.ScreenUpdating = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

Set shtSrc = Sheets("Assign Employees to Schedules") 'source sheet
Set shtDest = Sheets("Schedules Ended") 'destination sheet
destRow = 2 'start copying to this row


'don't scan the entire column...
Set rng = Application.Intersect(shtSrc.Range("D:D"), shtSrc.UsedRange)


For Each c In rng.Cells
If c.Value = "Date" Then

c.EntireRow.Copy shtDest.Cells(destRow, 1)

destRow = destRow + 1


End If
Next

With Application
.ScreenUpdating = True
.DisplayStatusBar = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

Application.CutCopyMode = False
Sheets("Assign Employees to Schedules").Range("D1").Select


End Sub

 

[My Aswer Is This]

Would you please explain in specific detail what you attempting to do.

Trying to read a script that does not work and modify it so it does work is not easy for me to do.
Please include all sheet names and specific details. Thanks

 

[Thomas McLernon]

Would you please explain in specific detail what you attempting to do.

Trying to read a script that does not work and modify it so it does work is not easy for me to do.
Please include all sheet names and specific details. Thanks

That illustration code copies rows over to another sheet when specified conditions (more than one) in the source sheet are met. I only need one condition in the cells of one column to be met, and when it occurs copy that row over to another sheet.
The illustrated code worked one time but it copied the whole sheet format over, without bringing the data. I suppose in simplest terms: When a condition exists in Source Sheet (Column D) then copy that row over to Destination Sheet, starting at row 9 etc. (Operated from an execute button)
Thanks

 

[My Aswer Is This]

If you notice I said:
Would you please explain in specific details
But you said:
"copies rows over to another sheet"
What is the other sheets Name?

Then you said:
"When specified conditions (more than one) in the source sheet are met"
What are the conditions?

See what I'm saying we need exact specific details

 

[Thomas McLernon]

If you notice I said:
Would you please explain in specific details
But you said:
"copies rows over to another sheet"
What is the other sheets Name?

Then you said:
"When specified conditions (more than one) in the source sheet are met"
What are the conditions?

See what I'm saying we need exact specific details

The source Sheet is named "Absence Scheduling", the destination sheet is named "Archived Schedules", the triggering data is in column "E" of the source (Absence Scheduling) sheet. The triggering data can be 4 text expressions (Company Termination, Self Termination, Work Schedule Change, AWOL Termination)
I have the Macro "sort of working" but I am not experienced much with the foibles of VBA, from some of the results that I am getting I assume returns are (column position number) instead of hard position references (B1, B2, E1 etc.) (like VLOOKUP only worse) because when I hide columns in the source sheet, although the destination sheet is identical (column letters and headings) to the source sheet, the transferred data (in the destination sheet) gets misaligned if columns in the source sheet are hidden.
Thanks,
Tom McLernon