Hi All,
I want to transferring a data automatically by using
vba code.But need soeone to point me the right
direction.What I want to do is something simple.
I want the code to run if it meet some condition.

If a cell in column F:F have value >1 and not empty (""),
I want the code to collect the entire data on every cell
on the left to be copied and paste into a new workbook.

And how to handle if there are some cell that have
value >1.How to copy the recordset and paste it into
a new workbook.And maybe I also need a message
box to tell the user that there is no data will be copied
if there is no cell in range F:F that contains value >1.

New workbook maybe need a header something like:
" Wrong data list ".

I have read automation transfer data to workbook
from MS Help and Support,but since I'm a new user,
I'm still cannot modified the code as I need.I'm still
need more simpler sample and lesson.

Thank's and Rgds,

Shiro.

This is a very good problem for using the Macro Recorder. Say we have two
workbooks open Book1.xks and Book2.xls

In Sheet1 of Book1 we have:

qewr gd bd br t evv
-2 0 8 0 2 -8
5 -1 -2 0 -3 -10
6 1 8 2 8 4
-6 -4 8 6 4 -3
-9 2 -3 -10 3 2
7 6 5 -1 -2 1
9 -1 -7 10 4 -8
1 -9 -8 -7 -2 6
0 -10 9 7 -3 -5
4 -3 -3 7 -5 10
-9 -4 3 2 6 -6
-6 -6 -6 10 10 -6
-2 9 10 3 3 3
10 5 -5 6 -3 6
3 3 0 6 6 0
-10 -4 -7 0 -4 7
-9 10 4 10 -7 -3
1 -6 6 -8 -1 4
-9 6 -2 -9 -9 1
9 -5 -10 8 2 9
2 -9 -9 7 0 -8
8 1 -7 -10 -5 8
10 9 -7 7 5 5
0 1 4 7 5 -1
1 1 2 4 8 -8
8 -9 4 -2 -3 8
7 -9 -8 5 -2 8
1 -8 -6 -2 6 9
6 0 9 6 10 -10

To do the transfer manually, we click on F1 and:

Data > Filter > AutoFilter...
Custom > is greater than > 1

This displays:

qewr gd bd br t evv
6 1 8 2 8 4
-9 2 -3 -10 3 2
1 -9 -8 -7 -2 6
4 -3 -3 7 -5 10
-2 9 10 3 3 3
10 5 -5 6 -3 6
-10 -4 -7 0 -4 7
1 -6 6 -8 -1 4
9 -5 -10 8 2 9
8 1 -7 -10 -5 8
10 9 -7 7 5 5
8 -9 4 -2 -3 8
7 -9 -8 5 -2 8
1 -8 -6 -2 6 9

and we would manually copy/paste to Book2.xls

Let's do this with the Recorder turned on:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/17/2008 by James Ravenswood
'

'
Range("F1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=">1", Operator:=xlAnd
Range("A1:F29").Select
Selection.Copy
Windows("Book2").Activate
Range("A1").Select
ActiveSheet.Paste
End Sub

The only manual issue we face is correctly adjusting the F29.
--
Gary''s Student - gsnu200786


"shiro" wrote:

> Hi All,
> I want to transferring a data automatically by using
> vba code.But need soeone to point me the right
> direction.What I want to do is something simple.
> I want the code to run if it meet some condition.
>
> If a cell in column F:F have value >1 and not empty (""),
> I want the code to collect the entire data on every cell
> on the left to be copied and paste into a new workbook.
>
> And how to handle if there are some cell that have
> value >1.How to copy the recordset and paste it into
> a new workbook.And maybe I also need a message
> box to tell the user that there is no data will be copied
> if there is no cell in range F:F that contains value >1.
>
> New workbook maybe need a header something like:
> " Wrong data list ".
>
> I have read automation transfer data to workbook
> from MS Help and Support,but since I'm a new user,
> I'm still cannot modified the code as I need.I'm still
> need more simpler sample and lesson.
>
> Thank's and Rgds,
>
> Shiro.
>
>
>
>
>

Sorry Gary,
not so understand.Please more guidance.
What about the cell value in the left?


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:815C496A-B8AD-441D-98CF-59CD200B8CCB@microsoft.com...
> This is a very good problem for using the Macro Recorder. Say we have two
> workbooks open Book1.xks and Book2.xls
>
> In Sheet1 of Book1 we have:
>
> qewr gd bd br t evv
> -2 0 8 0 2 -8
> 5 -1 -2 0 -3 -10
> 6 1 8 2 8 4
> -6 -4 8 6 4 -3
> -9 2 -3 -10 3 2
> 7 6 5 -1 -2 1
> 9 -1 -7 10 4 -8
> 1 -9 -8 -7 -2 6
> 0 -10 9 7 -3 -5
> 4 -3 -3 7 -5 10
> -9 -4 3 2 6 -6
> -6 -6 -6 10 10 -6
> -2 9 10 3 3 3
> 10 5 -5 6 -3 6
> 3 3 0 6 6 0
> -10 -4 -7 0 -4 7
> -9 10 4 10 -7 -3
> 1 -6 6 -8 -1 4
> -9 6 -2 -9 -9 1
> 9 -5 -10 8 2 9
> 2 -9 -9 7 0 -8
> 8 1 -7 -10 -5 8
> 10 9 -7 7 5 5
> 0 1 4 7 5 -1
> 1 1 2 4 8 -8
> 8 -9 4 -2 -3 8
> 7 -9 -8 5 -2 8
> 1 -8 -6 -2 6 9
> 6 0 9 6 10 -10
>
> To do the transfer manually, we click on F1 and:
>
> Data > Filter > AutoFilter...
> Custom > is greater than > 1
>
> This displays:
>
> qewr gd bd br t evv
> 6 1 8 2 8 4
> -9 2 -3 -10 3 2
> 1 -9 -8 -7 -2 6
> 4 -3 -3 7 -5 10
> -2 9 10 3 3 3
> 10 5 -5 6 -3 6
> -10 -4 -7 0 -4 7
> 1 -6 6 -8 -1 4
> 9 -5 -10 8 2 9
> 8 1 -7 -10 -5 8
> 10 9 -7 7 5 5
> 8 -9 4 -2 -3 8
> 7 -9 -8 5 -2 8
> 1 -8 -6 -2 6 9
>
> and we would manually copy/paste to Book2.xls
>
> Let's do this with the Recorder turned on:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 5/17/2008 by James Ravenswood
> '
>
> '
> Range("F1").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=6, Criteria1:=">1", Operator:=xlAnd
> Range("A1:F29").Select
> Selection.Copy
> Windows("Book2").Activate
> Range("A1").Select
> ActiveSheet.Paste
> End Sub
>
> The only manual issue we face is correctly adjusting the F29.
> --
> Gary''s Student - gsnu200786
>
>
> "shiro" wrote:
>
> > Hi All,
> > I want to transferring a data automatically by using
> > vba code.But need soeone to point me the right
> > direction.What I want to do is something simple.
> > I want the code to run if it meet some condition.
> >
> > If a cell in column F:F have value >1 and not empty (""),
> > I want the code to collect the entire data on every cell
> > on the left to be copied and paste into a new workbook.
> >
> > And how to handle if there are some cell that have
> > value >1.How to copy the recordset and paste it into
> > a new workbook.And maybe I also need a message
> > box to tell the user that there is no data will be copied
> > if there is no cell in range F:F that contains value >1.
> >
> > New workbook maybe need a header something like:
> > " Wrong data list ".
> >
> > I have read automation transfer data to workbook
> > from MS Help and Support,but since I'm a new user,
> > I'm still cannot modified the code as I need.I'm still
> > need more simpler sample and lesson.
> >
> > Thank's and Rgds,
> >
> > Shiro.
> >
> >
> >
> >
> >