Auto Copy based on criteria in colum

[J.W. Aldridge]

Hi,

I have a column of formulated data (results from looking up on another
sheet) in column B.
Based on the date value anyone puts in cell a1 this sheet...

I need a macro that would copy the results in row B, and paste values
in C.

(ex. If someone puts 9/2 in cell a1, the macro would find that date
value and copy the cells in column B based on matching that criteria
and paste in C.).

Cell A1= date

A B C
results from formula values copied
9/2 123 123
9/2 456 456
9/2 789 789
9/2 012 012
9/3 123
9/3 456
9/3 789
9/3 012

If there are any easier ideas, please let me know. thanx.
Thanx

 

[Guest]

Hi,

I think you don't need a macro to do this. What you can do is write a
VLOOKUP() in col B and make col C as '=B1' and so on.

By doing this the values will be automatically fetched in both the columns.

HTH,

 

[J.W. Aldridge]

Thanx.
That would work, however, the sheet from which column B is pulling its
data is subject to change. That's why i need to copy and paste values.
If leave a = formula in C, then when criteria for which the data in B
is no longer avaialbe, it will all dissappear.

I found the following code which bases it on two conditions, i only
need one. (which would be in cell A1). Please advise if i can alter
this code to refer to one condition/cell (A1).


Sub macro1()
Dim ws As Worksheet
Dim iA As Integer
Dim iB As Integer
Dim c As Range
Dim rng As Range


Set ws = Worksheets("Sheet1")
Set rng = ws.Range("C2:C16")
For Each c In rng
If c = "A" Then
iA = iA + 1
ws.Cells(iA, 5) = c.Offset(0, -2)
ws.Cells(iA, 6) = c.Offset(0, -1)
Else
iB = iB + 1
ws.Cells(iB, 8) = c.Offset(0, -2)
ws.Cells(iB, 9) = c.Offset(0, -1)
End If
Next c
End Sub