VBA Code Help. Need to cut/paste rows to different sheets based on different criteria & sort by date

[VBAN0oB]

Hello! I have a workbook with 12 sheets (named each month fully spelled out) and I need to move rows from each monthly sheet as follows:

If Column A reflects the word Pipeline I need the row to cut/paste to the Pipeline worksheet (in the same workbook)
If Column K reflects a value greater than $0 I need the row to cut/paste to the Bound worksheet (in the same workbook)

I need every worksheet within the workbook to sort by date which is in Column H in every sheet.

Currently, I have a code that moves the pipeline which is as follows:

Sub MoveBasedOnValueNovember()
'Created by Excel 10 Tutorial
Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long
A = Worksheets("November").UsedRange.Rows.Count
B = Worksheets("Pipeline").UsedRange.Rows.Count
If B = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Pipeline").UsedRange) = 0 Then B = 0
End If
Set xRg = Worksheets("November").Range("A1:A" & A)
On Error Resume Next
Application.ScreenUpdating = False
For A = 2 To xRg.Count
If CStr(xRg(A).Value) = "Y" Then
xRg(A).EntireRow.Copy Destination:=Worksheets("Pipeline").Range("A" & B + 1)
xRg(A).EntireRow.Delete
If CStr(xRg(A).Value) = "Y" Then
A = A - 1
End If
B = B + 1
End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
Selection.End (xlUp)
End Sub

I also have the following to call the Macros automatically

Private Sub Worksheet_Change(ByVal Target As Range)
' Run a macro that is located inside of a module
Call Worksheet_SelectionChangeNovember (this macro is moving the rows that have Pipeline in Column A)

Call MoveBasedOnValueNovember (this macro is currently moving the row column K is greater than $0 to the bottom of the monthly sheets. But I want it to now move to its own worksheet instead of to the bottom of each months.)

End Sub

Your assistance would be much appreciated.

 

[psgoldberg]

It seems that while you capture every cell change to execute the macro, the macro itself runs through all the records that are there which makes little sense to me. I therefore decided to guess the logic might be that you want to manually kick off the process periodically to move the records. That would make a lot of sense if in fact multiple people are in the spreadsheet at the same time and would see things disappear whenever they entered a cell.

In any event, I effectively did the same logic that you were attempting but used table references more completely which I prefer. I renamed tables and worksheets to be a bit clearer but nothing very magical. You would need to think through how you will handle all months with this structure (as in, there should be one sheet for input that considers all months...) And also limited error checking which your use case ignores.

Take a look here.