Transferring data to new workbook.

  • Thread starter Thread starter shiro
  • Start date Start date
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.
 
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.
 
Sorry Gary,
not so understand.Please more guidance.
What about the cell value in the left?
 
Back
Top