Moving rows based on column values

lm8ub

New Member
Joined
Jul 18, 2011
Messages
6
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:
VBA Code:
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,157
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2011
Messages
6
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:
Q12001Q1
1.00​
2.00​
33.00​
34.00​
Q22001Q2
3.00​
4.00​
35.00​
36.00​
Q32001Q3
5.00​
6.00​
37.00​
38.00​
Q42001Q4
7.00​
8.00​
39.00​
40.00​
Q12002Q1
9.00​
10.00​
41.00​
42.00​
Q22002Q2
11.00​
12.00​
43.00​
44.00​
Q32002Q3
13.00​
14.00​
45.00​
46.00​
Q42002Q4
15.00​
16.00​
47.00​
48.00​
Q12003Q1
17.00​
18.00​
49.00​
50.00​
Q22003Q2
19.00​
20.00​
51.00​
52.00​
Q32003Q3
21.00​
22.00​
53.00​
54.00​
Q42003Q4
23.00​
24.00​
55.00​
56.00​
Q12004Q1
25.00​
26.00​
57.00​
58.00​
Q22004Q2
27.00​
28.00​
59.00​
60.00​
Q32004Q3
29.00​
30.00​
61.00​
62.00​
Q42004Q4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,157
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

lm8ub

New Member
Joined
Jul 18, 2011
Messages
6
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
 

Forum statistics

Threads
1,148,371
Messages
5,746,305
Members
424,006
Latest member
Metal_warrior

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
Top