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:
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!
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