Solved: Copy data from one Worksheet to another based on Flag

[akartis]

Hello everyone,

I am extremely new and have searched with no solution to my situation. Here goes.

I have two worksheets within a workbook. They are labeled as test1 and test2.

test1 contains data that is updated daily by and individual. The data is broken down by column name. (ie, name, address, phone and a date colmn.)

What I would like to do is at the end of every week take any data that has a date in the date column and copy it from test1 to test2.

If anyone could possibly help with this I would greatly appreciate it.

Thank you.

[fb7894]

I would write a macro that identifies what to copy over with a formula.
So, in your first non-used column (let's assume column Z), put in a formula =if(D1>"12/30/2008","COPY",""). This assumes column D is your date column.

Then you can do a select special cells on column Z. Select - Special Cells - Non Blank. Copy the entire row and paste into Worksheet("Test2").

[akartis]

Thank you for the reply. Is there a way to automate the whole thing. Such as a macro that will copy from test1 and then paste to test2?

[fb7894]

[vba]
Sub CopyStuff
With WS.Range("Z2:Z" & WS.UsedRange.Rows.Count)
.Formula = "=If(D2>""12/30/2008"",""COPY"","""")
.Value = .Value
On Error GoTo Handler
.SpecialCells(xlCellTypeConstants).EntireRow.Copy Destination:=WS2.Range("A2")
.ClearContents
End With
On Error GoTo 0
Exit Sub
End Sub
[/vba]

[lucas]

Another way:

[VBA]
Option Explicit
Sub Copy()
Dim cl As Range
Dim ws As Worksheet
Set ws = Worksheets("Test1")
With ws

For Each cl In .Range("Z2:Z" & .Range("Z65536").End(xlUp).Row)
If cl.Value <> "" Then
cl.EntireRow.Copy Worksheets("Test2").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next cl
End With
End Sub
[/VBA]

[hardlife]

Thank you, for this example, Pavel