Hello, perhaps a simple problem...but...:-(

I've got four columns (A B C D) of data in sheet 1. In columns B there are
increasing value (integer 1,2,3,4,5...200)

In sheet one, in cell E1 there is number (that comes from other data), for
example "78"

Now, in sheet 2 I need to do sthg as "copy & paste" columns from A to D of
sheet1 but only if B values are <=E1....

How can I do this in Vba...??
Actually I copy the A1:D1 range and, by using an if statement (if B1<=$E1$1)
autofill down columns...but I can't "stop" automatically the autofill
function....

If possible I wish to use a "for" routine (so that I can understand to solve
other similar problems by myself...)

Thanks in advance...!

Hi,

Sub CopyData()
Dim Rw, V, Rnge
V = Range("E1").Value
Rw = 0
Do
Rw = Rw + 1
Loop Until Cells(Rw, 2) = V
Rnge = "A1:D" & Rw
Range(Rnge).Copy Sheets("Sheet2").Range("A1")
End Sub

The value in E1 is stored in V. The DO loop goes down the rows looking for V
in column 2 (B)
It adds Rw to the end of Rnge, copies the range to sheet 2, A!.

Don

"uriel78" wrote in message
...
Hello, perhaps a simple problem...but...:-(

I've got four columns (A B C D) of data in sheet 1. In columns B there are
increasing value (integer 1,2,3,4,5...200)

In sheet one, in cell E1 there is number (that comes from other data), for
example "78"

Now, in sheet 2 I need to do sthg as "copy & paste" columns from A to D of
sheet1 but only if B values are <=E1....

How can I do this in Vba...??
Actually I copy the A1:D1 range and, by using an if statement (if
B1<=$E1$1)
autofill down columns...but I can't "stop" automatically the autofill
function....

If possible I wish to use a "for" routine (so that I can understand to
solve
other similar problems by myself...)

Thanks in advance...!

Thank you for your help...it gives an error 1004 at line "Loop Until
Cells(Rw, 3) = V"...I really don't know why....:-(



"Don Lloyd" ha scritto nel messaggio
...
Hi,

Sub CopyData()
Dim Rw, V, Rnge
V = Range("E1").Value
Rw = 0
Do
Rw = Rw + 1
Loop Until Cells(Rw, 2) = V
Rnge = "A1:D" & Rw
Range(Rnge).Copy Sheets("Sheet2").Range("A1")
End Sub

The value in E1 is stored in V. The DO loop goes down the rows looking for
V
in column 2 (B)
It adds Rw to the end of Rnge, copies the range to sheet 2, A!.

Don

Check:

Works OK for me.
Seems like a syntax error. Try copying and pasting the code direct from the
post into the module.
Also ensure that you're on Sheet1 when using the code.
It would also be a good idea to ensure that column B contains the value in
E1.

Don


"uriel78" wrote in message
...
Thank you for your help...it gives an error 1004 at line "Loop Until
Cells(Rw, 3) = V"...I really don't know why....:-(



"Don Lloyd" ha scritto nel messaggio
...
Hi,

Sub CopyData()
Dim Rw, V, Rnge
V = Range("E1").Value
Rw = 0
Do
Rw = Rw + 1
Loop Until Cells(Rw, 2) = V
Rnge = "A1:D" & Rw
Range(Rnge).Copy Sheets("Sheet2").Range("A1")
End Sub

The value in E1 is stored in V. The DO loop goes down the rows looking
for
V
in column 2 (B)
It adds Rw to the end of Rnge, copies the range to sheet 2, A!.

Don

Ok, now it works ...!!! I didn't understand I need to be on sheet1 before
running the macro :-)

....and just to finish...

now I've got values in columns A:D of sheet2...

in sheet 3 I've got data like in sheet1

is it possible to do the same operation and putting values coming from
sheet3 starting from the first free row of sheet2??

Example: after copy from sheet1, in sheet2 there are values in A1:D15...and
i wish to put data from sheet3 starting from A16...

"Don Lloyd" ha scritto nel messaggio
...
Check:

Works OK for me.
Seems like a syntax error. Try copying and pasting the code direct from
the
post into the module.
Also ensure that you're on Sheet1 when using the code.
It would also be a good idea to ensure that column B contains the value in
E1.

As the matter of fact I realized that often comun B does not contain value
in E1...
Column B is made of increasing but not filled values (10,12,13,18,19,23 in
column)...I try to substitute

Loop Until Cells(Rw, 2) = V

with

Loop Until Cells(Rw, 2) < V , but with this statement I get back only one
rows...maybe it's due to the fact of not filled number in column B?