How to move records from one folder (tab) to another

I want to write a macro to move certain records from one folder (tab)
to another. Here are the details:
I have a list of records in a folder named Official List. It's a list
that grows and shrinks in size every day. It extends from column A
through S. When a date is entered in column Q, it means that this
record can be moved to the other folder (named Deleted List), and
placed at the bottom of that list. I have named the header of column Q
"Taken_Date". (I prefer using named ranges, rather than row numbers or
column letters in case I change the design of my spreadsheets)
The range name of the column heading at the top left of the Deleted
List is called "Moved_To"
I hope this is clear enough. As always, I appreciate the help provided
from this group.

ALSO, I've always liked using the If-Then function in my spreadsheets.
But, I'm finding it hard to understand how to use this in VBA. I have a
book by John Walkenback, Power Programing with VBA, but it just doesn't
seem to address this powerful function enough. I've always thought the
If-Then function as a versatile tool, but maybe I'm bypassing better
functions to incorporate into my macros. I've been writing code just
for a few months, so if anyone has a suggestion on good resource
material, I would appreciate it.

The following assumes that by "folder" you mean worksheet. Paste to the
Official List worksheet code module. Minimal testing:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range, c As Range
Dim ws As Worksheet

If Target.Count > 1 Then Exit Sub
If Not IsDate(Target.Value) Then Exit Sub
If Not Target.Column = Range("Taken_Date").Column Then Exit Sub
Set c = Cells(Target.Row, 1)
Set r1 = c.Resize(1, 19)
Set ws = Sheets("Delete List")
Set c = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set r2 = c.Resize(1, 19)
r2.Value = r1.Value
r1.ClearContents
End Sub

Regards,
Greg


"excelnut1954" wrote:

> I want to write a macro to move certain records from one folder (tab)
> to another. Here are the details:
> I have a list of records in a folder named Official List. It's a list
> that grows and shrinks in size every day. It extends from column A
> through S. When a date is entered in column Q, it means that this
> record can be moved to the other folder (named Deleted List), and
> placed at the bottom of that list. I have named the header of column Q
> "Taken_Date". (I prefer using named ranges, rather than row numbers or
> column letters in case I change the design of my spreadsheets)
> The range name of the column heading at the top left of the Deleted
> List is called "Moved_To"
> I hope this is clear enough. As always, I appreciate the help provided
> from this group.
>
> ALSO, I've always liked using the If-Then function in my spreadsheets.
> But, I'm finding it hard to understand how to use this in VBA. I have a
> book by John Walkenback, Power Programing with VBA, but it just doesn't
> seem to address this powerful function enough. I've always thought the
> If-Then function as a versatile tool, but maybe I'm bypassing better
> functions to incorporate into my macros. I've been writing code just
> for a few months, so if anyone has a suggestion on good resource
> material, I would appreciate it.
>
>

To answer the second part of your post, the VBA "IIf" function is effectively
the same as Excel's "If" worksheet function except that you have to set
something to the value returned by this function while Excel's version
returns the value to the cell instead. Example:

x = IIf(Range("A1") > 100, "Yes", "No")
MsgBox x

The statement IIf(Range("A1") > 100, "Yes", "No") by itself will return an
error.

I find the Select Case construct superior to either the If/Then/End If or
If/Then/ElseIf/Else/End If constructs so long as there is only one condition
being evaluated. Example:

Select Case Range("A1").Value
Case 1, 5, 9
x = True 'i.e. If A1 equals either 1, 5 or 9 then x = True
Case Is > 100
y = True 'i.e. If A1 > 100 then y = True
Case Else
x = False
y = False
End Select

On second read of your post, it appears that you may not be transfering the
data to column A in the Delete List worksheet but to whatever column the
Moved_To named range is in. Appended is a rewrite of the macro I gave you
previously that accounts for this.

Regards,
Greg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range, c As Range
Dim ws As Worksheet
Dim col As Long

If Target.Count > 1 Then Exit Sub
If Not IsDate(Target.Value) Then Exit Sub
col = Range("Taken_Date").Column
If Not Target.Column = col Then Exit Sub
Set c = Cells(Target.Row, 1)
Set r1 = c.Resize(1, 19)
Set ws = Sheets("Delete List")
col = ws.Range("Moved_To").Column
Set c = ws.Cells(Rows.Count, col).End(xlUp).Offset(1)
Set r2 = c.Resize(1, 19)
r2.Value = r1.Value
r1.ClearContents
End Sub