autofill or copy & paste "till a value"

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> ha scritto nel messaggio
> news:[email protected]...
>> 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" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> 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?