Thomas McLernon
New Member
- Joined
- Nov 22, 2021
- Messages
- 4
- Office Version
- 2021
- 2019
- Platform
- Windows
By finding bits of code and stringing them together I got this row copy between sheets on specified criteria to work, and meet the requirements. Source data is formulas, and it needs to copy static data. (non reactionary data)
This all works except it is intended to copy over and retain (when criteria met) records cumulatively over time. This string copies new records over the top of existing records. I need code that will enable it to copy to the next blank row. The extent of my VBA experience is by trial and error.
Sub Macro1()
Dim c As Range
Dim NextRow As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Set NextRow = Range("A" & Sheets("Archived Schedules").UsedRange.Rows.Count + 1)
Set Source = ActiveWorkbook.Worksheets("Absence Scheduling")
Set Target = ActiveWorkbook.Worksheets("Archived Schedules")
j = 9
For Each c In Source.Range("E9:E1000")
If c.Value = "Company Term" Or c.Value = "Schedule Change" Or c.Value = "Self Term" Or c.Value = "AWOL Term" Then
Source.Rows(c.Row).Copy Target.Rows(j)
Target.Rows(j).Value = c.EntireRow.Value
If IsEmpty(Target) Then
j = j + 1
End If
End If
Next c
End Sub
This all works except it is intended to copy over and retain (when criteria met) records cumulatively over time. This string copies new records over the top of existing records. I need code that will enable it to copy to the next blank row. The extent of my VBA experience is by trial and error.
Sub Macro1()
Dim c As Range
Dim NextRow As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Set NextRow = Range("A" & Sheets("Archived Schedules").UsedRange.Rows.Count + 1)
Set Source = ActiveWorkbook.Worksheets("Absence Scheduling")
Set Target = ActiveWorkbook.Worksheets("Archived Schedules")
j = 9
For Each c In Source.Range("E9:E1000")
If c.Value = "Company Term" Or c.Value = "Schedule Change" Or c.Value = "Self Term" Or c.Value = "AWOL Term" Then
Source.Rows(c.Row).Copy Target.Rows(j)
Target.Rows(j).Value = c.EntireRow.Value
If IsEmpty(Target) Then
j = j + 1
End If
End If
Next c
End Sub