macro help

[Brian]

I have a workbook that has seven worksheets "open", "closed", "area1",
"area2", "area3", "area4", "area5". On worksheet "open" each row will have a
column range b2,b2000 labled "area" that I will be putting in one of the
five areas (1-5). The "open" worksheet will also have another column range
i2,i2000 listed as "active" which will have either a "y" or an "n".

So this is what I need.

1. I need the entire row to copy and paste over to the corresponding
worksheet listed in b2,b2000 when I tab out of the "area" column (column b)
after entering in the appropriate erea information in the "area" column.
2. When we put a "n" in the "active" collum we need the information to cut
and paste into the "closed" worksheet. This should in turn cause the
information to removed from the corresponding "area" (1-5) worksheets.
3. In the "closed" worksheet if we put a "y" in the "active" cloumn it
should cut and paste the row information back into the "open" worksheet.

 

[Greg Wilson]

A suggestion is that you rig the cells in the "Active"
columns (column I) of both the Open and Closed worksheets
to behave like check boxes intstead of relying on the user
to input either "y" or "n". If interested I can advise.

Did you really mean to cut the contents of the ENTIRE row?
This also removes the "y" and "n" values in column I. This
doesn't seem to me to be appropriate.

The following is my interpretation of your request:-

**** Step 1 *****
Paste the following to the "Open" worksheet code module:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks1 As Worksheet, wks2 As Worksheet
Dim Val As Integer
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Val = Target.Value
If IsNumeric(Val) And Val < 6 Then
Set wks1 = Sheets("Area" & Val)
Target.EntireRow.Copy wks1.Range("A" & Target.Row)
End If
ElseIf Not Intersect(Target, Range("I:I")) Is Nothing Then
If Trim(LCase(Target.Value)) = "n" Then
Val = Target(1, -6).Value
If IsNumeric(Val) And Val < 6 Then
Set wks1 = Sheets("Area" & Val)
Set wks2 = Sheets("Closed")
wks1.Range("A" & Target.Row).EntireRow.ClearContents
Target.EntireRow.Cut wks2.Range("A" & Target.Row)
End If
End If
End If
End Sub

***** Step 2 *****
Paste the following to the "Closed" worksheet code module:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("I:I")) Is Nothing Then
If Trim(LCase(Target.Value)) = "y" Then
Set wks = Sheets("Open")
Target.EntireRow.Cut wks.Range("A" & Target.Row)
End If
End If
End Sub

Minimal testing. Hope it works.

Regards,
Greg

-----Original Message-----
I have a workbook that has seven

worksheets "open", "closed", "area1",

 

[Brian]

I am interested and I do need to move the entire row if it is possible. I
will try the below sugestion and am eager to hear more about what you
mentioned.

Thank you for your help. I have a sample of the execl if you would like to
look it over. If that is possible I do not know message board editcate or
limitations..