Transferring data to new workbook.

S

shiro

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.
 
G

Gary''s Student

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.
 
S

shiro

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