Moving rows based on column values

[lm8ub]

Hello, all:

I am picking up on a topic which I first found through another thread on here from when it was discontinued some years ago because I found it via a Google search for a problem I am having (and, more recently, here). I know Excel VBA is ancient now, but Excel is currently my bread n' butter and I need VBA for the particular task at hand. I need to move every fourth row in the Excel sheet to another sheet. Basically, every fourth row has the values for a particular calendar quarter. So, in my case, I am first trying to move Quarter 1 ("Q1") rows to a different sheet. Here is the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Q1").Cells(Rows.Count, "B").End(xlUp).Row + 1

If Target.Value = "Q1" Then Rows(Target.Row).Copy Destination:=Sheets("Q1").Rows(Lastrow)
Rows(Target.Row).Delete
End If

End Sub
Sub Macro2()
Call Worksheet_Change
'
' Macro2 Macro
'
'
End Sub

I wasn't able to use the Private Subroutine without calling it in a regular subroutine (because I didn't see it when I clicked on the "View Macros" option). However, when I try to run it, I get a compile error saying "Argument not optional" with the "Call Worksheet_Change" highlighted. Any tips would be greatly appreciated. Thank you in advance!

 

[Joe4]

You don't call "Worksheet_Change" event procedures.
They are automated procedures that run when some "event" happens. In "Worksheet_Change", that event is the manual update of some cell in a pre-defined range.
Since they run automatically, there should never be any need to call them from another macro.

If you have code that you may want to run automatically, but you may also want to be able to call it manually (from the menu, a button, a keyboard shortcut, etc), then it is best to move that code to its own separate procedure that you can call manually, or call it from the Worksheet_Change event procedure.

If you truly need to move every 4th row, that sounds like a manual thing to me, and I question why you would even try to use a Worksheet_Change event procedure. It seems to me that you only want to call this code manually and not automatically.

If that is the case, it would be best to place this in your own procedure in a General module.
See: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

Note that you will need to change your code a bit, because manual code does not have a "Target" range, by default.

If you need help crafting this code, please provide us with more details, such as which rows you are starting from, and which columns you are copying over.
Providing sample data and expected output may be helpful in this endeavor.

 

[My Aswer Is This]

The code your showing was written by me several years ago.
So why are you trying to run one code and then trying to call another code?
Tell me what your trying to do.

 

[lm8ub]

You don't call "Worksheet_Change" event procedures.
They are automated procedures that run when some "event" happens. In "Worksheet_Change", that event is the manual update of some cell in a pre-defined range.
Since they run automatically, there should never be any need to call them from another macro.

If you have code that you may want to run automatically, but you may also want to be able to call it manually (from the menu, a button, a keyboard shortcut, etc), then it is best to move that code to its own separate procedure that you can call manually, or call it from the Worksheet_Change event procedure.

If you truly need to move every 4th row, that sounds like a manual thing to me, and I question why you would even try to use a Worksheet_Change event procedure. It seems to me that you only want to call this code manually and not automatically.

If that is the case, it would be best to place this in your own procedure in a General module.
See: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

Note that you will need to change your code a bit, because manual code does not have a "Target" range, by default.

If you need help crafting this code, please provide us with more details, such as which rows you are starting from, and which columns you are copying over.
Providing sample data and expected output may be helpful in this endeavor.

Basically, I need to rearrange the rows so that all data pertaining to a single quarter are together. The data look something like this:

Q1	2001Q1	1.00​	2.00​	33.00​	34.00​
Q2	2001Q2	3.00​	4.00​	35.00​	36.00​
Q3	2001Q3	5.00​	6.00​	37.00​	38.00​
Q4	2001Q4	7.00​	8.00​	39.00​	40.00​
Q1	2002Q1	9.00​	10.00​	41.00​	42.00​
Q2	2002Q2	11.00​	12.00​	43.00​	44.00​
Q3	2002Q3	13.00​	14.00​	45.00​	46.00​
Q4	2002Q4	15.00​	16.00​	47.00​	48.00​
Q1	2003Q1	17.00​	18.00​	49.00​	50.00​
Q2	2003Q2	19.00​	20.00​	51.00​	52.00​
Q3	2003Q3	21.00​	22.00​	53.00​	54.00​
Q4	2003Q4	23.00​	24.00​	55.00​	56.00​
Q1	2004Q1	25.00​	26.00​	57.00​	58.00​
Q2	2004Q2	27.00​	28.00​	59.00​	60.00​
Q3	2004Q3	29.00​	30.00​	61.00​	62.00​
Q4	2004Q4	31.00​	32.00​	63.00​	64.00 ​

I realized just now that I can use the Excel filter function to that end, so moderators could feel free to close this topic if desired. But there may be a scenario in which the solution cannot use a filter. That scenario hasn't happened yet for me.

Thanks

 

[Joe4]

Why not just sort your data?

You can sort by multiple fields, so you can use Field1 as your primary sort field, and Field2 as your secondary sort field.

If Filters do what you need, then you can just use that option too.

 

[My Aswer Is This]

Why not just sort your data?

My thought's exactly use Excel built in sort command.

 

[lm8ub]

Why not just sort your data?

You can sort by multiple fields, so you can use Field1 as your primary sort field, and Field2 as your secondary sort field.

If Filters do what you need, then you can just use that option too.

Yes, I can sort, too. It didn't occur to me. Thanks